Using the Access Mail Merge Wizard
Access 2003's Mail Merge Wizard can help you create a new main merge document or
an existing main merge document from which to create form
. The Mail Merge Wizard uses a table or a query as the data source for the merge data file. The sections that follow describe two
of creating a form letter:
Using the Mail Merge Wizard to create a new main merge document whose merge data source is a Jet table
Using an existing main merge document with a merge data source from a Jet table with a filter or a select query
Access 2000 and earlier used Dynamic Data Exchange (DDE) to send mail merge data to Word. Access 2003 uses an OLE DB data source to generate mail-merge documents. OLE DB and Automation is a much more reliable method of interapplication communication than DDE. Using OLE DB also lets you take advantage of Word's filter and
features, which were unavailable from documents created with earlier versions of the Mail Merge Wizard.
Creating and Previewing a New Form Letter
When you first try a new wizard, it's customary to create a new object rather than use the wizard to modify an existing object, such as a main merge document. The following steps use the Mail Merge Wizard to create a new main merge document from records in the Customers table of Northwind.mdb:
Northwind.mdb, if necessary, and select the Customers table in the Database window.
Click the arrow beside the Office Links button to open the drop-down menu and select
erge It with Microsoft Word to launch the Microsoft Word Mail Merge Wizard.
Select the Create a New Document and Then Link the Data to It option (see Figure 8.48) to create a new main merge document using fields from the Customers table.
Figure 8.48. The Mail Merge Wizard's only dialog lets you use an existing merge document (the default) or create a new document.
Click OK to launch Word 2003, if it isn't running. Word opens a new mail merge main document, Document1, and displays the Mail Merge page of the task pane.
Click the Insert Merge Fields button to verify the available fields from the Customers table in the Insert Merge Field dialog, as shown in Figure 8.49. Click Cancel to close the dialog, and close the task pane.
Figure 8.49. When you start the mail merge process, Word opens with the Mail Merge menu and task pane page active. The Insert Merge Field dialog confirms that you selected the correct table as the merge data source.
Click the Show/Hide button on Word's toolbar to display end-of-paragraph marks. Choose
ptions, and mark the options on the View page to show space characters, tab
, and other document symbols that are usually hidden. All figures of Word 2003 in these sections were captured with the Show/Hide button active.
With the cursor at the top of the document, choose
nsert, Date and
ime to display the Date and Time dialog; choose any date format you want, mark the Update Automatically check box, and click OK to add a date field to the main document.
Add two blank lines, click the Insert Merge Fields button to display the list, and double-click to insert the CompanyName, Address, City, Region, PostalCode, ContactName, and ContactTitle fields from the Customers table to create the address section of the main document. Format the field
, which insert in a single line in Word's Normal view, as shown in Figure 8.50.
Figure 8.50. Arrange merge fields in letter format after you insert them.
Click the View Merged Data button of the Mail Merge toolbar to preview the appearance of the first of your form letters.
The form letters go only to customers in the United States, so you should check the address format for United States addresses. Click the Find Entry button on the toolbar to open the Find Entry dialog, type
in the Find text box, and select Country from the This Field list. Click Find Next to find the first U.S. record. Alternatively, type
in the Go To Record text box of the toolbar, and press enter. The preview of the form letter for Great Lakes Food Market appears as shown in Figure 8.51.
Figure 8.51. After clicking the View Merged Data toolbar button, use the Find Entry dialog to locate the record for the first U.S. customer.
To send letters to U.S. customers only, click Word's Mail Merge Recipients button to open the eponymous dialog with the current record selected. Open the Country field's list and choose (Advanced...), as shown in Figure 8.52 to specify a Word filter on the Country field.
Figure 8.52. Word 2003's Mail Merge Recipients dialog lets you choose how to filter and sort the data source for mail merge documents.
In the Filter dialog, select the Country field, if necessary, accept the default Equal To comparison, and type
in the Compare To text box (see Figure 8.53). You can create complex filters by adding additional criteria and selecting And or Or to determine the filter logic. Click the Sort tab, select PostalCode in the Sort By list, and click OK to close the dialog.
Figure 8.53. The Filter page of the Filter and Sort dialog lets you specify expressions and criteria on which to filter the data source.
Applying a filter marks for inclusion only those records that meet the filter criterion (see Figure 8.54). Click OK to close the dialog.
Figure 8.54. The Wizard marks and sorts the set of filtered records in preparation for generating the mail-merge documents.
The filtered list is applied to the mail-merge document. Click the Next Record button to display only the U.S. records in sequence.
Click the Merge to New Document button to open the dialog of the same
. Accept the All (records) option, and click OK to generate the Letters1 document that contains the 13 letters to U.S. customers (see Figure 8.55).
Figure 8.55. The merge document contains a letter for each of the 13 U.S. customers in the filtered list.
Close Letters1 and save it with a descriptive name, such as
, and do the same for Document1, the main merge document (
The two documents created in the
steps are located in the \Seua10\Chaptr08\MailMerge folder of the
CD-ROM. The merge document doesn't save the filter, so you must reestablish the filter when opening the main merge document.
Using an Existing Main Merge Document with a New Data Source
After you create a standard main merge document, the most common practice is to use different data sources to create form letters by addressee category. Word main merge documents store database and table connection data, and retain filter settings. Using Access filters or queries to restrict the recipient list usually is more
than performing the same operation in Word.
Take the following steps to use the main mail-merge document you created in the preceding section, USCustomersMerge.doc, with a data source based on a filter for the Customers table:
In Access, open the Customers table in Datasheet view, and click the toolbar's Filter by Form button.
Scroll to the Country field, open the field list and select USA.
Click the Apply Filter button to filter the table data and display only U.S. customers.
dvanced Filter/Sort to open the Filter Design window, which displays the filter criterion you applied in step 2. Drag the PostalCode field to the second column, and select an
sort (see Figure 8.56).
Figure 8.56. The Filter Design window opened by the Advanced Filter Sort command displays the filter for the Customers table with an ascending sort on the PostalCode field.
To review use of Access's Advanced Filter/Sort feature,
"Applying Advanced Filters and Sort Orders,"
s Query, give your filter a descriptive name, such as
, and click OK.
Close the Filter window and the Customers table, and click the Queries shortcut of the database window.
Double-click the filter item, fltUSCustomers for this example, to open the query result set and test the filter. Close the Query Datasheet window.
erge It with Microsoft Word button to launch the Mail Merge Wizard. With the Link Your Data to an Existing Microsoft Word Document option
(the default), click OK to open the Select Microsoft Word Document dialog.
Navigate to and double-click your main merge document, USCustomersMerge.doc for this example, in the file list. After a few seconds, Word opens, and Access's title bar flashes. Minimize Word to expose the message box, shown in Figure 8.57. Click Yes to change to the new data source.
Figure 8.57. When you change the data source for a main merge document, Word opens a message box.
If Word can't resolve the data source type, the Confirm Data Source dialog opens to verify use of an OLE DB data source for the Jet query. Accept the default OLE DB Database Files, and click OK to continue in Word.
If you attempt to connect to an open document, you receive a "File in Use" error message. Click Cancel, and then click OK when the "Command Failed" error message appears. Close the merge document, and try again.
Restore Word and confirm that the filter is the new merge data source—[fltUSCustomers] in "Northwind.mdb"—in the task pane's Mail Merge page (see Figure 8.58). Double-check the list by clicking the Mail Merge Recipients button. Only the filtered records appear in the list. Click OK to close the list.
Figure 8.58. The Mail Merge task pane page confirms the document is using the fltUSCustomers filter.
You can merge the main document and data source directly to the printer, spam the customers via email, send faxes, or create a series of form letters in a new document, which is identical to the sample document you created in the preceding section.
If you close Word at this point, be sure to save your changes to the main mail-merge document.