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:

mailing records

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

start the Recordset Builder from toolbar submenu DAO

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

Recordset Builder Browse qryMailingDemo

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: Create Variable using Builder String EmailAddress
  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...

menu Create Outlook MailItem

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

dialog to specify the use of the MailItem object

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

start string builder using intellisense

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.

string builder dialog

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.

automated customized emails
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