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.

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 , and <co address>. When the email is created, these tags will be replaced with data that is looked up, so that the text "Dear " 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 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 and the company address will not show. Leave off the 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.

Full article...