My company maintains a large email address list and often needs to send mail to hundreds of people. After evaluating several techniques, I settled on using the Microsoft Exchange Event Service to send messages to many different distribution lists (DLs). Here are the solutions I considered and rejected, the technique I developed, and some tips to make the process run smoothly. I'm grateful to Sue Mosher for elaborating on the process and for making other suggestions that improved the article.
My first idea for managing large mailings to diverse recipients—to cut and paste all the addresses into the To field of a message—wasn't efficient. Another idea was to use a UNIX script that sends mail using a legacy UNIX box, but this process was very slow and hard for the administrative assistant to administer. When my company implemented Exchange Server, I considered creating custom recipients in the Global Address List (GAL) and then adding them to DLs. I decided against this approach, because mailings go to thousands of addresses that change often, and the company didn't want to give the administrative assistant permission to add, remove, or modify accounts on the Exchange server. I also wanted each message to be addressed to the individual recipient, not to an impersonal DL name.
Exchange Server's Better Solution
The Event Service offered a better solution, that didn't require additional software: You can install this Exchange Server 5.5 add-on from your Exchange Server 5.5 CD-ROM. The administrative assistant uses Notepad to maintain each list as a simple text file containing one email address per line. The addresses in the text file come from many sources, including email messages, SQL databases, and spreadsheets.
The company manages all the lists with one Exchange Server mailbox and a folder for each DL. The mailbox also has one secondary SMTP email address for each DL. To send to a list, the administrative assistant sends a message to the list's SMTP address. When the mailbox receives the message, a Rules Wizard rule qualifies the sender, then moves the message to the list's folder. From the folder, the script opens the corresponding text file full of email addresses and generates an individually addressed copy for each address. To implement this system, you need to know how to
- Install the Event Service
- Set up mailboxes to handle the DLs
- Write and install a script that uses the Scripting.FileSystemObject model to read addresses from a text file and Collaboration Data Objects (CDO) to generate an individual message to each recipient
Install the Event Service
Follow these steps to load the Event Service:
- Run Setup from your Exchange Server 5.5 CD-ROM.
- In the Microsoft Exchange Server Setup dialog box, click Add/Remove.
- Select Microsoft Exchange Server, then click Change Option.
- Select Microsoft Exchange Event Service, then click OK.
- Click Continue.
- Type your service account password, then click OK.
- After Setup finishes, reapply any Exchange Server service pack you've loaded.
After you install the Event Service, you must give a mailbox the appropriate permissions to create scripts. I suggest creating a separate mailbox just for scripting. My mailbox is called ExUser. Note that the return address for this mailbox is what DL recipients see as the From address. To create an ExUser mailbox and give it permission to write and execute server event scripts, follow these steps:
- In the Exchange Administrator program, connect to the server where you'll be running Event Service.
- Use File, New Mailbox to create a mailbox named ExUser. For the Windows NT account for this mailbox, use the account that you usually use to administer the Exchange server.
- On the E-mail Addresses tab, add a new SMTP address for each DL that you plan to manage in the folder.
- Select the address you want to use as the reply for messages that the DL scripts send.
- Click OK when you finish configuring the mailbox.
- In the Exchange Administrator hierarchy, go to Folders, System Folders, Events Root and bring up the properties for EventConfig_SERVER, where SERVER is the name of the Exchange server running the Event Service.
- On the General tab, click Client Permissions.
- Add the ExUser mailbox to the permissions list, and grant it the Owner role.
- Click OK twice to save the permission changes.
Configure a Client for Script Installation
Although you set permissions on the Events Root with the Exchange Administrator program, you must install scripts through the Outlook client. Specifically, you must run Outlook with a profile that connects directly to a mailbox to which you've given Owner permission on the EventConfig_SERVER system folder.
You can install scripts from any workstation on which you can log on to Outlook with the new ExUser account. Configure an Outlook profile to point to the ExUser mailbox. The only service you need to install in the profile is the Microsoft Exchange Server service.
After creating the profile, start Outlook with it. To verify that you've installed the Server Scripting add-on, choose Tools, Options, Other, Advanced Options, Add-in Manager. If you don't see Server Scripting, click Install, select Scrptxtn.ecf from the list of add-ins, click Open, and then click OK three times to complete the installation.
Add a Folder, Script, and Address File
When you're running Outlook and connected to the ExUser mailbox, you're ready to configure a folder to hold the DL messages and its matching script. Follow these steps:
- Create a new folder in the mailbox for each DL. For example, one of my company's lists uses a folder named CustomersDL and an SMTP address of [email protected] Each DL has a folder and script, all managed from within the ExUser mailbox.
- Right-click the folder, and select Properties.
- On the Agents tab, click New.
- In the New Agent dialog box you see in Screen 1, under When the following event(s) occur, select the A new item is posted in this folder check box. Click Edit Script to open the shell for the script in Notepad. Replace the shell completely with the script in Listing 1, modifying
Set a = fso.OpenTextFile("C:\Mail\CustomersDL.txt", 1)to use the path on the Exchange server to the text file that contains the addresses.
- Close Notepad. At the prompt, click Yes to save changes, then click OK twice.
The text file this script uses consists simply of one email address per line, like this:
You need to store the text file on the Exchange server because the script will run on the server and not on the machine on which you're running Outlook to install the script. Be sure that the path for the text file in the script is a valid path on the server.
Test the Script
Never put a script into final production without testing it first. For your tests, put your email address in the CustomersDL.txt file two or three times. If you plan to use the list to send to people outside your organization, make sure you test with external Internet addresses, not just your mailbox's SMTP address.
To test the script, copy any message to the CustomersDL folder in the ExUser mailbox. After a minute or two, check that Exchange has sent the message you copied to the CustomersDL folder to each email address in the C:\Mail\CustomersDL text file. Each recipient will receive an individual copy of the message.
The final step is to create and test Inbox Assistant or Rules Wizard rules that move items sent to each DL's address to the corresponding folder containing the script that will send the message to the recipients. You assigned each list an SMTP address when you set up the mailbox. If you add folders for new lists later, don't forget to go back to the Exchange Administrator program and assign a matching SMTP address.
For the CustomersDL folder, create a rule that moves all mail sent to [email protected] to the CustomersDL folder. You can also qualify the sender by adding a condition specifying authorized senders in the From field. That way, only authorized users can send to the DLs. Screen 2 shows a complete rule to move messages and qualify senders.
Be sure to test the rules by sending messages to the different lists' addresses. Because this system runs unattended, make sure you test the rules while you're not logged into the ExUser mailbox to ensure that the rules are running on the server.
Usage and Administration
Using this system is easy. The administrative assistant just composes the email message and sends it to the list's address. The rule moves it to the appropriate folder, then the script forwards an individual copy of the message to each person whose address is in the text file for the particular folder's script.
Administration is a matter of keeping the text file updated and adding or removing DLs as needed. The Exchange administrator (or the administrative assistant, if you've granted appropriate folder permissions) also can add the ExUser mailbox to his or her Outlook profile—in Tools, Profile through the Microsoft Exchange Server service—and monitor the activity in the mailbox or update scripts. Remember, though, that to update rules, you must run Outlook logged on directly to the mailbox.
I usually don't log messages that the assistant sends out. However, if you want to add logging, you can do it in several ways. My script deletes the incoming messages from the administrative assistant after Exchange has relayed them to the DL. Perhaps the easiest way to add logging is to leave the messages in the ExUser mailbox. To leave the messages, remove this line from the script:
Alternatively, you could add an Inbox Assistant or Rules Wizard rule to copy the message to a separate Archive folder, before the rule moves the incoming message to the DL's folder.
Another method is to add code to the script to write the log to the Event Service's intrinsic Script.Response object. This method has two drawbacks. First, the log can hold only 32KB of information. When the log is full, Exchange replaces it with a new one. The other problem is that you can read the log through only the Agent tab, which means that only someone who can log on to the ExUser account can read the log. You might want the log to be more widely available.
For a log that anyone can access, you can write information to a text file with code similar to Listing 2. Add this subroutine to the script, and call it from the Folder_OnMessageCreated subroutine before calling SendMessage().
Note that if you record just the Sender.Address and not the Subject, you can use this subroutine in a script on any folder to extract all email addresses from the sender of every message arriving in the folder. This capability is useful if you have hundreds or thousands of messages arriving in a folder that you want to use to build a new DL.
This system is relatively foolproof, but any change in your Exchange Server configuration can affect what you thought were stable operations. For example, I recently implemented a separate Exchange server just for processing these DLs and moved the ExUser mailbox to that server. For unrelated reasons, I also turned on Message Journaling on both the original and the new servers. When I turned on journaling, my rules stopped functioning correctly because Message Journaling caused the items to go through the Message Transfer Agent (MTA), which resolved the DL email addresses (e.g., [email protected]) to the scripting mailbox. The result was that the rules on the ExUser mailbox never saw the [email protected] address, only the mailbox's main address. Therefore, the rules didn't work.
My solution was to remove the [email protected] address from the ExUser mailbox and instead create a DL named CustomersDL in the GAL, with an SMTP address of [email protected] I made the ExUser mailbox a member of the list and adjusted the rule in the ExUser mailbox to look for the CustomersDL entry in the GAL. Now the rule works fine, even with the ExUser mailbox on a separate server.
The Event Service supports many more options, features, and objects. I learned most of the information I showed you by using the example files included on the Exchange Server 5.5 CD-ROM. The files are in \Server\Support\Collab\Sampler\Scripts. You can also look at the agents.hlp and cdo.hlp files on the CD-ROM. I guessed some of the object names and searched the Microsoft Developer Network (MSDN) Library for others. Some of the code is standard VBScript; you can download VBScript documentation from http://msdn.microsoft.com/scripting.
Another good resource is the CDOLive Web site at http://www.cdolive.com. The Tips and Tricks page at http://www.cdolive.com/cdo5.htm has information about changing the From address of an outgoing message. You can modify future scripts to use this technique to use each DL's email address as the From address for its outgoing messages. The messages in the script in Listing 1 are going out with the default reply address of the ExUser mailbox. Another possible modification is to add more rules and scripts to handle replies sent to the ExUser mailbox (or its folders) and to handle email that is nondeliverable because the email address in the text file is no longer correct.
Keep It Simple
Other people have suggested managing DLs with more complex scripts in public folders, but I prefer keeping the scripts simple and centralized in one mailbox and using the Rules Wizard to screen messages before redistributing them. This solution isn't perfect, but if you already own Exchange Server 5.5, this solution is free and easy to implement and maintain.