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

Search for Tips

All Tips

Browse Tips by Category

Tip Books

Needs tips? Here are two books, with nothing but tips:

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

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

© Copyright 2019, J-Walk & Associates, Inc.
Privacy Policy