WORD AND EXCEL | Word Annoyances: How to Fix the Most ANNOYING Things about Your Favorite Word Processor

MAIL MERGES

Perform a Basic Mail Merge

The Annoyance:

I know that Word has a mass-mailing feature somewhere. Where is it, and how does it work?

The Fix:

The feature you're looking for is called "mail merge" and is located on the Tools menu. Exactly where it is and what it's called depends on your version of Word. In Word 2003, choose Tools Letters and Mailings Mail Merge to open the Mail Merge task pane (Figure 9-3, left); in Word XP, choose Tools Letters and Mailings Mail Merge Wizard to open the wizard, which looks the same as the Mail Merge task pane. The task pane or wizard then walks you through the six steps of specifying the document type, choosing the starting document, selecting recipients or creating a new list of recipients, laying out the document, previewing the document, and then completing the merge.

In Word 2000, choose Tools Mail Merge to display the Mail Merge Helper dialog box (shown in Figure 9-3, right, with most of a merge set up). The Mail Merge Helper organizes the steps differently than the task pane or wizard; the sequence of actions is essentially the same, but they are accomplished in only three steps.

Tip: The name "mail merge" suggests that this feature is useful primarily for letters, email messages, or faxes, but it's also great for other items that use text drawn from some form of database and entered into a rigid formatfor example, catalogs and mailing labels.

The main document and the data source

For each mail merge, you need a main document and a data source. The main document is the template according to which the merged documents are laid out, so if you're creating a form letter, the main document is a letter containing placeholders (called fields ) for the information that will be merged in. The data source is the file that contains the data that will be merged into the fields in the main document to create the merge documents. The main document is always a Word document (or template), but the data source can be a Word document containing a table, an Excel spreadsheet, an Access database, your address book, or another file that contains data records.

Figure 9-3. Mail merge has a different face in Word 2003 (left) and Word XP than in Word 2000 (right), but the steps are largely the sameas are the results.

Creating or designating the data source

The way in which Word presents the mail merge suggests that you should create the main document before you designate the data source, but this is putting the cart before the horse: you can't finish creating the main document until you know the names of the fields in the data source. First, tell Word whether to use the current document as the main document or open another document. Next , designate the data source to use (or create a new data source) and place the fields in the main document.

Word offers you the choice of using an existing data source or creating a new data source. You should almost always use an existing data source, because creating a new data source usually involves replicating data stored elsewhere. It's far better to spend a couple of hours sorting out your Windows Address Book, your Outlook Address Book, or your customer list in Excel or Access so that you can use it effectively for mail merges in the future than it is to spend an hour updating a custom list each time you need to perform a mail merge. This theory holds no matter what type of data you're working with: customer names, telephone numbers , parts listed in a catalog, or product details for a special offer.

Warning: If you need to be able to create a mail merge for only some of your contacts, either put them in a different group or use one of the address fields you never use as a place for storing differentiating information. For example, in the Windows Address Book, the Conferencing Server field and the Conferencing Address box on the NetMeeting tab are rarely used.

Sometimes, however, you will need to create a new data source for a merge. To do so, select the "Type a new list" option in Word 2003 or Word XP, and then click the Create link. In the New Address List dialog box, click the Customize button to display the Customize Address List dialog box (see Figure 9-4). Add, delete, and rename fields so that you have the list you need, and then use the Move Up and Move Down buttons to shuffle the fields into a suitable order.

Click the OK button to close the Customize Address List dialog box, and enter the details of the first record in the New Address List dialog box (see Figure 9-5).

Figure 9-4. When creating a new database for a mail merge, first customize the "address list" so that it includes all the fields you need.

Figure 9-5. After setting up the fields, use the New Address List dialog box to create the records for the database.

THE MAIL MERGE HELPER

What's confusing about the Mail Merge Helper is that, because the dialog box is modal (and thus prevents you from working in a document while it is displayed), Word has to hide it so that you can edit the merge document. You then have to redisplay the dialog box manually (by choosing Tools Mail Merge again or clicking the Mail Merge Helper button on the Mail Merge toolbar) so that you can switch to another action (such as editing the data source) or perform the merge. By contrast, the Mail Merge task pane and wizard remain open throughout the merge process, making it easier to see whats happening. The task pane and wizard also include other time-saving features, such as the ability to insert an entire standard address block of fields at once rather than having to insert them one at a time.

When you've entered all the records, click the Close button and save the database when Word prompts you to do so. Word then displays the Mail Merge Recipients dialog box (see Figure 9-6) so that you can select which people (or items) to include in the mail merge. Click the OK button after you've made your choices, and you'll be ready to create the main document.

Figure 9-6. In the Mail Merge Recipients dialog box, choose the recipients for the mail merge. You can also perform quick filtering by clicking the drop-down arrows in the heading row.

In Word 2000, click the Get Data button and choose Create Data Source from the pop-up menu, and then work in the Create Data Source dialog box (see Figure 9-7). Add a field by typing its name in the "Field name" box and clicking the Add Field Name button; click the Remove Field Name button to remove the selected field name, and use the two Move buttons to rearrange the list into the right order. Click the OK button to close the Create Data Source dialog box, and then save your data source when Word prompts you to do so. Click the Edit Data Source button in the dialog box that Word displays to tell you that the data source contains no records. Word then displays the Data Form dialog box, in which you can add the details of each record in turn . Click the OK button when you've finished entering the records.

Creating the main document

The main document is the template for all the merge documents you're creating. The basic process is pretty simple: create the nonvariable parts of the document, and then insert a merge field wherever variable information is to be drawn in from the data source. Keep the following points in mind:

Figure 9-7. Word 2000 uses a different interface for creating a data source, but the process is similar.

You can base the main document on any template or any existing document. For example, you might use your company's letterhead for a mail-merge letter.
Include all the spaces and punctuation needed among your merge fields so that the resulting text will be correctly laid out. It's easy to miss the spaces and punctuation when looking at the field codes, but the results will look shoddy. If you normally work without formatting marks displayed, you may find it helpful to display them while laying out your merge. (Click the Show/Hide button on the Standard toolbar to toggle the display of formatting marks.)
Use If fields (as discussed in the next Annoyance "Create a Smart Mail Merge with Sorting, Filtering, and If Fields," next) to make decisions where necessary in a merge.
You can filter the records in your database so that you get only the ones that match your criteria, and then sort them so that they're in the right order (for example, state by state). Again, see "Create a Smart Mail Merge with Sorting, Filtering, and If Fields," next.

To insert a merge field:

In Word 2003 or Word XP, click one of the links ("Address block," "Greeting line," "Electronic postage ," "Postal bar code," or "More items") and use the resulting dialog box to enter the merge fields.
In Word 2000, click the Insert Merge Field button on the Mail Merge toolbar and choose from the pop-up menu.

Create a Smart Mail Merge With Sorting, Filtering, and if Fields

The Annoyance:

The way we've got mail merge set up at the moment, I have to go through the letters in the merge document to check them for blank lines in awkward places, fill in any missing information ("Dear [blank] Smith" doesn't cut it), and make sure we're not sending any letters to Delaware (don't ask). As you can imagine, this takes almost as much time as the merge itself saves.

The Fix:

It sounds as though you could smarten up the mail merge a lot by using sorting, filtering, and If fields.

Use filtering and sorting

Unless you create a data source specifically for your mail merge and it's manageably small, chances are that your data source for the mail merge will be imperfect, with some fields blank and others containing the wrong type of information. Word's filtering capabilities and its fields can help you avoid consequences from the imperfections in your data source. Sorting can help put the merged documents in the best order for whatever you're going to do with them.

To use filtering on your mail merge, in Word 2003 or Word XP, click one of the "Edit recipient list" links in the Mail Merge task pane or wizard, click the Edit button in the Mail Merge Recipients dialog box, and then click the "Filter and Sort" button to display the Filter and Sort dialog box (see Figure 9-8). In Word 2000, click the Query Options button in the Mail Merge Helper dialog box to display the Query Options dialog box, which is the Filter and Sort dialog box by its earlier name.

Figure 9-8. Filtering enables you to narrow down the records in your database to match only specific criteriain this case, customers who bought five or more hawsers.

The Sort Records tab lets you sort the records in your database by one, two, or three fields; for example, you might sort records by city, then by last name, and then by first name. For each sort field, you can choose between ascending order (A to Z, low numbers to high) and descending order (the reverse).

Use If fields

Word's If fields enable you to enter specific text if a condition is true, and other text if it is not. The If fields give you great flexibility in your merges.

To use an If field, click the Insert Word Field button on the Mail Merge toolbar and choose "If... Then... Else..." from the pop-up menu. Set up the condition in the Insert Word Field: IF dialog box (see Figure 9-9): select the field name, the comparison type ("Equal to," "Not equal to," "Less than," "Greater than," "Less than or equal," "Greater than or equal," "Is blank," or "Is not blank"), and the text for the comparison (for any comparison except "Is blank" or "Is not blank"). Then type the text strings in the "Insert this text" and "Otherwise insert this text" boxes. Word displays the "Insert this text" string in the document until you perform the merge.

Figure 9-9. If fields let you customize the text of your mail merge documents depending on a condition.

If fields tend to be the most widely useful, but Word offers eight other field types (on the Insert Word Field drop-down list) that you should investigate if you need to perform complex merges. The next most useful field is probably the "Skip Record If" field, which lets you skip the current record if the comparison is true. Ask fields and Fill-in fields, which let you prompt the user for information as the merge runs, are also sometimes useful.

Insert Data Source Fields into a Merge Document

The Annoyance:

When inserting data source fields into the main document in Word 2003 or Word XP, you must insert each field individually and then close the Insert Merge Field dialog box before inserting another. It's all a bit cumbersome.

The Fix:

This is pretty much as you say, unless you need to place several fields right next to each otheror unless you want to put all the fields in at once and then drag (or move) them into place.

If you've upgraded from Word 2000 to Word 2003 or Word XP, you're probably longing for the Insert Merge Field button on the Mail Merge toolbar (see Figure 9-10), which allowed you to choose the fields in the data source from a drop-down list. Good newsyou can add this button to the toolbar by clicking the Toolbar Options arrow and choosing Add or Remove Buttons Mail Merge Insert Merge Field.

Figure 9-10. Many people find the Insert Merge Field drop-down list a faster way to insert merge fields than the links in the Mail Merge task pane and the dialog boxes they display.

Edit a Data Source Document

The Annoyance:

In Word 2003 (and even Word XP), the data source documents are saved in Access format. If you want to do much editing, you need to know Access.

The Fix:

As you say, whereas Word 2000 and earlier versions used a Word table to store a custom data source that you created in Word, later versions use the Access database format. You can edit a Word table easily enough by dragging rows and columns to different positions (or simply deleting them), but to edit an Access database directly you need to open Accesswhich is a can of worms that we won't go into here.

Fortunately, you don't actually need to edit the database directly. Just click one of the "Edit recipient list" links in the Mail Merge task pane or wizard to display the Mail Merge Recipients dialog box, and then click the Edit button to display the data-source dialog box (which is named according to the data-source file). Click the Customize button, and use the Customize Address List dialog box (see Figure 9-11) to add new fields to the database or to delete, rename, or reorder the existing fields.

Figure 9-11. The Customize Address List dialog box puts a friendly (if clumsy) frontend on customizing a data source that's saved as an Access database.

Filter Using a Numerical Field

The Annoyance:

I'm not having much luck with filtering using a numerical field. If I filter for all records with amounts under 50, for example, Word reads 100 from the left and includes those records, too.

The Fix:

This problem most often occurs with street addresses (for example, 88 Acacia Blvd.), but it also occurs with other fields that mix numbers with text (e.g., 42 boxes). Word identifies such fields as alphanumerical rather than numerical, and the filtering is handled differently. In your case, it will include all records with amounts ranging from 049, 100499, 1,0004,999, and so on. To get around this, you'll need to split the non-numerical data from the numerical data in the field.

To apply filtering to a field, click the Edit button in the Mail Merge Recipients dialog box, click the "Filter and Sort" button to display the Filter and Sort dialog box, and then click the Filter Records tab. Choose the appropriate field, and enter the comparisons and values for it. The example in Figure 9-12 matches quantities from 1 up to and including 100, excluding records for which the quantity is blank (which typically implies a value of 0).

Figure 9-12. Use the Filter Records tab of the Filter and Sort dialog box to implement filtering on a numerical field.

Restore a Mail Merge Document to a Normal Word Document

The Annoyance:

I bailed out of the mail merge process somewhere before the endright at the point where I got really confused and decided I could paste the addresses into a dozen letters with less grief and gnashing of teeth. But Word still thinks that the letter is a mail-merge document, so now it displays the Mail Merge toolbar every time I open the letter, and it sometimes gives me messages about the data source being missing.

The Fix:

You need to tell Word that the document isn't part of a mail merge any more.

In Word 2003 or Word XP, click the Main Document Setup button (the leftmost button) on the Mail Merge toolbar to display the Main Document Type dialog box (see Figure 9-13). Select the "Normal Word document" option, click the OK button, and save the document. You may still need to close the Mail Merge toolbar manually, but it shouldn't return the next time you open the document.

Figure 9-13. If you terminate a merge, you may need to tell Word that the document isn't a merge document anymore.

In Word 2000, choose Tools Mail Merge Helper to display the Mail Merge Helper dialog box, click the Create button, and choose Restore to Normal Word Document from the pop-up menu. Click the Yes button in the confirmation message box, click the Close button in the Mail Merge Helper dialog box, and save the document.