MS Access VBA / SQL Email Function

Question

I am building a database in MS Access that is being used by employees to log daily entries, access customer information, etc. The 2 major functions of the database is a Distro (replacing an old Excel Distro Matrix) and a custom Email Report Function.

The Email Report Function is designed to open a new message in Outlook and automatically generate fields such as To, CC, Subject Line, and Message Body. The Button itself is located on a form, where the employees do not interact with the tables of the Database themselves, and can simply select from a list of Reports in a combo box, and click "Email Report".

My question is complicated because of the structure of my database. The combo box is a list of Reports that are sent out on a daily basis, and unable to be edited by the user. The Distro is a table that is structured as follows: Person / Entity (Name), Contact Information (Email Address), and the rest of the columns are the Report Types that hold a True / False check box. Ultimately, each receiving person is listed by name, contact email, and according to what reports they recieve they have checked box under each report column.

I need to design a function that does the following: Checks what report is selected from the combo box on the form, open a new email in Outlook, query the Distro Table for who is checked to recieve the selected report, and finally, collect those specific email addresses and insert them into the message in the correct location (To, CC) while also generating the Email Subject line and Message Body according to what report has been selected from the original combo box.

Show what you've tried in code and note where it is not working. — Andrew Mortimer
I do not see any problem. Yes you have design work to do, but there is no problem stated such that I can replicate it on my computer for me to see your "problem". — donPablo
I apologise for the confusion. I'm having a hard time understanding how to create this query. I am unable to access my database at the moment due to a maintenance window, so I am using my phone to respond. I can't seem to grasp how I need to build this function, in order for it to query a table for values, and THEN use values out of a column in order to generate this email. — Braden
So you're asking how to query a table in MS Access and use the results to create and populate an email. First learn how to query an MS Access database and use the results. Do you have a query? Do you have an input form? These things are opaque mysteries to us until you provide some info — Nick.McDermaid
Here is an example of how to load a query result into a variable: social.msdn.microsoft.com/Forums/en-US/… — Nick.McDermaid

Hamed Hamed · Accepted Answer · 2018-10-28T08:55:44

If you want your database be more rational and easier to use, I suggest Distro table has just Name and email address and ID.

Therefor create another table [Distro Reports] with 2 fields: [Person ID] which is a number joined to Distro.ID and [Report name] which hold report name.

For your combo box row source filed, use bellow:

SELECT [Name] 
FROM MsysObjects
WHERE [Type] = -32764

To extract list of email addresses per report use bellow:

Private Sub Command13_Click()
Dim Selected_Report As String
Dim SqlStr As String
Dim EmailList As ADODB.Recordset

Dim olApp As Object
Dim olMailItm As Object

Selected_Report = Form_MainForm.ReportListCombo.ItemData(0)
'....... Export report as PDF ......
SqlStr = "SELECT Distro.[Contact Info] " & _
    " FROM Distro INNER JOIN [Distro Reports] ON " & _
    "   [Distro Reports].[Person ID] = Distro.ID " & _
    " WHERE [Distro Reports].[Report Name] = [" & Selected_Report & "];"
CurrentDb.QueryDefs("EmailList").SQL = SqlStr
Set EmailList = CurrentDb.OpenRecordset("EmailList")
Set olApp = CreateObject("Outlook.Application")
Set olMailItm = olApp.CreateItem(0)
With olMailItm
    .cc = ""
    While Not EOF(EmailList)
        .cc = .cc & ";" & EmailList(0)
        ٍEmailList.MoveNext
    Wend
    EmailList.Close
    .subject = "Your subject"
    .Attachments.Add Path2Report, olByValue, 0
    ' for simple text:
    .body = ""
    ' for reached text style:
    .htmlbody = ""
    .display
End With
End Sub

To export report to PDF see bellow link: Simple VBA code to export Access Report to saved PDF File when code is run

MS Access VBA / SQL Email Function

1 Answers