Send an email to multiple recipients using MS Access and Outlook

This article shows how to code a VBA macro to send a customized email to multiple recipients using MS Access and Outlook. Additionally, it shows how the Code VBA add-in helps create the code easily by using its builders.

1. Create a loop to process records with recipients to create the email for;
2. Create the email object in Outlook;
3. Make an email body with customized salutation.

Before starting, create a query 'qryMailingDemo' which returns the required records and the field I will be using:

In a module, create an empty Sub 'MailingDemo' for the macro

Create a loop over the records of qryMailingDemo

Use the Code VBA Recordset Builder

In the Recordset Builder dialog select qryMailingDemo, select the required fields and Option 'Browse'. After OK the code is inserted.

Comment the Debug.Print line and insert a new line below it; create variables to later use the field values

Note: use the Variable Builder to save time...

... in typing, in placing the variable at the top of the procedure, and copying its name for further use. For example:

1. Comment the Debug.Print line and insert a new line below it;
2. Paste to insert the name strEmailAddess variable from the clipboard;
3. Complete the line: strEmailAddess = ![E-mail Address];
4. Repeat the process for the other two variables;

Sub MailingDemo()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strEmailAddess As String
Dim strFirstName As String
Dim strLastName As String
Set db = CurrentDb
Set rst = db.OpenRecordset("qryMailingDemo", dbOpenDynaset)
With rst
    Do While Not .EOF
        'Debug.Print ![Last Name], ![First Name], ![E-mail Address]'
        strEmailAddess = ![E-mail Address]
        strFirstName = ![First Name]
        strLastName = ![Last Name]
        .MoveNext
    Loop
    .Close
End With
Set rst = Nothing
db.Close
Set db = Nothing    
End Sub

We now have a loop making the required data available for the recipients. Now we will add code to create the emails in Outlook and show how the data can be used there.

Using the Outlook MailItem

We want to send an email to each recipient. To achieve this we make an empty line before .MoveNext to create the email. Now we will use the Code VBA add-in to connect to Outlook and create the mail: down the Code VBA menu, select Outlook followed by Create...

This opens a Fragment Builder dialog to specify the use of the MailItem object:

• Select strEmailAddess for the To property;
• Enter text for subject line;
• Agree to have a new String variable strBody created to set the Body;
• Accept the default value .Display for the MailItem's Action setting. You will probably change this to .Send once things are working to your satisfaction.

After OK, the following code block is inserted

Dim mimEmail As Outlook.MailItem
Dim appOutlook As Outlook.Application: Set appOutlook = New Outlook.Application
 Dim strBody As String: strBody =
Set mimEmail = appOutlook.CreateItem(olMailItem)
With mimEmail
    .To = strEmailAddess
    .Subject = "Taskforce meetin"
    .Body = strBody
    .Display
End With

We don't want to call the opening of Outlook for each individual record, so we move the line with New Outlook.Application more to the top, outside the Do While loop.

Make a body with customized salutation

The line with variable strBody still needs completion. Put the cursor after the '=' and press Shift-Space to use Code VBA IntelliSense. This opens a context menu with many options to build your string from. Here, we chose the String Builder to make the combining text with variables easy.

As you see, the string builder dialog is filled with the email's invitation text. Using the variables we have a customized email with for each recipient a separate salutation. We added the first and last name variables from the popup selection box and the Add button. Pressing OK inserts the code.

strBody = "Dear " & strFirstName & " " & strLastName & "," & vbNewLine & _
    "Just a reminder that our meeting to discuss the environment is later this week. See you Thursday!"

You can now run the macro (F5). The emails are opened in draft. If you extended this code to your satisfaction and all's good, change .Draft to .Send for future enjoy.

Sub MailingDemo()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strEmailAddess As String
Dim strFirstName As String
Dim strLastName As String
Dim appOutlook As Outlook.Application: Set appOutlook = New Outlook.Application
Set db = CurrentDb
Set rst = db.OpenRecordset("qryMailingDemo", dbOpenDynaset)
With rst
    Do While Not .EOF
        strEmailAddess = ![E-mail Address]
        strFirstName = ![First Name]
        strLastName = ![Last Name]
        Dim mimEmail As Outlook.MailItem
        Dim strBody As String: strBody = "Dear " & strFirstName & " " & strLastName & "," & vbNewLine & _
                                            "Just a reminder that our meeting to discuss the environment is later this week. See you Thursday!"
        Set mimEmail = appOutlook.CreateItem(olMailItem)
        With mimEmail
            .To = strEmailAddess
            .Subject = "Taskforce meeting"
            .Body = strBody
            .Display
        End With
        .MoveNext
    Loop
    .Close
End With
Set rst = Nothing
db.Close
Set db = Nothing
End Sub