How to create a form letter by using information from an Excel 2002 worksheet in Word 2002 (318118)

SUMMARY

This article explains how to use the Mail Merge feature in Microsoft Word to create and print form letters by using data from a Microsoft Excel worksheet.

When you use the Word 2002 Mail Merge feature, Word merges a main document with a recipient list to generate a set of output documents:

• The main document contains the basic text that is the same in all of the output documents. It may contain a letterhead, text, and instructions in merge fields for inserting text (such as recipient names and addresses) that vary from one output document to another.
• The recipient list is a database -- for example, a Microsoft Access 2002 database file or an Excel 2002 worksheet -- that contains the data that is to be merged into the output documents. This database is typically a list of names, addresses, phone numbers, and other categories of personal information.
• The output documents are the result of the mail merge. The text in an output document can be the same in all output documents, but you can apply formatting to specific documents.

back to the top

Step 1 - Set Up the Excel Data File

Before you proceed with the Mail Merge Wizard, make sure that your Excel worksheet is well structured for this purpose. Note the following requirements for the data table:

• The first row should contain field names for each column -- for example, Title, Salutation, First Name, Middle Name, Last Name, Address1, and Address2.
• Each field name should be unique.
• Each row should provide information about a particular item. For example, in a mailing list, each row might include information about a particular recipient.
• The table should contain no blank rows.

Create your Excel data file, and then arrange it by using the fields that you want to use for your letter, as shown in the following sample data file.

Last Name	First Name	Title	Address	City	Postal Code	Country
Davolio	Nancy	Sales Representative	507 - 20th Ave. E. Apt. 2A	Seattle	98122	USA
Fuller	Andrew	Vice President, Sales	908 W. Capital Way	Tacoma	98401	USA
Leverling	Janet	Sales Representative	722 Moss Bay Blvd.	Kirkland	98033	USA
Peacock	Margaret	Sales Representative	4110 Old Redmond Rd.	Redmond	98052	USA
Buchanan	Steven	Sales Manager	14 Garrett Hill	London	SW1 8JR	UK
Suyama	Michael	Sales Representative	Coventry House Miner Rd.	London	EC2 7JR	UK
King	Robert	Sales Representative	Edgeham Hollow Winchester Way	London	RG1 9SP	UK
Callahan	Laura	Inside Sales Coordinator	4726 - 11th Ave. N.E.	Seattle	98105	USA
Dodsworth	Anne	Sales Representative	7 Houndstooth Rd.	London	WG2 7LT	UK

After you create your Excel data file, save it, and then close the data file.

For additional information about how to create a mail-merge address list, click the following article number to view the article in the Microsoft Knowledge Base: back to the top

Step 2 - Set Up the Main Document

1. Start Word 2002, point to Letters and Mailings on the Tools menu, and then click Mail Merge Wizard.
2. Under Select document type, click Letters.
   
   The active document becomes the main document (the document that contains the text and graphics that are the same for each version of the merged document -- for example, the return address or salutation in a form letter).
3. Click Next: Starting document.
4. Do one of the following:
   • Start with the document currently shown in the document window. To do this, click Use the current document.
     
     You can then either type the letter in the document window or wait until the wizard prompts you to do so in a later step.
   • Start with a template:
     1. Click Start from a template.
     2. Click Select template.
     3. On the Mail Merge tab, select the template that you want in the Select Template dialog box, and then click OK.
   • Start with an existing document:
     1. Click Start from existing document.
     2. In the Start from existing box, select the document that you want, and then click Open.
     3. If you do not see the document, click More files, and then click Open. In the Open dialog box, locate the document that you want, and then click Open.
5. Click Next: Select recipients.

back to the top

Step 3 - Specify the Excel Data Source

1. Under Select recipients, click Use an existing list.
2. Click Browse.
3. In the Select Data Source dialog box, locate and then click the Excel worksheet that you want to use.
   
   By default, Word opens the My Data Sources folder.
4. Click Open.
5. If your Excel worksheet contains information on multiple tabs, you must select the tab that contains the information that you want, and then click OK.
6. All of the entries in the data source appear in the Mail Merge Recipients dialog box, where you can refine the list of recipients to include in the merge.

back to the top

Step 4 - Select the Recipients

1. In the Mail Merge Recipients dialog box, select the recipients that you want to include. To do this, use any of the following methods:
   • Use the check boxes to designate recipients.
     
     This method is most useful if your list is short. Click to select the check boxes next to the recipients that you want to include, and click to clear the check boxes next to the recipients that you want to exclude.
     
     NOTE: If you know that you want to include most of the list in your merge, click Select All, and then clear particular records. Similarly, if you want to include only a few records in the list, click Clear All, and then select the records that you want.
   • Sort items in the list.
     
     This method is useful if you want to see items in alphabetical or numeric order. Click the column heading of the element that you want to sort by. For example, if you want to display the list alphabetically by last name, click the Last Name column heading.
   • Filter items in the list.
     
     This method is useful if the list contains records that you know you do not want to see or include in the merge. After you have filtered the list, you can use the check boxes to include and exclude records, as described earlier. To filter the list, follow these steps:
     1. Click the arrow next to the column heading of the element that you want to filter by.
     2. Click any of the following:
        • (Blanks) -- displays all the records in which the corresponding field is blank.
        • (Nonblanks) -- displays all the records in which the corresponding field contains information.
        • If your data source contains records that share the same information, and there are 10 or fewer unique values in the column, you can filter by specific information. For example, if there are multiple addresses that list Australia as the country/region, you can filter on Australia.
   • The Mail Merge Recipients dialog box displays only the designated records. To display all the records again, click (All).
   NOTES:
   • For advanced sorting and filtering, click the arrow next to any column name, and then click (Advanced). Use the Filter Records and Sort Records tabs to set up the sorting or filtering query that you want.
   • If you have installed address validation software, you can click Validate in the Mail Merge Recipients dialog box to validate your recipients' addresses.
2. : Click OK to return to the Mail Merge Wizard.
   
   Word uses the recipients that you designated for the merge.
3. : Click Next: Write your letter.

back to the top

Step 5 - Complete the Letter and Add Merge Fields

If you have not already done so, in the main document, type the text that you want to appear in every form letter.

Insert Merge Fields

Insert merge fields where you want to merge names, addresses, and other information from the data source. To insert merge fields, follow these steps:

1. In the main document, click where you want to insert the field.
2. Insert any of the following:
   • Address block with name, address, and other information:
     1. Click Address block.
     2. In the Insert Address Block dialog box, select the address elements that you want to include and the formats that you want, and then click OK. For help on an option, click the question mark, and then click the option.
        
        
     3. For help on an option, click the question mark, and then click the option. If the Match Fields dialog box appears, Word may not be able to find some of the information it needs for the address block. Click the arrow next to (not available), and then select the field from your data source that corresponds to the field required for the mail merge.
   • Greeting line:
     1. Click Greeting line.
     2. Select the greeting line format, which includes the salutation, name format, and following punctuation.
     3. Select the text that you want to appear in cases where Word cannot interpret the recipient's name -- for example, when the data source contains no first or last name for a recipient, but only a company name.
     4. Click OK.
     5. If the Match Fields dialog box appears, Word may not be able to find some of the information it needs for the greeting line. Click the arrow next to (not available), and then select the field from your data source that corresponds to the field required for the mail merge.
   • Other fields of information:
     1. Click More items.
     2. Do one of the following:
        • To select from address fields that will automatically map to corresponding fields in your data source, even if the data source's fields do not have the same name as your fields, click Address Fields.
        • To select from fields that always take data directly from a column in a database, click Database Fields.
     3. In the Fields box, click the field that you want.
     4. Click Insert, and then click Close.
     5. If the Match Fields dialog box appears, Word may not be able to find some of the information it needs to insert the field. Click the arrow next to (not available), and then select the field from your data source that corresponds to the field required for the mail merge.
        
        NOTE: If you insert a field from the Database Fields list, and then later switch to a data source that does not have a column with the same name, Word cannot insert that field information into the merged document.
   • Electronic postage: To add electronic postage, you must first install an electronic postage program, such as one that you can purchase from a third-party provider on the Web. To use electronic postage, follow these steps:
     
     
     1. Click Electronic postage.
        
        If you do not have an electronic postage program installed, Word prompts you to install one, and offers to connect to the following Microsoft Office Web site:

        Print Online Postage
        http://office.microsoft.com/services/service.aspx?sid=2.4

     2. Insert the postage according to the program's instructions.
   • To add electronic postage, you must first install an electronic postage program, such as one that you can purchase from a third-party provider on the Web. To use electronic postage, follow these steps:Postal bar code: You must select a letter or envelope type that supports the POSTNET bar code. To use the Postal bar code, follow these steps:
     
     
     1. Click Postal Bar Code.
     2. In the Insert Postal Bar Code dialog box, select the appropriate address fields.
        
        NOTE: The Postal Bar Code option appears only if you are using the U.S. language version of Word.
     3. Repeat steps a and b for all the fields that you want to insert. NOTES:
        • You cannot type merge field characters (" ") or insert them by using the Symbol command on the Insert menu.
        • If the merge fields appear inside braces, such as { MERGEFIELD City }, Word is displaying field codes instead of field results. This does not affect the merge, but if you want to display the results instead, right-click the field code, and then click Toggle Field Codes on the shortcut menu.
     For example, by using the sample database shown earlier, your letter might contain the AddressBlock and GreetingLine fields, and therefore your first page would appear similar to the following:

     February 26, 2002
     
     AddressBlock
     
     GreetingLine
     
     Type your letter here.
     
     Sincerely,
     
     Type your name here
     
     

   NOTE: You can also use the Mail Merge toolbar to insert merge fields, work with your mail-merge main document, or run a mail merge. To display the Mail Merge toolbar, point to Letters and Mailings on the Tools menu, and then click Show Mail Merge Toolbar. The Mail Merge toolbar provides additional commands not included in the Mail Merge Wizard task panes. For example, you can use the Insert Word Field menu on the Mail Merge toolbar to insert Word fields for controlling the merge process (for example, an IF field that inserts text only if a particular merge field has a specified value). Or, you can click Check For Errors to make Word run the mail merge and report any errors contained in the main document.

back to the top

Change the Format of the Merged Data

To format merged data, you must format the merge fields in the main document. Do not format the data in the data source, because its formatting is not retained when you merge the data into the document. To change the format of the merged data, follow these steps:

1. In the main document, select the field that contains the information that you want to format, including the enclosing merge field characters (<< >>).
2. On the Format menu, click Font, and then select the options that you want.

Formatting by Using Field Codes

To control other aspects of formatting, press ALT+F9 to display field codes, and then add switches to the merge fields. When you work with fields, a switch is a special instruction that causes a specific action to occur. Generally, a switch is added to a field to modify a result.

For example:

1. To display the number 34987.89 as $34,987.89, add the Numeric Picture switch (\#).
2. To print client names in uppercase letters, add the Format switch (\*).
3. To make sure that the merged information has the same font and point size that you apply to the merge field, add the Charformat switch (\*).

back to the top

Step 6 - Saving the Document

After you have completed the main document and inserted all of the merge fields, make sure that you save the document before proceeding:

1. Click Save As on the File menu.
2. Name the document, and then click Save.
3. Click Next: Preview your letters.

back to the top

Step 7 - Preview the Letters and Fine-Tune the Recipient List

When the wizard displays the Step 5 Mail Merge task pane, it replaces each of the merge fields in the main document with the actual text from the first entry of the recipient list, and therefore you can see how your first output document will look.

For example, if you were to continue to use the sample database shown earlier, the first page should appear similar to the following after you click Next: Preview your letters: To preview additional entries, do either of the following:

• To preview the items in order, click the left or right arrow buttons.
• To locate and preview a specific item, click Find a recipient, and then enter the search criteria in the Find Entry dialog box.

Fine-tune the recipient list if you want. To do this, use one of the following methods:

• To exclude a particular recipient from the merge operation, click Exclude this recipient.
• To change the list of recipients, click Edit recipient list, and then make your changes in the Mail Merge Recipients dialog box.

back to the top

Step 8 - Complete the Merge

To complete the merge, use any of the following methods.

Personalize Individual Letters

To personalize individual items, you actually complete the merge, and then edit the information that you want in the resulting merged document. To do this, follow these steps:

1. Click Edit individual letters.
2. In the Merge to New Document dialog box, select the records that you want to merge.
3. Click OK.
   
   Word creates and opens a new merged document. Your main document also remains open, and you can switch back to it if you want to make a change to all the documents.
4. Scroll to the information that you want to edit, and then make your changes.
5. Print or save the document just as you would any regular document.

back to the top

Print the Letters

To print the letters, do either of the following:

• If you personalized the items and the merged document is active:
  1. On the File menu, click Print.
  2. Select the options that you want.
• If you want to print directly from the Mail Merge Wizard:
  1. In Step 6 of the Mail Merge Wizard (Complete the merge), click Print.
  2. In the Merge to Printer dialog box, do one of the following, and then click OK:
     • To print all the documents, click All.
     • To print the document that you see in the document window, click Current record.
     • To print a range of documents, click From, and then type the record numbers in the From and To boxes.
  3. In the Print dialog box, select the options that you want.

back to the top

Save the Merged Letters for Later Use

If you want to edit merged letters or save them for later use, you can collect them into a single document, as follows:

1. Click Edit individual letters.
2. In the Merge to a New Document dialog box, do one of the following, and then click OK:
   • To merge all the documents, click All.
   • To merge only the document that you see in the document window, click Current record.
   • To merge a range of documents, click From, and then type the record numbers in the From and To boxes.
3. Word opens a single new document that contains all the individual letters. You can then save the document for later use, just as you would any regular document.

back to the top

REFERENCES

For more information about IF fields, click Microsoft Word Help on the Help menu, type Field Codes: IF field in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For more information about MERGESEQ fields, click Microsoft Word Help on the Help menu, type Field Codes: MERGESEQ field in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For more information about the SET field, click Microsoft Word Help on the Help menu, type Field Codes: SET field in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For more information about general field formatting switches, click Microsoft Word Help on the Help menu, type general switches in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

back to the top