Bulk-Batch Email From Microsoft Access

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

Danny Lesandrini
Danny Lesandrini
Danny J. Lesandrini currently works as the IT Director for Pharmatech Oncology Inc. at http://www.pharmatechoncology.com/. He holds Microsoft Certifications in Access, Visual Basic and SQL Server and has been programming with Microsoft development tools since 1995.

Latest Articles