Mail Merge - Without Word

Category: Printing / General VBA | [Item URL]

A companion file is available: Click here to download

Mail merge is the process of merging information from a database into a text document, and then printing the document. The result is a series of personalized documents.

Normally, Microsoft Word is used for this type of operation. Word can perform a mail merge with data stored in an Excel workbook. The process works well, but in some cases it may be preferable to eliminate Word and do all of the work with Excel.

The basics

Using Excel to perform a mail merge requires:

• A range that contains the data to be merged. In the example, this is on the sheet named Data.
• A range that contains the text. In the example, this is on the sheet named Form.
• A cell that contains the row number of the "current record." In this example, that cell is C3.
• Formulas that use Excel's INDIRECT function to retrieve data from the current record. These formulas are in column L.
• Formulas within the text that refer to the cells in column L. The example uses several such formulas to personalize the letter.
• A simple macro that loops through the data, incrementing the current record number and printing the sheet.

This example, of course, can be adapted to many other purposes.

This example has a few additional features:

• The ability to specify the first and last records from the database
• Buttons to assist in navigating through the workbook
• An option to preview instead of print
• A Help button that displays instructions