Send Bulk Email with ASP and VBScript

My company's Customer Support group sends email to thousands of customers at a time. The email messages often contain important information about software updates or other support issues, so the Customer Support staff tries its best to make sure the messages are read. For example, the staff sends email messages in both HTML and plain-text format. In addition, the staff personalizes the email messages because people tend to delete impersonal bulk email without reading it.

This personalization wasn't always possible, though. The bulk email methods that the company used in the past prevented personalization and had a host of other problems. Thus, I created a new system—the Bulk Email Sender—that combines the technologies of Active Server Pages (ASP) and VBScript.

The Past Methods and Their Problems
In the past, my company used three methods to send bulk email to customers. The oldest method was to use a UNIX mailer script that read email addresses from a text file, then sent a generic message to them. The second method was to simply copy and paste email addresses into the To field of a manually composed Microsoft Outlook email. The third method was to use a commercial email program to send the messages from a service account.

These bulk-email solutions were far from ideal. They posed several problems:

• The email messages were impersonal. The Customer Support staff couldn't customize the messages for different groups of customers. In addition, customers often received the email as a blind copy or received it from a system account that couldn't respond to their replies.
• The email address lists were difficult to maintain. The email addresses for the UNIX mailer script resided on a UNIX file system. Thus, anyone who wanted to update the addresses (or the scripts) had to be well versed in UNIX. Similarly, the commercial program's email addresses resided on a corporate mail server. Thus, only an authorized server administrator could make changes to the email addresses.
• The email addresses were sometimes unprotected. In the copy and paste method, all the customers' email addresses were listed in the To field. Anyone could have grabbed that list of email addresses and used it for any purpose.
• The email methods didn't scale well. The three methods sufficed when a small number of customers needed to receive email messages. However, when the Customer Support group had to send hundreds or thousands of email messages at a time, the methods broke down. For example, the person copying and pasting email addresses sometimes forgot which group of customers had already been processed. Other times, the UNIX mailer truncated the recipient list. Because of such problems, the Customer Support staff members were never sure whether they had sent every recipient a message.

The New System
The problems with the three methods prompted me to look for a different way to send bulk email to customers. The result is the Bulk Email Sender, a system that uses a Microsoft Access database and two scripts (Mail1.asp and Mail2.asp), which I wrote in VBScript. In addition to meeting the Customer Support group's need for personalization, this new system scales well and is easy to maintain.

Figure 1, page 6, shows the form that Mail1.asp creates. In this form, the Customer Support staff can select a specific group of recipients, include a personalized Subject line, choose HTML or plain-text format, include a customized message, and attach a file. (Mail1.asp automatically fills in the From field.) As Microsoft IIS sends the email messages, Mail2.asp displays the recipients, as Figure 2, page 6, shows.

Mail1.asp and Mail2.asp use a variety of objects, including ASP built-in objects (i.e., Microsoft Visual Basic—VB—objects), ADO, and Collaboration Data Objects for Windows NT Server (CDONTS). Let's look at how the scripts use these objects.

When a Customer Support staff member executes Mail1.asp, the script calls one of the ASP built-in objects—the Request object—into action. The script uses the Request object's ServerVariables collection to retrieve the value of the REMOTE_USER environment variable. In this case, the REMOTE_USER environment variable contains the username of the person who connected to IIS to access Mail1.asp. If a domain name precedes the username (e.g., mydomain\noone), the script removes the domain name and backslash (\). The script then appends the email domain name (e.g.,@domain.com) to the username and posts the resulting email address to the form, as Figure 1 shows. The Bulk Email Sender system assumes that users' logon names are the prefix to their email addresses. If this assumption isn't valid, the REMOTE_USER environment variable doesn't work and the From field will remain blank. If you don't want to leave this field empty, you can hard-code a specific email address in Mail1.asp.

Next, Mail1.asp uses an ODBC System data source called Email to query an Access database called EmailAddresses. To connect to the EmailAddresses database and access its contents, the script uses ADO's Connection and Recordset objects, respectively. The EmailAddresses database contains customers' email addresses. The customers are in groups according to their geographic location, and each customer group has a separate table. Each table has one column, or field, called EmailAddress, which Figure 3 shows. Each record in the EmailAddress field contains an email address.