Blog Archive

How to create a Mail Merge



Article contributed by Beth Melton and Dave Rado

.

Mail merge basics

This article will step you through the basics of creating a mail merge and contains links to some of the more advanced features.
Sometimes the term "mail merge" can be a little misleading. We assume from the title that the intent of mail merge is to produce letters for mass mailing purposes. That's not necessarily the case.
Mail merge is for simplifying repetitive documents and tasks. Mail merge can be used for creating many documents at once that contain identical formatting, layout, text, graphics, etc., and where only certain portions of each document varies. Mail merge is also used for generating mailing labels, envelopes, address lists, personalised training handouts, etc. As well as hard copy mailshots, it can be used to generate multiple emails and electronic faxes. And it can even be used to create a "friendly" front-end to spreadsheet or database information.
Whenever you need to assemble similar data, mail merge is the answer!
Mail merge primarily consists of two files, the Main Document and the Data Source. The Main Document contains the information that will remain the same in each record, and the Data Source contains all the variable information, in the form of fields. This is the information that will change in the Main Document when the merge is completed. Along with the information that remains the same, the Main Document also contains merge fields, which are references to the fields in the Data Source.
When the Main Document and Data Source are merged, Microsoft Word replaces each merge field in the Main Document with the data from the respective field contained in the Data Source. The end result is a third document, a combination of the Main Document and Data Source – although you can also mail merge directly to the printer; (or fax or email) – you don't need to create a merged document on screen; and you can also "preview" the mail merge without actually merging (using the ViewMergedData button).
Step
Comments
1. If you have an existing file you want to use as the merge document open it now.
For WordPerfect users this would be the primary file
2. Start the Mail Merge Helper by going to Tools/Mail Merge.
  
3. Select the type of Main Document you wish to create.
You will be asked whether you want to use the Active Window or a New Document. To use the document you opened in Step 1, select Active Window. 
If you select New Document, Word will create a blank document based on the Normal template. For most purposes it is much better to use a template specific to the task in hand rather than the Normal template; so Active Window is usually the better choice.
Form Letters – A Section Break is placed between records in the completed merge.
Mailing Labels – Records are merged to a table representing the label layout. If you use the wizard to create your labels, Word will insert a «Next Record» field at the start of every table cell except the first one. If creating your own customised label template, you will need to insert the «Next Record» fields yourself (you can use the "Insert Word Field" button to do this).
Catalog – Records are merged continuously; use for mailing lists, telephone directories, etc.
Envelopes – Similar to form letters except that an envelope paper definition is used.
4. Click Get Data to select your Data Source. There are four methods you can use for your Data Source:
  
   a) Create Data Source
This uses Word as the Data Source; setup your fields and type the data for the first time. The data is then stored in a Word table, which can easily be transferred to an Excel Data Source later if required.
Note that you may find even a Word Data Source easier to set up without the wizard once you know what you're doing, as it's nothing more than a Word table with the items in the Heading Row of the table representing the mail merge fields – although you may well need to use Normal View to view all the data in the table if you don't want the columns to be incredibly narrow. Some experienced users prefer to use the wizard for this reason.
   b) Open Data Source
Lets you browse to and open a Word, Excel, Access (or any supported database) or text file Data Source.
   c) Use Address Book
Use an Outlook or Outlook Express address book as the Data Source. (It can also link up to an Exchange *.pab and a Schedule+ Contacts list).
   d) Header Options
If a Data Source from another program does not contain a Heading Row, or if field names in the Heading Row of your Data Source do not match the merge fields in your Main Document, you can use a separate Header Source and specify the field names.
  Field names must be listed in the same order as the corresponding information in the Data Source.
  Field names in the Header Source must match any merge fields you've inserted in the Main Document.
However, where possible, build the Heading Row into your Mail Merge Data Source rather than having a separate Header Source – it simply makes life easier.
Note that if your Data Source does contain a Header row, and if you nevertheless use a separate Header Source, the Header Row within the Data Source will be treated as a mail merge record – so be careful.
5. Select Edit Main Document.
Add all text that you want to appear with every record; and insert the appropriate merge fields using the Insert Merge Field button on the Mail Merge Toolbar.
You can use as few or as many of the data fields as you need to, and you can use them in any order. Also, any field can be used more than once.
In a catalog merge, this text will appear with each record. If you want text to appear either before or after all records, it's best to add this to the completed merged document.
6. You will want to add appropriate punctuation and spaces between the merge fields.
Although blank paragraphs are suppressed automatically when you merge, spaces within a paragraph are not.
Frequently (for instance, in an address) you will want to suppress spaces and punctuation following a field if the field is blank – for example, if you have "«Street, «City»", then if there is no street in a given address, you won't want the comma and space to display.
You can achieve this flexibility by using IF fields. See: Making your mail merge "intelligent" by using IF fields.
7. As well as the obvious places, such as the address, you may also want to add mail merge fields at strategic points in the main body of the document – "«so-and-so» will be contacting you", etc..
Frequently, you may want to make the results that are displayed dependant on a condition. For example, you may want to display "he" or "she", depending on gender; or you might want to display either: "please let us know your start date", or "you will commence employment on 01 January 2001", depending on whether the start date is known.
Again, see: Making your mail merge "intelligent" by using IF fields for details of how to do this.
8. You can preview your data prior to performing the merge by clicking the ViewMergedData button on the mail merge toolbar. Doing this is far quicker than actually merging, if the Data Source is reasonably large.
You can skip though the records by pressing the Next and Previous buttons.
[You can also go to a specific record using the Find button (binoculars). This works fine under Word 2000 but can cause problems if you are using Word 97 – see: Why is mail merge unavailable after using the Find Record feature in Word 97?].
Having proof-read it in this way, you can merge straight to the printer if you want to, without having to create a merged document first.

  

  
A couple of minor "gotchas" with using ViewMergedData:
 
Blank paragraphs are not suppressed until you do the merge. However, if you have the ShowAll button on, you will see a " symbol to indicate a paragraph that is going to be suppressed, instead of the usual ¶ symbol for a normal paragraph.
 
Fields based on the total number of pages tend not to work as expected following a merge (e.g. Page X of Y), but this usually won't be apparent until you do a full merge.
9. You may well need to filter the records, in order to merge only to a subset of your Data Source.
You may also want to change the sort order for the merge without affecting the sort order of the Data Source.
To do this, you can use Mail Merge Query Options
For instance, you may be doing a follow-up mailshot to those people who didn't reply to the original mailshot. Or you may want to send a letter only to people whose birthday is this week.
For details of how to do this, see: Turning Word into a pseudo-database by using Mail Merge Query Options.
10. Prior to performing the merge, hold <Shift> and then select File/Save All. This will save the Mail Merge Main Document; and if the Data Source is a Word document, it will save that too.
For additional formatting techniques, tips and tricks, and special merges such as setting up an Address Book, phone lists, merging graphics, suppressing duplicates, One-to-Many Relationships, see the Mail Merge home page on this site.
11. Perform the merge. Merge choices are:
  Merge to New Document
  Merge to Printer
  Merge to Email
  Merge to Electronic Fax
For large mail merges, merging straight to the printer generally works better and is much faster than merging to a new document and printing that.
If merging to Email or Electronic Fax, click the Setup button on the Merge dialog when you're ready to merge. Then select the Data field which contains the email address or fax number, type the subject line; and in the case of Email, choose whether or not you want the document to be sent as an attachment. Finally, you may also want to use Query Options to filter out any records that don't contain an email address/fax number.

2.

General mail merge FAQ

Question
Solution
Page numbering doesn't restart with each record in completed merge.
1. Go into the Header or Footer.
2. On the Header/Footer toolbar, click the Format Page Number button:
3. Click Start At and enter the desired value.
4. Click OK to close the dialog.
How do I amend, add or remove mail merge fields?
1. Select Edit Data Source on Mail Merge Toolbar
2. If the Data Source is an Excel spreadsheet or Access database, this will open it (or switch to it, if it's already open).
If the Data Source is a Word document, the Data Form dialog will now appear. Click the View Source button to open the Data Source.
3. To amend a field, edit it in the Heading Row; to add a field, add a column to the table; to remove a field, delete the relevant column.
Alternatively, if the Data Source is a Word Document, you can select Manage Fields on the Database Toolbar.
This makes life a bit simpler, especially if it's your first time. Then save and Close the Data Source.
My email addresses aren't inserted as hyperlinks in the completed merge.
1. Select Format/AutoFormat and then Options
2. Turn off all formatting options that you do not want, but ensure that Internet and network paths as hyperlinks is turned on.
Merge to electronic fax option missing  Follow this link
How do I get an image such as our company logo into my mail merge labels? Assuming you want the same picture in every label, just insert it inline into every table cell in the mail merge Main Document.
If you don't want the logo to print for cells where there is no data (so you can avoid wasting labels), you can use a combination of an IF field and an INCLUDEPICTURE field:
{ IF { MERGEFIELD FirstName } <> "" { INCLUDEPICTURE "C:\\Temp\\Logo.tif" \d \* MERGEFORMAT } "" }
You could also create an AutoText entry for the image or for the IncludePicture field. To use it, in the Label Setup dialog box, sample label text area, type the name for the AutoText entry and press <F3> to insert it.
Or you could create a label template with the image or field already included.




Excel Tip: Using REPT Function to covert the data in graphical mode


 

 

 

 

Font type in this column is wingding

 

50

nnnnnnnnnnnnnnnnnnnnnnnnn

=REPT("n", C4/2)

30

nnnnnnnnnnnnnnn

=REPT("n", C4/2)

10

nnnnn

=REPT("n", C4/2)

40

nnnnnnnnnnnnnnnnnnnn

=REPT("n", C4/2)

18

nnnnnnnnn

=REPT("n", C4/2)

28

nnnnnnnnnnnnnn

=REPT("n", C4/2)

8

nnnn

=REPT("n", C4/2)

48

nnnnnnnnnnnnnnnnnnnnnnnn

=REPT("n", C4/2)

 

 

 

 

 

Excel Functiona: IF

Name Sales Target Result
Alan 1000 5000 Not Achieved  =IF(C4>=D4,"Achieved","Not Achieved")
Bob 6000 5000 Achieved  =IF(C5>=D5,"Achieved","Not Achieved")
Carol 2000 4000 Not Achieved  =IF(C6>=D6,"Achieved","Not Achieved")
What Does It Do?
This function tests a condition.
If the condition is met it is considered to be TRUE.
If the condition is not met it is considered as FALSE.
Depending upon the result, one of two actions will be carried out.
Syntax
 =IF(Condition,ActionIfTrue,ActionIfFalse)
The Condition is usually a test of two cells, such as A1=A2.
The ActionIfTrue and ActionIfFalse can be numbers, text or calculations.
Formatting
No special formatting is required.
Example 1
The following table shows the Sales figures and Targets for sales reps.
Each has their own target which they must reach.
The =IF() function is used to compare the Sales with the Target.
If the Sales are greater than or equal to the Target the result of Achieved is shown.
If the Sales do not reach the target the result of Not Achieved is shown.
Note that the text used in the =IF() function needs to be placed in double quotes "Achieved".
Name Sales Target Result
Alan 1000 5000 Not Achieved  =IF(C31>=D31,"Achieved","Not Achieved")
Bob 6000 5000 Achieved  =IF(C32>=D32,"Achieved","Not Achieved")
Carol 2000 4000 Not Achieved  =IF(C33>=D33,"Achieved","Not Achieved")
Example 2
The following table is similar to that in Example 1.
This time the Commission to be paid to the sales rep is calculated.
If the Sales are greater than or equal to the Target, the Commission is 10% of Sales.
If the Sales do not reach Target, the Commission is only 5% of Sales.
Name Sales Target Commission
Alan 1000 5000 50  =IF(C43>=D43,C43*10%,C43*5%)
Bob 6000 5000 600  =IF(C44>=D44,C44*10%,C44*5%)
Carol 2000 4000 100  =IF(C45>=D45,C45*10%,C45*5%)
Example 3
This example uses the =AND() within the =IF() function.
A builders merchant gives 10% discount on certain product lines.
The discount is only given on products which are on Special Offer, when the Order Value
is £1000 or above.
The =AND() function is used with the =IF() to check that the product is on offer and that
the value of the order is above £1000.
Special Order
Product Offer Value Discount Total
Wood Yes  £   2,000  £          200  £         1,800
Glass No  £   2,000  £            -    £         2,000
Cement Yes  £     500  £            -    £            500
Turf Yes  £   3,000  £          300  £         2,700
 =IF(AND(C61="Yes",D61>=1000),D61*10%,0)