Access: Mail Merge Using Access Data

WRITE THE LETTER

Begin by creating your letter or communication in Microsoft Word. For example, Mike’s Music planned a party and big sale to celebrate American Independence Day and sent out a letter to the customers and other individuals in its database (see Figure 1). The letter announced the event and other details. It also included a two-column table with invisible border lines that shows the address for Mike’s Music in one column and the name and address of the customer in the other.

Below the addresses was some additional text that would only be displayed if a certain condition was true. The letter included graphics and images as well—the beauty of using Word to write the letter is that it’s easy to get it to look the way you want.

THE DATA IN ACCESS

Create a query or temporary table in Access that contains the data that you want included in the letter. Originally, Mike made a query called “qMAILMerge” that contained all the fields he needed to reference. In Word, however, the query didn’t appear on the list of objects to use for the merge. He had to go back to Access and make a temporary table (Mail_Merge) from the query he’d built and use that instead since Word will show all tables but not necessarily all queries when choosing a data source.

Mike included several fields in his table:

• WhoTo: the name to use in the salutation
• NameFirstLast: the name to use in the address block
• MailAddress: the mailing address (with line breaks)
• TextNbr: An integer (0, 1, or 2) to indicate if conditional text will be printed

THE MERGE DOCUMENT

Once the data is set up, go back to the Word document and begin the mail merge. Go to Mailings, Start Mail Merge, Step-by-Step Mail Merge Wizard. This opens the Mail Merge pane. Follow these steps:

1. Select document type: Choose “Letters” (default) to send letters to a group of people. Click Next.
2. Select starting document: Choose “Use the current document” (default). Click Next.
3. Select recipients: Choose “Use an existing list” (default), then click Browse to open the Select Data Source dialog.
4. Select data source: Navigate to your Access database (such as StoreContacts.accdb) and click Open.
5. Select table: A dialog box appears with a list of tables and queries in the database. Choose the correct table or query.

Once the data source has been identified, the next step is to add the merge fields to the Word document. Place the cursor in the address block where the name will go. In the ribbon, click on Insert Merge Field to see the list of field names. Choose NameFirstLast, and Word will add <<NameFirstLast>> to the document. Move the cursor to the next line and insert the MailAddress field. Finally, insert the WhoTo field in the salutation.

Now comes the tricky part—the conditional text at the bottom. Mike’s event included a private party and a camping event for specific customers and friends, so he wanted to include conditional text blocks in the letters for the respective invitees.

This involves writing an IF statement within the Word doc. To do that requires seeing the field codes. Go to File, Options, Advanced. In the Show Document Content section, check the box to “Show field codes instead of their values.” Click OK.

Place the cursor at the end of the document and press Ctrl+F9 to add a field. Word will add an empty set of braces, also called curly brackets, with two spaces between them. Click inside the braces, and position the cursor after the first space. Then add if { MERGEFIELD TextNbr } > 1 " "   " " between the braces. To get { MERGEFIELD TextNbr }, go to Insert Merge Field and select TextNbr.

Within the first set of quotation marks, include the text you want to display if the formula is true. Leaving the second set of quotation marks empty means nothing will be displayed if the formula is false. For example, for the camping event text, the end result in Word is { if { MERGEFIELD TextNbr } > 1 "Bring a sleeping bag if you want to camp."   " " }.

Make sure there are spaces between everything! A space is a delimiter within the field braces, much like a comma is used in an Excel formula.

FINISHING THE MERGE

To see the results, go back to File, Options, Advanced and turn off the display of field codes. Then go to Mailings, Preview Results. Cycle through the records to spot-check the results.

If everything looks good, click Finish & Merge on the ribbon. Choose Edit Individual documents, then All records. A new Word document will be created with the results of your merge.

Download this month’s files: SF2008_Database_WordLetter.

SF SAYS

Use Microsoft Word to write letters and then personalize them with data from Microsoft Access by doing a mail merge.