\[Editor's Note: Email your Exchange Server and Outlook solutions (400 words maximum) to R2R at [email protected] Please include your phone number. We edit submissions for style, grammar, and length. If we print your contribution, you'll get $100.\]
Creating a Batch-Based Mail Merge
Recently, I needed to quickly send some important information to 250 users. Although part of the message was the same for each user, the message also needed to include the user's unique name, PIN, and email address. However, I didn't have the time or staff to manually create individual messages.
To accomplish this task, I created two batch files. The first batch file uses Windows 2000's (or Windows NT's) For command to parse an input text file and obtain the necessary parameters (e.g., PIN). The second batch file uses the Copy and Echo commands to create unique text files containing the required data, then uses the Microsoft BackOffice Resource Kit's (BORK's) Mapisend program to send these files to the users.
I began with a source text file (sourcedata.txt) that contained 250 lines—one line for each user. I used the Microsoft Exchange Administrator program's export facility to create the base Comma Separated Value (CSV) file because I had to ensure that I had the correct alias, full user name, and so on. I had to remove the lines for the more than 1500 user entries not pertinent to my specific task. This task was fairly simple because the export file included membership on distribution lists (DLs) and my users all belonged to the same lists. I sorted by list name, then easily deleted lines for the extraneous users. Microsoft Excel is a great tool for manipulating this kind of file.
The batch file that Listing 1 shows uses the For command to read sourcedata.txt one line at a time, obtain the required parameters, and pass them off to the batch file that Listing 2 shows. The four parameters, in order, for each user are lastname, firstname, PIN, and alias. Line 1 in Listing 2 creates a text file for each user by using passed parameters to name the file (e.g., %2%1.txt becomes FredSmith.txt) and inserts some boilerplate text and the recipient's firstname (i.e, %2). Line 2 appends more standard text to the new file created in Line 1. Line 3 appends more standard text and the user's PIN (i.e., %3). Line 4 appends more standard text. Line 5 uses a variety of Mapisend options to send the file to the user. The -u parameter causes the message to appear to come from the Messaging API (MAPI) profile I chose. This profile has to exist on the machine sending the message. I used a special profile (sendmail) created just for this admin type of message. A copy of each message and any nondelivery responses (NDRs) are automatically sent to the mailbox for this profile. The -p parameter is the password for the sendmail profile, the -r parameter is the recipient (actually, the user's alias), the -s parameter is the subject line for the message, the -t parameter is the uniquely created text file for each user that I used as content for the message (i.e., FredSmith.txt), and the -f parameter lets me attach a file.
Many methods undoubtedly exist for solving this problem, but this method is simple and fast. In addition, the Mapisend options let me customize the information to meet my needs.