In this article, Rick Dobson explores using the robust database management features of Access to develop e-mail solutions for managing business relationships.
Access is the premiere development tool for small businesses and departments in medium-sized businesses. At the very heart of many businesses are the needs to keep track of and communicate with prospects, clients, vendors, and partners via e-mail. Access is great for keeping track of people, such as prospects and clients. However, Access isn’t well known for its ability to send e-mail to people. I’m going to show you a couple of ways that you can leverage Access’ outstanding database management development features when sending e-mail.
Of course, Outlook is the general-purpose Office tool for e-mail and related kinds of requirements. Access can create e-mail solutions with Outlook or work independently of Outlook. Depending on your type of e-mail task, you may prefer one approach over the other. For example, Outlook offers rich previewing and editing tools for e-mail. If this is a requirement for your e-mail task, it makes sense to take advantage of Outlook. On the other hand, Outlook has built-in security features that block or restrict the ability of rogue software (such as a virus) from sending e-mail to everyone in its Address Book. Unfortunately, this security measure makes Outlook unfriendly for servicing your legitimate need to send a message to everyone or even a substantial subset of your contacts. In this situation, you can use CDO (Collaboration Data Objects) programmed with VBA from Access to push out e-mail messages to many different recipients. The CDO library isn’t known for its ease of use, but I’ll show you a fast way to get started programming e-mail solutions with CDO from Access VBA projects.
Two Access e-mail solutions
I don’t advocate using Access as a replacement for Outlook. Nevertheless, the database management features of Access make it an ideal development tool for selected types of e-mail applications. I’ll highlight two common e-mail solutions that benefit from development with Access.
First, a business may need to send any of several routine e-mail messages to correspondents. The term routine applies to those messages that are the same for all recipients, except for a few minor changes (such as the name of the person you’re sending it to, the name of a product, or the duration of a subscription or membership). These routine messages will typically thank clients and prospects for their interest in a firm, remind a subscription client that it’s time to renew, or confirm with marketing prospects the date, time, and venue for a presentation. Many businesses already have a collection of informal templates for these kinds of e-mail. In addition, each message template will typically feature one or more fields that require editing whenever the message is sent.
You can use Access to store the information about each message as well as data about the fields that can change. Access forms can simplify modifying field values just before sending a message. When sending messages one at a time to individual correspondents, using Access and Outlook together makes a lot of sense. You could, of course, also use Microsoft Word’s mail-merge facilities. However, an integrated approach built on Access and Outlook allows you to take advantage of Outlook’s editing/previewing features along with Access’ data management capabilities (both for tracking messages and the users who are to receive those messages).
A second kind of e-mail application requires sending the same message to all of your contacts or a substantial subset of them. Outlook distribution lists offer one approach to this task, but these lists don’t update automatically like queries do. Access queries offer a more flexible means of specifying who can receive a message. You can program Outlook with VBA to repeatedly send the same message to a subset of members from an Outlook Address Book, but Outlook has no way to distinguish between your VBA program and a virus. Therefore, both your program and the virus encounter the well-known security prompt (see Figure 1). This prompt requires a user to manually click a button, and the prompt will recur at regular intervals if you attempt to send a message to a long list of correspondents.
Figure 1
The security prompt problem has multiple solutions, and I’ll show you one based on CDO (in my article in last month’s issue, I discussed another solution: Express ClickYes). CDO offers a collection of classes that you can program with VBA. The NewMail objects simplify sending e-mail messages.
Sending one of several e-mails
To send one of several routine messages requires a collection of elements. First, you need to create a set of one or more messages that you save as files. Second, you need to create an Access database for tracking messages. The Access database also stores a list of e-mail correspondents in its Contacts table. The sample database for this article, SA0204.mdb, contains a Contacts table with several rows of data. The application uses the contact information to personalize your messages and to specify where to send an e-mail. Third, you need Outlook. After a user selects a message and performs any customization, the application opens Outlook and populates the fields of a new message to allow you to preview the message. This preview offers a second opportunity to customize a message before clicking the Send button in the Outlook message window to forward the message to the Outbox. Outlook automatically sends the message from the Outbox via a regular schedule or the next time a user clicks the Send/Receive button.
Since the application programmatically manipulates Outlook, you need a reference to the Outlook object library. I developed the sample applications for this article with Access 2002 and Outlook 2002. Therefore, the sample Access database file has a reference to the Outlook 10.0 object library. If you use another version of Office, such as Office 2000 or Office 2003, you need a reference to either the Outlook 9.0 or Outlook 11.0 object library.
You can compose the items in your message collection with any program that edits and saves plain text or HTML. I used Word as the e-mail editor for Outlook with Plain Text selected for the message format in order to generate a message with plain text formatting. This format doesn’t permit formatting such as active hyperlinks or even bold text, but you can see what your message will look like in an e-mail reader set to read only plain text (the HTML and Rich Text message format settings in Outlook provide more formatting options). Since Outlook and CDO both support an HTML property for a message or mail object, I selected HTML as the alternative to plain text in the sample application. You can use either Outlook/Word with an HTML output setting or any HTML editor to compose messages in HTML format. I chose FrontPage 2002 as my HTML editor.
The three messages for this sample application have names ClubRenewal.htm, ClubWelcome.txt, and WidgetEnquiry.htm. They reside in the Articles folders of the C: drive on my test computer. You can store your messages wherever you choose, but you must synchronize the paths and file names to the messages with the table that specifies information about the messages.
Within each message, you can use one or more keywords to designate fields that users can update at runtime. You’re free to designate any keyword that you prefer. For example, the message in the ClubWelcome.txt file uses the keyword NewMemberName to designate the name of the recipient. On the other hand, the message in the ClubRenewal.htm file specifies the name of the recipient using ClubMemberName as a keyword. The application has built-in mechanisms for tracking keywords in messages. In addition, users can edit the values that replace keywords in messages from Access forms. Two tables–Messages and MessageFields–track the messages for an application along with the message keywords and their last value.
A value is a string that replaces the keyword in sent messages. For example, the keyword NewMemberName can have a last value of Rick Dobson. If Virginia Dobson is the next member to join the club, then the last value of the NewMemberName keyword will change from Rick Dobson to Virginia Dobson.
The Messages table contains a row for each message along with six columns to distinguish each message. The MessageID column is an Autonumber field that provides a unique value as the primary key. The MessageName column contains a short descriptive text field for the message. The Subject column contains the default Subject field value for a message. Users can override this field value from Outlook before clicking the Send button in the Outlook message window. The HTML column has a Yes/No data type. A value of Yes corresponds to an HTML format, and a value of No specifies a plain text format. The Path and Filename columns are two text fields that designate the path to the message file.
The MessageFields table has four columns. The initial MessageID column has a Long Integer value that points back at one of the rows in the Messages table. The FieldID column also has a Long Integer value, which specifies one of the keywords in the message file. Since the message in the ClubWelcome.txt file has four keywords, its FieldID values range from 1 to 4. The FieldName and LastFieldValue columns both have a text type. These columns designate the names of keywords and their last value.
The Contacts table has four columns. The first column is an Autonumber column. The second and third columns are for the first and last name of a contact. The fourth column contains the e-mail address of a contact.
Forms and procedures
Figure 2 shows frmMessages, which is a main/subform that displays Messages table column values in its main form and matching column values from the MessageFields table values in its subform. The top five textboxes in frmMessages with a checkbox between the third and fourth textboxes are bound to the columns of the Messages table.
Figure 2
The three textboxes immediately above the subform are unbound. These three controls allow you to display contact information. The textbox with a label of “To criteria” lets a user enter any part of the values for the first and last names, such as Rick (for Rick Dobson). Clicking the Find button recovers the first and last names as well as the e-mail address of the first row in the Contacts table that matches the criterion. The application puts the combination of the first and last name in the next-to-last textbox on frmMessages, and it puts the e-mail address in the form’s last textbox. In addition, the Click event procedure for the button updates the last column value in the first row of the subform. Therefore, you should always reserve this message field value for the full name of recipients.
The Click event procedure for the form’s Send button populates a new Outlook mail item with selected values from frmMessages, such as the recipient’s e-mail address and the message’s subject, as well as an edited version of the message. Keywords in a saved message are updated with values from the LastFieldValue column in the message to be sent.
The following code is from the Click event procedure for the Find button on frmMessages. The code begins by creating a SQL Where clause to be used against the Contacts table. The constant strDQ holds four double quotation marks, which are required to insert a single double quote into a string. I’ve used the Like operator to match any sequence of characters in the first and last names that have a space between them. Three DLookup functions populate the next-to-last textbox on the form (txtSenderName) and the last textbox on the form (txtE-mailAddress). The code closes by populating the last column value in the first row of the subform (ctl1.Form.Controls(6)):
strSQL = "(Fname & " & "Lname) " & _
"Like " & strDQ & "*" & _
txtSenderCriterion & "*" & strDQ
strFirst = DLookup("Fname", "Contacts", strSQL)
strLast = DLookup("Lname", "Contacts", strSQL)
txtSenderName = strFirst & " " & strLast
txtE-mailAddress = _
DLookup("E-mailAddress", "Contacts", strSQL)
Set ctl1 = ctlMessageFields
ctl1.Form.Recordset.MoveFirst
ctl1.Form.Controls(6) = strFirst & " " & strLast
ctl1.Form.Refresh
The next set of code is from the Send button Click event procedure. The code begins by using the FileSystemObject to open the saved message specified in the current row in the frmMessages form. Your VBA project needs a reference to the Microsoft Scripting Runtime for this to work. Next, the code edits keywords in the message based on the values in the LastFieldValue column of the frmMessageFields subform. The code finishes by passing the e-mail address, formatting style for the message, subject field, and a string with the edited message to the SendToSubjBody procedure:
str1 = txtPath & txtFilename Set tst1 = fso1.OpenTextFile(str1, ForReading) str2 = tst1.ReadAll Set ctl1 = ctlMessageFields ctl1.Form.Refresh ctl1.Form.Recordset.MoveFirst For int1 = 1 To ctl1.Form.Recordset.RecordCount For Each ctl2 In ctl1.Form.Controls If ctl2.Name = "txtFieldName" Then strFind = ctl2.Value End If If ctl2.Name = "txtLastFieldValue" Then strReplace = ctl2.Value End If Next ctl2 str2 = Replace(str2, strFind, strReplace) ctl1.Form.Recordset.MoveNext Next int1 SendToSubjBody txtE-mailAddress, _ chkHTML, txtSubject, str2
The last bit of code from this application that I want to show you is from the SendToSubjBody procedure. This procedure begins by instantiating a variable that points at an Outlook session. Then, the code uses the session to create a new mail item. Before displaying the mail item in Outlook, the procedure populates three properties for the mail item. The To property accepts the e-mail address. The Subject property takes the String value for the message’s subject passed to the procedure. The procedure populates one of two properties depending on the formatting for the message. When the message has an HTML format, the procedure assigns the string with the message to the HTMLBody property. Otherwise, the procedure assigns the message string to the mail item’s Body property. The final statement in the routine displays Outlook with the newly composed message. From this point, the user can either click the Send button or perform any final editing prior to clicking the button:
Set ola1 = New Outlook.Application Set mai1 = ola1.CreateItem(olMailItem) mai1.To = strTo mai1.Subject = strSubj If bolHTML = True Then mai1.HTMLBody = strBody Else mai1.Body = strBody End If mai1.Display
Sending the same message to multiple recipients
Sending the same message to multiple recipients is a different kind of e-mail application than the preceding one, which enables users to send any one of several different e-mail messages to one e-mail address. Sending the same message to multiple recipients is suitable for many business contexts. I use this kind of capability to notify registered visitors about new content at ProgrammingMSAccess.com. If you want to get a first-hand feel for what this type of application can do, register for the site’s messages at www.programmingmsaccess.com/mygb.htm. Consulting firms can use the capability to inform clients and prospects about new services, staff additions, and white papers or presentations authored by staff persons.
As I mentioned previously, the built-in security prompt in Outlook makes it difficult to perform this kind of task. In addition, Outlook automatically saves a copy of each message e-mailed in its Sent folder. If you’re e-mailing the same message to several thousand, or more, recipients, it’s unlikely that you wish to keep a copy of each sent message. Finally, when sending the same message (perhaps differentiated by recipient name), you may want to filter your contact list to just a subset of contacts who should get a copy of the message. Outlook does offer filtering, but it doesn’t have the same rich query features provided by Access. Using Access for filtering a list of contacts will be substantially more flexible than using Outlook (and more familiar to Access developers like you).
The following sample demonstrates how to use the CDO object model as an alternative to Outlook for sending the same message to multiple recipients with Access. When you program the CDO object model with VBA, you can avoid the Outlook security prompt. In addition, you bypass the whole Outlook user interface, which means you don’t automatically retain copies of messages in Outlook folders. You also gain the flexibility to readily specify recipient e-mail addresses from any source, such as an Access query.
The CDO object model used to be available as a separate redistributable, but it now ships exclusively as part of several packages, including Office 2000 through Office 2003. However, CDO doesn’t install by default with Office unless you perform a complete installation. The CDO library is available from the cdo.dll file, which typically resides in the local resources folder (1033 for the US) of the \Program Files\Common Files\System\Mapi\ path. You can add a reference to the CDO library by selecting Microsoft CDO 1.21 Library from the Available References list of the References dialog box from the Tools | References menu.
The sample for mailing one message to multiple recipients consists of three elements. First, you need a list of contacts. The sample demonstration uses a query (qryContacts) based on the Contacts table. Second, you need a message. The sample message is in HTML format. The application is set up to accommodate HTML instead of plain text. HTML format offers the ability to include active hyperlinks in a document, which isn’t available from messages in plain text format. Many recipients prefer active hyperlinks in messages because the recipient can click the links to view the hyperlinked Web page instead of having to copy a URL into the Address box of their Web browser. The third element is a form, frmBatchMail, which includes textboxes for specifying the parameters of a typical batch mail job and a button that has an event procedure for launching the mail job. The form’s Load event procedure automatically populates the textboxes for the sample batch job that I describe here. You can change the settings of the Load event procedure to work with any message and query for contacts that you prefer.
The qryContacts query simply returns all the columns for all the rows in the Contacts table. Since the sample Contacts table contains just four rows, there’s no need to restrict which rows are retrieved. However, a substantially larger Contacts table might contain more columns to support filtering on various criteria. In addition, Access makes it simple to filter on substrings for the Fname, Lname, and E-mailAddress columns. For any application where you’re mailing to e-mail addresses, you shouldn’t use the Access hyperlink data type to represent e-mail addresses. Instead, use text data types that contain e-mail addresses.
I’ve saved the sample message with the name ProgrammingMSAccess_Feb_2004.htm in the Articles folder. You can use any other name and folder you choose as long as you update the frmBatchMail Load event procedure or override the default settings in the form when it opens. In addition, the sample message initially designates the recipient’s full name (first name, space, last name) with the FullName keyword. You can also override this keyword for initially representing a recipient’s full name either from the frmBatchMail form or its Load event procedure.
Figure 3 shows frmBatchMail with the default settings. The Subject textbox is the Subject for the messages that the application pushes out to recipients in the source of contacts, which is specified in the last textbox on the form. The From textbox designates the Display name of the recipient and, optionally, the e-mail address of the sender (that would be you). If you elect to designate a sender e-mail address, place the address in angle brackets after the Display name.
Figure 3
The code behind the form takes advantage of the NewMail class to drastically simplify the use of the CDO library. The NewMail class resides in the CDONTS library. This library is a subset of the CDO library that’s available from cdo.dll. NewMail class instances feature a collection of read-only properties that let you set, but not read, the properties for a class instance. A NewMail class instance corresponds to a single e-mail message. After using the instance once, you should discard the instance and make a new one for another message. NewMail class instances permit you to set most common e-mail fields with String or Long values.
The following code shows an excerpt from the Click event procedure for the button on frmBatchMail. The listing begins by reading the message file into a String variable (str2). Next, the code opens a record source for contacts. After these two preliminary steps, the code loops through all the rows in the contacts record source. The code within the Do loop begins by creating a NewMail class instance. Then, the code assigns the Subject and From properties of the class instance. The assignments to the BodyFormat and MailFormat properties are necessary for a message in HTML format. See the NewMail class online documentation at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdo/html/_denali_newmail_object_cdonts_library_.asp for other possible settings of these two properties. Next, the code replaces the initial keyword or the last person’s full name from the string with the message before assigning the message to the Body property of the NewMail object. Just before invoking the Send method to route the message to a recipient, the code assigns the E-mailAddress field from the query to the To property of the NewMail object. The loop closes by setting the NewMail object to Nothing and moving to the next row in the contacts query source:
str1 = txtPath & txtFilename
Set tst1 = fso1.OpenTextFile(str1, ForReading)
str2 = tst1.ReadAll
Set rst1 = New ADODB.Recordset
strSource = txtNamesQry
rst1.Open strSource, CurrentProject.Connection
strFind = txtFullName
Do Until rst1.EOF
Set nmo1 = CreateObject("CDONTS.NewMail")
nmo1.Subject = txtSubject
nmo1.from = txtFrom
nmo1.BodyFormat = 0
nmo1.MailFormat = 0
strReplace = rst1("Fname") & " " & rst1("Lname")
str2 = Replace(str2, strFind, strReplace)
strFind = strReplace
nmo1.Body = str2
nmo1.To = rst1("E-mailAddress")
nmo1.Send
Set nmo1 = Nothing
rst1.MoveNext
Loop
All of my examples are from the area called CRM: Customer Relationship Management. You can position yourself to serve whole new areas of business needs that are both common and growing in importance–incorporating e-mail into your applications to manage business relationships.
