Bulk-Batch Email From Microsoft Access
October 5, 2009
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:
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
after-update event of the Recipient List text box takes action to clean up the
list, removing spaces and replacing commas with semi-colons.
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
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.
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.
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".
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 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
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.
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:
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.
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.