Emailing 3 – Reading Email Addresses From A Table

In the previous post, we introduced the fundamental code associated with opening Outlook from Access. In this post, we will be showing you how to manipulate the “To” field of the email so you can send emails to multiple email addresses.

The Code

We will be getting the email addresses from the Customers table.

This code is very similar to the code in the previous post except in section 2 (Recipients) where we have added code to loop through a recordset and extract the email addresses.

Public Function readingEmailAddressesFromATable(strSQL As String, strEmailField As String, _
                                    strSubject As String, strMessage As String)

On Error GoTo ErrorHandler
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''      1        ''''''''''''''''''''
    ''''''''''''''''''' Declarations '''''''''''''''''''''
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim olApp As Object
    Dim olMail As Outlook.MailItem
    Dim olRecipient As Outlook.Recipient
    Dim olAttachment As Outlook.Attachment
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    ' Create the Outlook session.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'if outlook is open it will need to be run as administrator
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Set olApp = New Outlook.Application
    Set olMail = olApp.CreateItem(olMailItem)
        
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''      2        ''''''''''''''''''''
    ''''''''''''''''''' Recipients '''''''''''''''''''''''
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
    'open up a recordset and move through it
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)
        
    With rs
        If Not .BOF And Not .EOF Then
            .MoveLast
            .MoveFirst
            
            While (Not .EOF)
                ' Add the To recipient(s) to the message.
                Set olRecipient = olMail.Recipients.Add(.Fields(strEmailField))
                olRecipient.Type = olTo
                .MoveNext
            Wend
        End If
    End With
    
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''      3        ''''''''''''''''''''
    ''''''''''''''''''''' Message'''''''''''''''''''''''''
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    With olMail
        ' Set the Subject, Body, and Importance of the message.
        .Subject = strSubject
        .Body = strMessage
        .Importance = olImportanceHigh  'High importance
              
        ' Resolve each Recipient's name.
        For Each olRecipient In .Recipients
            olRecipient.Resolve
        Next          
       
        
        ''''''''''''''''''''''''''''''''''''''''''''''''''''''
        '''''''''''''''''''      4        ''''''''''''''''''''
        ''''''''''''''' Display, Save, Send'''''''''''''''''''
        ''''''''''''''''''''''''''''''''''''''''''''''''''''''
        .Display
           
      End With

readingEmailAddressesFromATable = True

ExitFunction:
    Set olRecipient = Nothing
    Set olMail = Nothing
    Set olApp = Nothing
    Exit Function
ErrorHandler:
    readingEmailAddressesFromATable = False
    Resume ExitFunction
End Function

The Explanation

Public Function readingEmailAddressesFromATable(strSQL As String, strEmailField As String, _
                                    strSubject As String, strMessage As String)

The name of the function is readingEmailAddressesFromATable and its arguments are:

• strSQL – An SQL statement that can be either the name of a table or a full Select statement.
• strEmailField – The field from the generated recordset that contains the email addresses.
• strSubject – The subject of the email address.
• strMessage – The message of the email address.

Changes to Section 2

'open up a recordset and move through it
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)        

This code opens up a recordset based on the strSQL argument (can be a table name).

    With rs
        If Not .BOF And Not .EOF Then
            .MoveLast
            .MoveFirst
            ...
            ...
        End If
    End With

This code references the newly created recordset and checks that it contains records.

            While (Not .EOF)
                ' Add the To recipient(s) to the message.
                Set olRecipient = olMail.Recipients.Add(.Fields(strEmailField))
                olRecipient.Type = olTo
                .MoveNext
            Wend

This code loops through the recordset and adds the emails to olMail.Recipients.

Testing The Email

Our form now contains four fields and looks like this:

Clicking on the Test button produces this result:

All the email addresses from Customers have been added to the “To” field of the message.

The Conclusion

In this post, we have shown you how to get multiple email addresses from a table. In the next post, we will be showing you how to perform the same function but have the email addresses come up in the BCC field.