Mail Merge - Without Word
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.
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
Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.
Browse Tips by Category
Search for Tips
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks