Creating a Mail Merge Data Source

Article contributed by Beth Melton

Microsoft Word supports many file formats which can be used as a Data Source for a mail merge. This article covers specifications and frequently asked questions on the most commonly used Data Sources, along with how to set up a Data Source in Word.

Overview
Using Word as a Data Source
Using Excel as a Data Source
Using Access (or any supported database) as a Data Source
Using a Text File as a Data Source
Using Outlook as a Data Source
Using Outlook Express as a Data Source

1. Overview

Break your fields down into the smallest meaningful value. For example, create a separate field for First and Last names, break the City, State, and ZIP of an address into separate fields as well. Combining information in separate fields in a merge document is easy; separating information in a field is difficult. For an example of how difficult, see: I have a "Name" column which I want to split into "FirstName", "LastName", how can I do it?

You can refer to the First name of an individual in a salutation and then use the First and Last in the inside address. However, using a separate “Salutation” field gives you much more flexibility, because it lets you use “Joe” or “Mr Bloggs” as appropriate.

If you wish to use barcodes, the delivery address and postal code must be placed in separate fields Should you wish to sort by a specific piece of your information, it must be placed in a separate field. This is important if you want to be able to view or print data in alphabetical order by last name for a mailing list or directory but in ZIP code order when creating mailing labels or envelopes to qualify for bulk mailing rates.

2. Using Word as a Data Source

Step

Comments

1.	In Step 2 of the Mail Merge Helper, select Create Data Source.

Word displays a dialog box containing a default list of field names. You can use the fields provided and add/remove fields, or remove them all and create your own field names.

The order of the fields will become the data entry order. Use the Move Up/Move Down Commands to alter the order.

When all fields have been specified, select OK and Word will ask for a file name for your Data Source file.

Field Name specifications:
	Each field name must be unique.
	Field names can be up to 40 characters long.
	Word field names cannot contain spaces.
	No more than 63 fields can be used (Word 97 and later). If more are needed, then use an alternative Data Source. (In Word 95 and lower the limit was 31 fields).
Your Data Source is stored in a Word table. The first row in the table is called the Header row. Each row contains one record, and each column one field.

3.	Next you will be asked to set up the Main Document or to Edit the Data Source. Select Edit Data Source.

Word provides you with a data entry form to enter your records. Use either <Tab> or <Enter> to move from field to field.

Once you enter the data in the last field, press <Enter> again to move to a new record. Otherwise select Add New Record.

To move to a previous field use <Shift Tab>.

4.	After all records have been entered you are ready to edit your Main Document.

To return to the Data Source, locate Edit Data Source on the Mail Merge Toolbar.

Note that you don't have to use the data entry form. If you find it more convenient (as many users do), you can work directly with the data table by pressing the View Source button in the entry form.

Moreover, you can create this data table independently before beginning the merge process. In a blank document, insert a table with as many columns as you want merge fields. The first (heading) row of the table should contain the merge field names. Don't leave a blank row below the Heading Row; if you do, you will have a blank record, and the first page, item, label, or envelope in your merge will have blanks instead of merged data. As you add records, the table will grow, and you can sort the data on any column. Save this just as you would any Word document. When using the Mail Merge Helper, instead of choosing Create Data Source in the Get Data step, you will instead use Open Data Source to select the document in which you saved the data.

3. Using Excel as a Data Source

A Data Source in Excel, referred to in Excel as a list, must meet the following specifications:
	Field names/column labels must be in the first row of the list, and field names must be unique.
	There must not be a blank row between the field names and the first record.
	If there is additional information on the worksheet, there must be at least one blank row or column separating the list from the other data.
	There should be no empty columns or rows within the list.
	If the field names/column labels are not in Row 1 of the worksheet, either create a named range for the list or add the AutoFilter feature to the list (Data/Filter/AutoFilter). The AutoFilter feature will automatically create a named range called _FilterDatabase. If you want to use lists from other worksheets, however, then using a named range is the best method. Note that if you ever add records to the bottom of the list you would need to redefine the named range. See Select Method (below) for additional details.

Question	Solution
Word tells me it can't establish the DDE connection to Excel or Excel just won't start.	In Excel, go to Tools/Options/General and turn off “Ignore other Applications”.
I can only see the first worksheet of my Excel workbook when I open my Data Source.	By default Word uses the DDE method to connect to Excel. In the Open Data Source dialog box, check the “Select Method” option to view other methods. Select Method Notes: Excel files via ODBC: Named ranges are displayed by default. To specify a worksheet, click Options and select System Tables. Microsoft Excel Worksheet via Converter will convert your worksheet into a Word document. If the file is saved in Word, it will permanently convert your Excel workbook into a Word document. You will no longer be able to open this file in Excel. Be cautious when using this method.
My ZIP codes are missing leading zeros.	In Excel, select Format/Cells, and on the Number tab in the Category section, select Special, and apply the Zip code format.
An AutoFilter is applied to my list in Excel and I'm getting blank records in Word.	In Word use Tools/Mail Merge and then select Query Options. Filter Records by one field using the “is not blank” criterion. Preferably this field should be one that would always contain an entry.
How you can use MS Query to merge from two Excel files in a single mail merge.	See KB Article Q180599

4. Using Access (or any supported database) as a Data Source

An Access table or query is already in a format that Word will recognize as the Data Source.

In addition to Access, any database for which Word has a file converter (including Foxpro, dBase, and WordPerfect and Works databases, among others) can be used as a Data Source. Also, if you use VBA to open the Data Source, any ODBC-compliant database can (at least in theory!) be used as a Data Source. And finally, any database format supported by MS Query can (in theory) be used. In the case of bespoke Database Management Systems, it is generally simpler and quicker to export a comma-delimited text file from the database and use that as the Data Source.

Question	Solution
Help! My phone numbers are not formatted correctly.	Use Alt+F9 to turn on the display of field codes. Edit the merge field for the phone number and add the following format: { MERGEFIELD strPhone \# "(###) ###'-'####" } Note the apostrophes around the hyphen. To prevent the phone number from breaking during word wrap, you can use a nonbreaking space, Ctrl+Shift+ Space, between the area code and prefix and a nonbreaking hyphen, Ctrl+Shift+ Hyphen between the prefix and number; in which case you won't need the apostrophes. However, if some of the phone numbers are stored with the symbols and others are not, this will not correct the problem . In this circumstance, Word will display the calculated result of the phone number. So the best way to correct the phone numbers is in Access rather than in Word. In an Access Query, add a calculated field that uses the Format function to correct the field. It would look something like: Phone#: Format([strPhone],"(000) 000-0000")
Why does Word start another instance of Access?	This is Word is looking for a window titled Microsoft Access. If the database has an Application Title then a new instance of Access will be started. For a workaround, see Access MVP Dev Ashish's Web site.
I have pictures stored in Access but I can't get them to merge with Word – why not?	See: How to merge in a picture from a database [Lene Fredborg, 10-Jan-2020: Removed outdated link to http://homepage.swissonline.ch/cindymeister/]

5. Using a Text File as a Data Source

Many database management systems (for example, most Human Resources systems) are difficult or impossible to merge directly to, but are very easy to export data from, as a comma delimited text file. The comma delimited file can then be used as a Data Source; or alternatively, can be imported into Excel and saved in Excel format, and the resulting spreadsheet can be used as a Data Source.

Important: It is always best to give the text file an extension of .dat. The ODBC drivers for Windows 95 are broken, but if you give your text file a "non-standard" extension, such as .dat, the ODBC driver will not recognize it, and Word's internal text converter (which works) will take over.

Specifications for using a text file as a Data Source:
	Field names should contain no punctuation, and spaces are discouraged.
	Each record ends with a paragraph mark.
	There should be no extra paragraph marks between records or at the end of the data records.
	Use the same field delimiter in both the header row and the rest of the data.
	For empty fields in the data record, include the field delimiter anyway with the exception of the last field in the last record.
	Use quotation marks if the field contains the same character you are using as the delimiter, if the text contains a paragraph mark, line break, or list separator that is specified on the Number tab in the Regional Settings of the Control Panel. Note that the default is a comma (,).
	If the information in the field contains quotation marks use double quotation marks ("" ""). Only one set of quotation marks will display in the completed merge.

Example:

"FirstName","LastName","Address1","Address2"
"John","Doe","1000 1st St.",""
"Timothy","Smith","1455 Anywhere Ln.","Apt. 1"

Question	Solution
Accented or extended characters lost in mail merge	See KB Article Q195688

6. Using Outlook as a Data Source

Question

Solution

Some Contact List fields are not available to Word

Word 97: See MS KB Article Q141874. Word 2000:
1.	Start Outlook.
2.	Under Outlook Shortcuts, select Contacts.
3.	Select Tools/Mail Merge.
See MS KB Article Q225000for additional methods.

How to modify the layout of the address.

Word 97: See KB Article Q134901.

Word 2000: A supplemental macro, Modify Address Layout, is available in Macros9.dot.

Contact information does not appear in Address Book

To verify or install the Outlook Address Book Service:
1.	In Outlook, select Tools/Services.
2.	Verify Outlook Address Book service is in the list of available information services.
To add the Outlook Address Book:
1.	Click Add.
2.	In the Add Service to Profile dialog box, select the Outlook Address Book from the list of available services.

If the Outlook Address Book Service is not an available service, see KB Article Q173072.

Mapi32.dll is an invalid MAPI library.

Word was unable to open the Data Source.

Outlook Express has been set as your default email program.

In Outlook Express go to Tools/Options and either clear the checkbox for “Make Outlook Express my default Simple MAPI client.” or “Make Outlook Express my default e-mail program.”.

C:\~~\~~~_virtual_file_~~~.olk Is Being Used by Unknown. Do you want to make a copy?

This is a temporary file that is created when you use the Outlook Address Book.

When you receive the error message click No.

A second error message should appear. When you receive it, select Options and then Remove Data/Header Source.

Note that your Main Document is no longer associated with your Data Source. You will need to reattach the Data Source via Tools/Mail Merge/Get Data.

7. Using Outlook Express as a Data Source

The Outlook Express address book was not designed for use in Word. You can open the Address Book in Outlook Express, select File/Export, and use a format that Word does support.

See Knowledge Base article How to Use the Outlook Express Address Book in Mail Merge for step-by-step instructions.