Word can pull data for a mail merge from a variety of data source types. Because Word is part of the Office suite, it's only natural that mail merge readily accepts data from Outlook, Excel, Access, Microsoft Works Database, and even other Word files (provided the data is in a formatted table). It also accepts data from a wide variety of other non-Microsoft sources too, including dBASE, Paradox, and Lotus 1-2-3, as well as from delimited data files. Or if the data source you need does not already exist, you can create a new data source via Word's own interface.
A delimited data file is one that represents multiple columns without actually having any column lines. The break between columns is represented by a consistent character between entries, such as a comma or tab. Rows are represented by paragraph breaks.
For example, here's a plain-text delimited version of a table with three rows and three columns, with names in each cell:
Tom,Dick,Harry
Barbie,Ken,Skipper
Mary Ann,Ginger,Gilligan
Delimiters enable plain-text files to serve as databases. Almost all database programs can import and export delimited text files, which makes it possible to exchange data between almost any database program.
Delimited files also make it possible to do a Word mail merge based on virtually any data from any database program. If Word does not directly support mail merging from that database program, export the data to a delimited text file and then use the delimited text file for your Word merge.
Because Outlook is the default email and contact management program for Office 2003, Word makes it easy to use Outlook as the data source for a mail merge. The fields are already mapped between the two programs, so Outlook's address fields fit perfectly into the <<AddressBlock>>
field, for example. There's little you need to do in the way of setup if you're planning on using an Outlook file for the merge.
In the "Performing a Letter Merge with the Mail Merge Wizard" section, you saw how to choose Outlook as a data source from within the Mail Merge Wizard. It's easiest to use this wizard when selecting an Outlook source:
Note
After you select a valid Outlook contact list, the Mail Merge Recipients button becomes available on the Mail Merge toolbar. You can click it to return to the Mail Merge Recipients dialog box at any time to further restrict or filter which records will be included. If the Edit Recipient List button remains unavailable, try again to select the Outlook contact list.
As mentioned earlier, Outlook is only one possible program from which you can draw data. Word can accept data from a variety of data sources, including Excel worksheets, Word tables, plain-text files, and files from various database programs including Paradox and dBASE. You can also pull data from server-based SQL and Oracle databases.
An Excel worksheet works well as a data source for a mail merge, provided you set it up according to these guidelines:
Some people set up an Excel worksheet with a sheet title in cell A1, a few blank rows, and then the data after that. Word cannot interpret such a layout as a valid data file for a merge, though, so you need to edit the Excel file beforehand to omit any rows at the top that are extraneous to the data. Figure 1.6 shows an Excel file that is correctly set up.
Figure 1.6. This Excel file is properly configured to be used in a Word mail merge.
In addition, if you are planning on using the <<AddressBlock>>
field to create an address block in the main document, rather than inserting individual fields, the field names in the first row of the worksheet should be similar to the field names used in Outlook: First Name, Last Name, Address, City, State/Province, ZIP/Postal Code, and Country/Region. The closer you get to that naming convention, the more flawlessly the data will map to Word's <<AddressBlock>>
field. (The data file shown in Figure 1.6 would not work very well for that because its fields are too broad. Each is designed for a separate entire row of an address label. If you were using this as your data file, you would want to insert individual field names rather than an address block.)
To select an Excel file as a data source, follow these steps:
Figure 1.7. Select the sheet that contains the data to be used in the mail merge.
Tip
You can rename the tabs in an Excel workbook by double-clicking a tab and typing a new name (in Excel). The names you assign appear in the Select Table dialog box if you have assigned any; otherwise, the sheet names appear generic, like those seen in Figure 1.7.
Figure 1.8. Check the fields by displaying the Insert Merge Field dialog box.
The field names should appear in the Fields list as they appear in the top row of the worksheet. If generic names appear there instead, check the file in Excel to make sure the field names appear in row 1.
A Word file can serve as a data source, provided that one of these conditions is true:
To choose a Word document, follow these steps:
In a data source file in which the fields are delimited, you might need to specify which character is used as the delimiter. Word will prompt you if this information is needed. Other than that, the steps are pretty straightforward:
A fixed-width data file is one in which the break from one field to another in a record is not indicated by any particular character, but instead is decided by a fixed number of characters within the field. For example, in the following list, the field containing first names is 9 characters in size. There are six spaces after "Tom" but only three spaces after "Cheryl" and one space after "Marilynn."
Word cannot use a fixed-width data file in a mail merge; it accepts only delimited or tabular data files. However, Excel can import fixed-width data files. In Excel, choose Data, Import External Data, Import Data and then select the file containing the data. Excel takes you to a wizard that enables you to select the column widths and import the data. Save your work in Excel and then use that new Excel file as your data source for the mail merge in Word.
Word can connect to a variety of database server types, including SQL and Oracle. You set up a data source once, and then you can reuse it without having to go through its configuration again.
To set up a SQL or Oracle database, follow these steps:
Figure 1.9. Choose the type of data source you want to set up.
In the future, you can select the shortcut you just created from the Select Data Source dialog box (step 1) and bypass the other steps.
Open Database Connectivity (ODBC) is a widely used standard for connecting to databases. Data sources that use ODBC use SQL to connect, but they go through an ODBC driver installed in Windows. Using ODBC, you can access Excel workbooks, Access databases, dBASE databases, and other types of sources too if you have ODBC drivers for them, stored either on a server or on a local or network client. For example, if your company's customer database is stored in Access on the main server, you could access it via ODBC for your mail merge.
Note
You can also specify Excel and Access files as data sources by choosing them directly from the Select Data Source dialog box, as you would any file. In fact, that's what you did earlier, in the section "Choosing an Excel Data Source." The process is basically the same except the method described in the following steps sets up an .odb
shortcut to the file for easier access.
Follow these steps to connect to an ODBC data source:
In the future, you can select the shortcut you just created from the Select Data Source dialog box (step 1) and bypass the other steps.
If the data source does not already exist, you might find it easier to create it from within Word than to fire up some outside program to create it. Word makes it easy to create a simple Access database to hold mail merge data for a personal contacts mailing list, even if you do not have Access installed on your PC and don't know anything about that program. It's all just fill-in-the-blanks.
To create a new data source, follow these steps:
Figure 1.10. Enter a record.
Caution
You can open this database in Access if you have Access installed. Do not change the table name or structure, though, or the connection to your main document in Word will no longer work.
To use the new database in future mail merge projects, choose it as an existing data source, as you learned earlier in "Using an ODBC Data Source."
The fields provided for a new data source are typical for a personal mailing list, including name, address, and phone number. Perhaps you do not need or want all those fields, or maybe you need some different fields instead or in addition. For example, if you are creating a new data source to store inventory or events, the default fields need to be almost totally changed.
Here's how to customize the fields in the New Address List dialog box:
Figure 1.11. Change the columns (fields) that appear in the Customize Address List dialog box.
Normally, when you pick database records for your mail merge, you are not editing the data source; you're simply changing your usage of it. For example, if you exclude a certain person from a mailing, that person's record still exists in the database. To edit the database itself, you would normally go through the database program's own interface.
However, if you created the database from within Word, as in the preceding sections, then Word might be the only means you have of editing that database, particularly if you do not have Access installed.
To edit the data source from within Word, follow these steps:
•To create a new entry, click New Entry and fill in the fields.
•To delete a record, click the gray box to its left to select it and then click Delete Entry and click Yes to confirm.
•To change the columns, follow the steps in the earlier section "Customizing Fields."
4. Click Close to accept the changes to the data source.
5. Click OK to close the Mail Merge Recipients dialog box.