Bulk-Batch Email From Microsoft Access

If your users collect email addresses in a database, odds are before long, someone's going to get it into their head to send out an email "blast" or batch. There are several approaches, but what you really need is an engine to send individualized emails, one at a time...

If your database has a table with "person" information, odds are it has an email column. If your users collect email addresses, odds are they want to send emails to persons in the database and before long, someone's going to get it into their head to send out an email "blast" or batch.

There are several approaches to this request and over the years, I've used them all. The easiest solution is to create a function that returns a semi-colon delimited text string of emails and plunk in into the recipient's field of the email created in Microsoft Outlook. This is simple to implement and easy for users to understand, but carries with it the likelihood that the recipient's spam filter will reject it.

What you really need is an engine to send individualized emails, one at a time. That's the subject of this month's article, and one solution (of many potential solutions) is in the attached download. <BulkBatchEmail.zip>

Table Driven Emails

The first step is to set up some tables. It's assumed you have a table with the person-email information. In the demo this table is named tblContacts. To implement the demonstration code you'll have to make a few edits, if your table is not thus named. Simply do a search in all code modules for the word "tblContacts" and you'll see the edit points you need to address.

In addition to your own contacts table, you'll need to import the following tables into your MDB application:

tblEmail: Contains the email content and delimited recipient list

tblEmailRecip: Used to display the recipients as rows in a table

tblEmailAttachment: Lists the attachments for an email

tblEmailDefault: A single row with default text to be used with all emails

I've found that this approach has some advantages. Users like to look back to see what messages were sent and to whom, so tblEmail serves as a correspondence history of sorts.

The recipient list behaves as it does in other mail apps, showing email addresses as a semi-colon delimited list, but the inclusion of tblEmailRecip adds the flexibility of viewing the addresses as a sorted list with a lookup to the contact's full name.

Default text may be saved in tblEmailDefault, such as an email signature and company contact info, as well as a confidentiality disclaimer.

The Forms and Modules

The application has two forms, frmEmail and frmRecip, which do all the heavy lifting. There are also two code modules, basFnsAPI and basUtilities, which provide support services. Credit has been given where code has been borrowed from websites and newsgroup postings.

Both forms are shown below and appear as one might expect an email application to look. The creativity here is not in the cosmetic appearance, but in the functionality, which includes the following:

1. Fast and easy access to the Defaults table. In this example, only two defaults are given, email signature and company address, but more could be defined and used. Additionally, one could create a personalized row of defaults for each user.

2. The after-update event of the Recipient List text box takes action to clean up the list, removing spaces and replacing commas with semi-colons.

3. Double-clicking the recipient list opens/loads the Recipient List form with a lookup to the contacts table. While this list is not editable, one may delete rows from the pop-up form and see them disappear from the semi-colon delimited list in the email form.

4. The attachments listbox is loaded using the Add and Delete buttons to its left. The file navigation common dialog box is implemented for this process. Double-clicking an entry opens the attachment for viewing.

5. While it's not obvious from the UI, the body text will be inserted as HTML. This allows for some creativity for those who know a little about HTML tags.

6. The most important feature is the use of placeholder tags. In the email body text box you will see references to <name>, <email sig> and <co address>. When the email is created, these tags will be replaced with data that is looked up, so that the text "Dear <name>" will become something like "Dear Lamont Cranston".

This feature is extensible, of course. We once ran a batch where user's login credentials were inserted into 400 emails for clients who were being given access to our new web portal. I simply added a tag with the text <site login> and modified the code to do the appropriate lookup and substitution.

The beauty of using tags is that they may be employed, or not. If left out of the body text, the substitution will not take place. Leave off the <co address> and the company address will not show. Leave off the <name> tag and you can simply begin the email with "Dear Sirs". (Do we still start letters that way?)

7. Pre-Batch-Send safety valve. (We'll discuss this in the next section.)

8. The record is locked after the email(s) are sent. Though not shown here, the controls back-color is toggled to light yellow and their contents are locked after the request to send has been executed. This avoids the likelihood that the email will be accidentally sent a second time.

The record is locked after the email(s) are sent

The Big Send

My biggest fear in creating this user interface was that users might click Send and then regret it for one reason or another. Accordingly, I engineered (maybe over-engineered) a system of warnings and stop-gaps.

Rather than just initiating the send of these merged-data emails without previewing, I give the user the option to view them or cancel the entire operation. They see the message shown below, and if they click Yes, then the emails are displayed, not sent, as illustrated by the screen shot below.

the emails are displayed

Here we can see the effect of the tags. The name tag was replaced with real names, the email signature and company address tags also show our defined text. Also note that the email body is in HTML format, so where we added BOLD tags in our defaults, the text is bold.

The Code... In Brief

There is quite a lot of code involved so I won't be reproducing it all here, but if you get the download, you'll see it includes code to perform the following functions:

Get the Login Name of the current user.
Open a Find File dialog box.
Loop through items in a listbox
Toggle form controls setting properties as the record is loaded
Programmatically INSERT records into a table.
Programmatically open any file (from attachment list)
Send emails using HTML body style with Outlook automation.

The code used in the download employs Outlook Automation, as shown in the code snippet below.

With objEmail
    ' refresh the local body text variable with the template value.
    strBody = Replace(sBody, vbCrLf, "<br>")
    
    ' lookup the contact name ... if not found, derive from email
    strSQL = "SELECT TOP 1 [FirstName] & ' ' & [LastName] AS Contact " & _
             "FROM tblContacts WHERE [Email]='" & strRecip & "' "
    SetRstA strSQL    ' function for loading recordset named rstA
    
    If rstA.BOF And rstA.EOF Then
        strName = Left(strRecip, InStr(1, strRecip, "@") - 1)
    Else
        strName = Nz(rstA!Contact, Left(strRecip, InStr(1, strRecip, "@") - 1))
    End If
    Set rstA = Nothing
    
    strName = StrConv(Replace(strName, ".", " "), vbProperCase)

    ' here is where body text tags are replaced with looked up values
    strBody = Replace(strBody, "<name>", strName)
    strBody = Replace(strBody, "<email sig>", "<br>" & strEmailSig)
    strBody = Replace(strBody, "<co address>", "<br>" & strCoAddress)
    
    .To = strRecip
    .Subject = sSubject
    .BodyFormat = 2          ' olFormatHTML = 2
    .HTMLBody = strBody      ' use HTMLBody, not Body property.
    '.Body = strBody
    .Save
    
    ' loop through listbox, adding attachments to email
    For lngItem = 0 To Me!lstAttachments.ListCount - 1
        strName = Nz(Me!lstAttachments.Column(1, lngItem), "")
        strFile = Nz(Me!lstAttachments.Column(2, lngItem), "")
        
        If strFile <> "" Then
            .Attachments.Add strFile, 1, 1, strName
        End If
    Next

    ' here is where we decide whether to show or send the email    
    If fPreviewAll Then
        objEmail.display
    Else
        If intPreviewLimit >= cPreviewLimit Then
            If fSend = True Then
                objEmail.send
            Else
                objEmail.display
            End If
        Else
            objEmail.display
        End If
    End If
    intPreviewLimit = intPreviewLimit + 1
End With

As was mentioned at the outset, there are other ways to send an email. One is to loop through your list of addresses and call the DoCmd.SendObject that is native to Access. This will certainly work, but I don't believe I was ever able to get it to display HTML body content the way I wanted.

Another send mail method involves CDO. This code is also very good and not dependent upon having Microsoft Outlook configured for each user. Below is a partial code example of how CDO might be implemented. It's a nice alternative to Outlook automation.

    Dim sch As String
    Dim rrc As String
    Dim rrn As String
    Dim cdoConfig As Object
    Dim cdoMessage As Object

    sch = "http://schemas.microsoft.com/cdo/configuration/"
    rrn = "urn:schemas:mailheader:disposition-notification-to"
    rrc = "urn:schemas:mailheader:return-receipt-to"
    
    Set cdoConfig = CreateObject("CDO.Configuration")
    With cdoConfig.Fields
        .Item(sch & "sendusing") = 2 ' cdoSendUsingPort
        .Item(sch & "smtpserver") = "YourMailServerNameHere"
        .Item(sch & "smtpserverport") = 25
        .Update
    End With

    Set cdoMessage = CreateObject("CDO.Message")
    With cdoMessage
        .Fields(rrn) = strFrom
        .Fields(rrc) = strFrom
        .Fields.Update
        
        Set .Configuration = cdoConfig
        .ReplyTo = strFrom
        .From = "Network Management <" & strFrom & ">"
        .To = sRecipName & " <" & sRecipEmail & ">"
        .Subject = sSubject

        .HTMLBody = sBody
        If Dir(sAttach) <> "" And sAttach <> "" Then .AddAttachment (sAttach)
        
        .Send
    End With

    Set cdoMessage = Nothing
    Set cdoConfig = Nothing

    SendEMailCDO = Err.Number

Room for Customization

If you implement this email form, the next thing you'll likely be asked to do is to provide an interface for loading a list of emails. This demo application doesn't include such a device but the screen-shot that follows shows one I had built for one of my clients.

For this client, the key to selecting email addresses was the category they were assigned to. I also supplied a text box for a cut-off date, to exclude really old records. The list box updates automatically when the category is selected or a date edited.

The Load Emails button does nothing except loop through the entries creating a semi-colon delimited text string of email addresses, and then plunks it into the Recipients text box on the email form. It's handy, but a very custom job.

select emails for batch send

As long as emails are with us, the need for sending out batches of them will be too. This feature has been embedded into most of my applications. I have found it to be invaluable and I suspect you will too.

» See All Articles by Columnist Danny J. Lesandrini