AboutIf 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 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.
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 BriefThere 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.
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.