| Downloads |
|---|
| 24590.zip |
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.
In addition to the tables for the customer groups, the EmailAddresses database contains a table called Test (which I discuss later) and an indexing table called TableNames. As Figure 4 shows, TableNames contains one field whose records are the names of all the other tables in the database. In Mail1.asp, the Email data source directs all queries to this indexing table, as the excerpt of code in Listing 1 shows. The script displays the indexing table's list of table names in the form's To drop-down list box. The Customer Support staff member then selects the appropriate table (i.e., customer group) and enters the rest of the information in the form. When the person clicks Submit, Mail2.asp begins to execute.
Mail2.asp starts by requesting the sender's email address and the selected database table from Mail1.asp. Using the selected table's name, Mail2.asp queries the database and stores that table's email addresses in an array. Mail2.asp then obtains the Subject text, the Message text, and any attachment information from Mail1.asp.
As Listing 2 shows, Mail2.asp uses a For...Next statement to send an email message to every address in the table. In the For...Next loop, the script creates an instance of the NewMail object, which is the main object in CDONTS. The script uses the NewMail object's From, To, and Subject properties to set the email message's From, To, and Subject fields, respectively.
Next, Mail2.asp sets the NewMail object's BodyFormat and MailFormat properties to 0. The BodyFormat property specifies the text format. You have two options: the default value of 1 (plain text) or the value of 0 (HTML). The MailFormat property sets the encoding format. Again, you have two options: the default value of 1 (plaintext) or the value of 0 (MIME, which you need to send HTML messages). If the email includes an attachment, the script uses the NewMail object's AttachFile method to retrieve the file.
Mail2.asp uses the NewMail object's Send method to send the email, then sets the object to the keyword Nothing to get the object ready for the next iteration. The For...Next loop ends by displaying the message Mail has been sent to, followed by the email address of that email recipient, as Figure 2 shows.
If you send thousands of email messages, the process of writing and sending them might take a long time. For that reason, Mail2.asp uses the ScriptTimeout property of the Server object (another ASP built-in object) to set a timeout. Currently, the timeout is 1900 seconds. The more email messages you send, the longer this timeout should be. To change the timeout's length, you need to customize the line
Server.ScriptTimeout = 1900
in the script.
After sending the last email message, Mail2.asp displays the message Done, followed by the total number of messages sent and a timestamp. As a tracking and security measure, Mail2.asp logs all email messages and the person who sent them.
If the Customer Support staff members want to send the same email message to another group, they can click the browser's Back button and select a different group. The settings and text message they entered previously will still be available. The staff members can also use this feature for test runs of email messages. As I mentioned earlier, the database includes the Test table. After the staff members compose an email message, they can select this table and send the message to the test recipients. If the staff members are happy with the results, they can simply click the browser's Back button, choose the table that contains the targeted customer group, and click Submit to send the email message to that group.
You can find Mail1.asp, Mail2.asp, and a sample Access database in the Code Library on the Windows Scripting Solutions Web site (http://www.winscriptingsolutions.com). To use Mail1.asp and Mail2.asp, your machine needs to be running Windows 2000 Server or NT Server 4.0; Microsoft IIS 4.0 or later; Internet Explorer (IE) 4.0 or later; and Access 2000 or Access 97. You must enable IIS's SMTP service. I set the access option of Windows NT Challenge/Response Authentication with Script only on the virtual directory. I use NTFS or share permissions to control access to the ASP scripts and the database.
Explore the Possibilities
The Bulk Email Sender system is a simple example of how you can use ASP with VBScript. Because the code is in VBScript, you can customize the system. For example, you might combine the separate database tables into one large distribution list (DL). You might even adapt the system so that the end users can maintain the email addresses in the database. There are virtually no limitations on what you can do with this system.
