Sample Application: BulkMail

BulkMail is a Microsoft Excel-based application designed to make it easier to manage mailing list data in Microsoft Excel. Microsoft estimates that over 70% of the people who use Microsoft Excel use it as a database—and why not? Anyone who can use a spreadsheet can organize data into a simple, but effective, flat-file format. There's no need for relational database theory and no need to figure out how to construct a query that returns the data you need. Microsoft Excel version 5's list feature (which treats any contiguous range as a database) make this especially easy.

BulkMail has two components:

BulkMail.XLS, a Microsoft Excel database and mail merge interface that serves as the main application controlling Word
BulkLtrtr.DOT, a Word template for creating the mail merge letter

BulkMail.XLS contains the Microsoft Excel database, which stores standard address information for contacts, and a custom menu with two commands: Update Data and Mail Merge. The Update Data command is a placeholder for a dialog box (not included in this sample application) that lets people add records, edit existing records, and delete unwanted records. Whenever you deal with applications for any database (from Microsoft Excel to Access to Oracle), you should always have an interface that hides the actual data from users, and strictly controls the type and format of data entered into the database. This prevents mistakes from corrupting the data.

The Mail Merge command on the BulkMail menu opens a dialog box that lets users select the recipients of the mailing, and then uses OLE automation to perform the mail merge in Word.

BulkMail has four main routines (stored in the module modMailMerge), which I explain in the following sections:

The ListDocFiles subroutine uses the Files function to list the Word DOC files in the current directory.
The cmdChange_Click subroutine lets users select a mail merge letter in directory other than the default directory.
The ListDistinctEntries subroutine lets users select the recipients of the mail merge letters.
The cmdOK_Click subroutine performs the query that users specified regarding letter recipients, and then performs the mail merge with Word.

Product Sheet: BulkMail
Purpose:	BulkMail demonstrates how to build a Microsoft Excel database application for managing mailing lists. It is structured so that users enter, edit, and delete data through a custom Microsoft Excel dialog box, and perform mail merges in Word through OLE automation. Although this version of BulkMail focuses on performing the mail merge with a letter already written in Word, you could expand the application to enable users to control Word through OLE automation in order to write the letter as well.
Development tools used:	Microsoft Excel, Word, Windows API, OLE Automation
How BulkMail works:	To use BulkMail: 1. Use BulkLttr.DOT to create the letter to merge. This template (which users Header.DOC as the data source) already has merge fields. 2. In Microsoft Excel, select the Mail Merge command from the BulkMail menu. 3. In the BulkMail dialog box, select the Word document to use as the letter. Next, select the criterion for selecting the letter's recipients--for example, All or Companies. 4. Click on OK when you're ready to do the mail merge.