Documentation/OOoAuthors User Manual/Writer Guide/Using Mail Merge

From Apache OpenOffice Wiki
Jump to: navigation, search

Template:NeedsWork This page was created by converting ODT to Mediawiki using Writer2MediaWiki.

This is Chapter 11 of the 2.x Writer Guide (Third edition), produced by the OOoAuthors group. A PDF of this chapter is available from the OOoAuthors Guides page at

<< User Manuals page
<< Writer Guide Table of Contents
<< Chapter 10 Working with Templates   |    Chapter 12 Creating Tables of Contents, Indexes, and Bibliographies >>

What is mail merge? (OOo) Writer provides a very useful feature to:

  • Create and print multiple copies of a document to send to a list of different recipients (form letters).
  • Create and print mailing labels.
  • Create and print envelopes.

This chapter describes the entire process. The steps include:

  1. How to create and register a data source.
  2. How to create and print form letters, mailing labels, and envelopes.
  3. Optionally, how to save the output in an editable file as an alternative to printing it directly.

Creating the data source

Although you can create and print mailing labels and envelopes without using a data source, in most cases using a data source is the most expedient approach. This chapter assumes that you are using a data source.

A data source is a file, spreadsheet or database, which contains the list of names and addresses the letter is to be sent to.

For this example, we will use the following column headers: First name, Last name, Address 1, Address 2, Town/City, State/County, Country, Postal Code, Gender, Points. A sample data source is shown below.

OOo can access a wide variety of data sources, including spreadsheets, text files and databases such as MySQL, Adabas, and ODBC. If the information to be used in the mail merge is currently in a format that OOo can not access directly, you need to convert it, for example by exporting it to a comma-separated file.

You can enter the data for the mail merge as part of the Mail Merge Wizard. However, if you have more than a few records, you probably should use an external data source.

Spreadsheet data source
Spreadsheet data source.

Registering a data source

Before any OOo documents can use a data source, it must be registered.

To register a data source:

  1. Click File > Wizards > Address Data Source.
  2. Starting the Address Data Source wizard
    Starting the Address Data Source wizard.

  3. Select the appropriate type of data book (in this case, Other external data source). Click Next.
  4. Select type of external address book
    Select type of external address book.

  5. On the next page of the Wizard (Figure 4), click Settings.
  6. Starting the Settings part of the Wizard
    Starting the Settings part of the Wizard.

  7. On the Data Source Properties dialog, select the Database type (in this case, Spreadsheet). Click Next.
  8. Selecting the database type
    Selecting the database type.

  9. On the next dialog, click Browse and navigate to the spreadsheet that contains the address information. Select the spreadsheet and click Open to return to this dialog.
  10. Selecting the spreadsheet document
    Selecting the spreadsheet document.

  11. Click Finish.
  12. On the following dialog, click Next. (Do not click Field Assignment.)
  13. Do not click Field Assignment
    Because this is a spreadsheet, do not click Field Assignment.

  14. Name the file in the location field. The default will be ...\Addresses.odb; you can replace Addresses with another name if you wish. You can also change the name in the Address book name field. In our example, the name "Points" was used for both.
  15. Name the file and the address book
    Name the .odb file and the address book.

  16. Click Finish. The data source is now registered.

Creating a form letter

Example: Sending a letter to your customer base
A mail order company organized a campaign to assign credit points to their customers according to the quantity of goods they buy during one year. At the end of the year, they want to send a letter to each customer to show the total of credit points collected.

You can use the Mail Merge wizard to create a form letter, or you can create the letter manually. See Using the Mail Merge Wizard to create a form letter for instructions on using the Wizard.

This section describes how to create a form letter manually.

  1. Create a new text document: File > New > Text Document.
  2. Display the registered data sources: View > Data sources (or press F4).
  3. Find the data source that you wish to use for the form letter, in this case Points. Expand the Points folder, expand the Tables folder, and select Sheet1. The data file is displayed.
  4. Selecting the data source
    Selecting the data source.

  5. Now create the form letter by typing in text, punctuation, line breaks, and so on that will be present in all of the letters, and adding fields where needed by clicking in the field heading and dragging to the appropriate point in the letter.
  6. Dragging fields
    Dragging fields to the body of the form letter.

  7. Continue until you have composed the entire document. (See below for an example.)
  8. Completed form letter
    The completed form letter.

  9. The document is now ready to be printed. Click File > Print.
  10. The following dialog appears. Answer Yes. Do not select the checkbox.
  11. WG11-msg.png

  12. In the Mail Merge dialog, you can choose to print all records or selected records. To select records to be printed, use Ctrl+click to select non-contiguous records. To select a contiguous block of records, select the first record in the block, scroll to the last record in the block, and Shift+click on the last record.
  13. Mail Merge dialog
    The Mail Merge dialog.

  14. Click OK to send the letters direct to the printer. If you wish to check the letters before printing, see Saving as a file rather than printing for instructions.

Printing mailing labels

Before beginning this process, check to see what brand and type of labels you have.

To print mailing labels:

  1. Click File > New > Labels.
  2. On the Labels dialog, go to the Options tab and uncheck Synchronize contents if it is checked.
  3. Uncheck Synchronize contents
    Uncheck Synchronize contents.

    Note: If you need to suppress blank lines in some of your labels, read Removing blank lines in labels.

  4. On the Labels tab, select the Database and Table. Select the Brand of labels to be used, and then select the Type of label.
  5. Select Database, Table, label Brand, and label Type
    Select Database, Table, label Brand, and label Type.

  6. Click the dropdown arrow under Database field. Select the first field to be used in the label (in this example, First name). Click the left arrow button to move this field to the Label text area.
  7. Move fields from Database field list to Label text area
    Move fields from Database field list to Label text area.

  8. Continue adding fields and inserting desired punctuation, spaces, and line breaks until the label is composed. The figure below shows the completed label.
  9. Completed label
    The completed label.

  10. Click New Document.
  11. Click File > Print. The following dialog appears. Click Yes. Do not select the checkbox.
  12. WG11-msg.png

  13. In the Mail Merge dialog, you can choose to print all records or selected records. To select records to be printed, use Ctrl+click to select non-contiguous records. To select a contiguous block of records, select the first record in the block, scroll to the last record in the block, and Shift+click on the last record.
  14. Mail Merge dialog
    Mail Merge dialog.

  15. Click OK to send the letters direct to the printer. If you wish to check the labels before printing, see Saving as a file rather than printing for instructions.

Printing envelopes

Instead of printing mailing labels, you may wish to printing directly onto envelopes. Printing envelopes involves two steps: setup and printing.

Setting up envelopes for printing

To set up envelopes to be printed with your letters:

  1. Click Insert > Envelope from the menu bar.
  2. In the Envelope dialog box, start with the Envelope tab (shown below). Verify, add, or edit the information in the Addressee and Sender boxes (Sender is the “from” on the envelope). You can use the right-hand dropdown lists to select the database or table from which you can draw the envelope information. Use the same method as described in Creating a form letter.
  3. Choosing addressee and senderinformation
    Choosing addressee and sender information for envelopes.

  4. On the Format tab, verify or edit the positioning of the addressee and the sender information. The preview area on the lower right shows the effect of your positioning choices. To format the text of these blocks, click the Edit buttons to the right.
    • In Character, you can choose fonts, sizes, underlines or color, rotating/scaling, backgrounds and more.
    • In Paragraph, you can choose indenting, alignment, flow, tabs and more.

    Choosing positioning and size of elements
    Choosing positioning and size of elements for an envelope.

  5. In the lower left of this tab, choose the envelope format from the dropdown list. The width and height of the selected envelope then show in the boxes below the selected format. If you chose a pre-existing format, just verify these sizes. If you chose User defined in the Format list, edit the sizes.
  6. After formatting, select the Printer tab to choose printer options such as envelope orientation and shifting. You may need to experiment a bit to see what works best for your printer. You can also choose a different printer or alter the printer setup (for example, specify the tray that holds envelopes) for this print job.
  7. Choosing printer options
    Choosing printer options for an envelope.

  8. When you have finished formatting, click either the New Doc or Insert button to finish. New Doc makes only the envelopes or starts a new document with the envelopes. Insert inserts the envelopes into your existing document as page 1.
    To not proceed with this envelope, click Cancel or press the Esc key. You can also click Reset to remove your changes and return to the original settings when the dialog box opened.
  9. To remove blank lines from the addresses, use the technique described in steps 1 through 5 in Removing blank lines in addresses.

Merging and printing the envelopes

To merge addresses and print the envelopes:

  1. Choose File > Print. The following dialog appears. Click Yes. Do not select the checkbox.
  2. Confirmation dialog
    Confirmation dialog for merging database fields.

  3. The Mail Merge dialog appears. As with form letters and mailing labels, you can choose to print envelopes for one, several or all address records in the database.
  4. Choosing records
    Choosing records to use when printing envelopes.

  5. Make your selections and then click OK to print direct to the printer. If you wish to check the envelopes before printing them, see Saving as a file rather than printing for instructions.

Using the Mail Merge Wizard to create a form letter

To start the Mail Merge wizard, select Tools > Mail Merge Wizard. The wizard opens, as shown below.

Step 1: Select starting document

The wizard gives various options to select your starting document:

  • Use the current document.
  • Create a new document.
  • Use a template.
  • Use an existing document.

You can write the letter later on in the wizard, but it is easier to use a draft you prepared earlier. For this example, select Use the current document and click Next.

Select starting document
Select starting document.

Step 2: Select document type

The wizard can produce letters or, if a Java Mail connection exists, email messages. You can see these options in the figure below. In this example, we are producing a letter. Select Letter and click Next.

Choose document type
Choose document type.

Step 3: Insert address block

This is the most complex step in the wizard. In this step you will do three things:

  1. Tell the wizard which data source to use. The data source must be a file; in this example it is the spreadsheet created earlier.
  2. Select the address block to use in the document. This means choosing which fields appear (for example, whether the country is included) and how they look.
  3. Make sure that the fields all match correctly. This is very important. For example, the wizard has a field called <Last Name>. If your spreadsheet has a column called "Surname", you need to tell the wizard that <Last Name> and “Surname” are the same thing. This is described in Matching the fields.
Insert address block
Insert address block.

Selecting the data source (address list)

  1. Click Select Address List in section 1. This opens the Select Address List dialog for choosing a data source.
  2. If you have not already created the address list, click Create to do so now. If you have an address list, as we have in this example, click Add and select the file.
  3. Select the spreadsheet file and click OK to return to step 3 of the wizard. For this example, the preceding steps are all you need to do. The wizard can also exclude certain records; click Filter to choose them.
Select address list dialog
Select address list dialog.

Selecting the address block

  1. In step 3 of the wizard (shown in Step 3: Insert address block), look at section 2. This is where you select the address block to appear on the letter, and define its appearance and the fields it contains. The main page gives two examples. If neither of those is exactly what you want, click More to see more choices, in the Select Address Block dialog box (shown below).
  2. Select address block
    Select address block.

  3. The Select Address Block dialog box offers six choices for the format of the addresss block (scroll down to see the last two choices). You can also optionally include or exclude the country (for example, only include the country if it is not USA). The six formats provided are relatively common, but they might not exactly match your preference. If this is the case, select the address block that is closest to what you want and click Edit, which opens the New Address Block dialog box.
  4. New address block
    New address block.

  5. In the New Address Block dialog, you can add or delete address elements using the arrow buttons on the left. To move elements around, use the arrow buttons on the right. For example, to add an extra space between first and last names, click <Last Name> and then click the right arrow button.

Matching the fields

Finally, it is time to match the wizard’s fields with the spreadsheet fields, so that items like <Last Name> and “Surname” match correctly.

  1. Look at section 3 of step 3 of the wizard. The box at the bottom displays one record at a time, using the address block format you selected. Use the right and left arrow buttons below that address box to step through the addresses, checking that they display correctly. Do not assume that all the records display correctly, just because one or two do. Check them all if you can, or at least a good proportion.
  2. If the addresses do not display correctly (and they probably will not right away), click Match Fields. The Match Fields dialog box opens.
  3. Match fields dialog box
    Match fields dialog box.

    The Match Fields dialog box has three columns.

    • Address Elements are the terms the wizard uses for each field, such as <First Name> and <Last Name>.
    • The Matches to Field column allows you to select, for each address element, the field from your data source that matches it.
    • The Preview column shows what will be shown for this field from the selected address block, so you can double-check that the match is correct.
  4. When you have matched all the fields, click OK to return to step 3 of the wizard. Now, when you use the arrow buttons to look at all the addresses, they should all look correct. If not, go back and change anything you’re not happy with, before clicking Next to move to step 4.

Step 4: Create salutation

The salutation is the initial greeting, for example, Dear Mr Jones. In step 4, select the salutation that will appear in the letter.

Create a salutation
Create a salutation.

You can use a different greeting for men and women. To do this, Writer must have some way of knowing whether a person is male or female. In our spreadsheet we had a column called Gender. In the section Address list field indicating a female recipient, set the field name to Gender and the field value to F. The male salutation is then printed for all men and the female salutation for all women.

Note: You do not need to tell OOo who is a male, because it assumes that all non-female records are males.

If you do not select Insert Personalized Salutation, you can use a more general salutation without referring to the recipient directly, such as “To whom it may concern”.

As in step 3, step 4 of the wizard has a preview pane at the bottom. You should check more than one record to ensure that other records look the way you expect.

Step 5: Adjust layout

In step 5, you can adjust the position of the address block and salutation on the page. You can place the address block anywhere on the page. The salutation is always on the left, but you can move it up and down the page. Use the buttons shown in the figure below to move the elements.

Adjust layout
Adjust layout.

Step 6: Edit document and insert extra fields

In step 6 you have another opportunity to exclude particular recipients from the mail merge, as shown below.

Edit document
Edit document.

You can also edit the body of the document. If you started with a blank document, you can write the whole letter in this step. Click Edit Document to shrink the wizard to a small window so you can easily edit the letter.

Minimized mail merge wizard
Minimized mail merge wizard.

You need to perform another important task in this step. The wizard only inserts information from the name and address fields, but you may wish to add additional data. In our example, we want to tell each person how many points they had accumulated during the year; that information is in the spreadsheet.

To do this:

  1. Click Edit Document in step 6 of the wizard.
  2. Select Insert > Fields > Other. The Fields dialog box opens, as shown below.
  3. Insert mail merge fields dialog box
    Insert mail merge fields dialog box.

  4. Click the Database tab.
  5. On the left hand side, select Mail merge fields.
  6. Under Database selection find your data source (in this example, it is a spreadsheet). Expand it to see the fields.
  7. Click the field you want to insert, then click Insert to insert the field. You can insert any number of fields any number of times into your mail merge document.
  8. Click Close when you are done.

Note: The Database selection lists the data source you selected in step 3. All the information you need for the letter must be contained in that data source.

Step 7: Personalize documents

In step 7, OOo creates all your letters, one per recipient.

Clicking the Edit Document button here is similar to step 6. The difference is that you now edit a long file containing all of the letters, so you can make changes to a particular letter to one person. In step 7 of the Mail Merge wizard (Figure 35), click Find to open a dialog box that allows searches within the document.

[[Image:|thumb|<center>Figure 35: Personalize document

As with step 6, when editing the document, the wizard shrinks to small window. Click on this window (see Figure 33 on page 23) to expand the wizard to its full size.

Step 8: Save, print or send

You have now completed the mail merge process. The last step is to do something with it. In step 8, you can save the original sample letter, save the merged document, print the letters right away or, if you created email messages, send them.

[[Image:|thumb|<center>Figure 36: Save,print or send

You probably want to save the starting document and the merged document. The merged document can be saved as one big file or one file for each letter.

When you have saved the merged document, you can print the final letters now or later; and you can still manually check and edit the letters if necessary.

Saving as a file rather than printing

You can save the output of a form letter, mailing labels, or envelopes as a file, rather than printing it directly. Saving as a file is useful for several reasons. For example, you may want to view the file to make sure it is correct before printing a large number of form letters or mailing labels, or you may want to edit the file (change the typeface, the type size, indentation, and so on).

To save as a file rather the printing directly, select the Mail Merge Wizard at the point where File > Print would normally be selected.

Thus, after selecting New Document, instead of selecting File > Print, do this:

  1. Select Tools > Mail Merge Wizard.
  2. In Step 1 of the wizard (Figure 23), select Use the current document.
  3. Then select step 8 (Save, print, or send). In step 8, you can save the original sample letter, save the merged document, or print the letters right away. In the example below, Save merged document and Save as single document are selected. You can also save as individual documents, which means that there will be a separate document for each record. In most instances, saving as a single document is more desirable.
[[Image:|thumb|<center>Figure 37: Step 8: Save, print or send
  1. Click Save Documents. You will be prompted to give the document a name. You then return to the above dialog, where you can choose another option (for example, save the starting document), or click Finish.

Editing a saved file of mailing labels

You can edit individual records in the normal fashion, by highlighting and changing the font name, for example. However, you cannot edit globally (changing the font name for all records, for example) by selecting the entire document.

[[Image:]]To make global changes, you must edit the style:

  1. Right-click any correctly spelled word in a label record. Select Edit Paragraph Style from the popu-up menu. (Note: If you click on a misspelled word, a different context menu will appear.)
  2. Then from the paragraph style dialog, you can make change to the font name, the font size, the indents, etc.

Removing blank lines in addresses

A common situation is that some addresses may have no information in some fields. For example, you may have an “address1” field and an “address2” field to deal with multi-line addresses, or a “company name” field that may be blank in some cases. When you print a form letter, address label, or envelope, you will not want a blank line to appear when the field is empty. This section describes how to suppress blank lines.

Note – Very Important!
Be sure your field names (or column headings in a spreadsheet) do not contain spaces or the technique described here does not work.
Normally you would enclose a field name in quotation marks if the name contains a space. However, a quoted string is not acceptable in the Condition box for a hidden section (see Figure 40 and Figure 42) and an un-quoted string stops on the first space in the field name, effectively making the condition always True and causing the section to be hidden even if the field has data in it..

Removing blank lines in addresses on form letters

  1. Figure 38 shows a typical address block with field highlighting on. The field <ad2> is the second address line, which may be blank for some addresses, so you want to have that whole line hidden if the field is blank.
[[Image:|thumb|<center>Figure 38: Typical address block
  1. Select the <ad2> field, and then select Insert > Section from the menu bar. In the Insert Section dialog (Figure 40), select the Hide checkbox. In the With Condition box, type the field name (ad2 in this case) and EQ "" (which means “equals a blank record”). Click Insert.

Back in the document, if you have section markers showing, you will see something similar to Figure 39.

[[Image:|thumb|<center>Figure 39: Section containing second address line

Now, when you do a mail merge using this document, the line inside the section (including its paragraph marker) will be hidden if the field <ad2> is blank.

[[Image:|thumb|Figure 40: Setting a condition for a hidden section]]

Removing blank lines in labels

The process for removing blank lines in labels is a bit different.

  1. When setting up your labels (as described in “Printing mailing labels” starting on page 8), select the Synchonise contents checkbox on the Options tab of the Labels dialog (Figure 13). Continue as described in that section.
  2. When you click New Document, a page of labels is displayed, showing the field names. If you do not already have paragraph markers showing, display them now. (Click the Nonprinting Characters icon, which looks like a backwards P.) Notice there is only one paragraph mark in each label, at the end of the last line (Figure 41).
[[Image:|thumb|<center>Figure 41: Labels showing only one paragraph mark each
  1. To make this technique work, you need to have a paragraph mark at the end of each line. To do this, go to the first label (upper left corner), place the cursor at the end of the first line, press Enter, and then press Delete. This inserts a paragraph mark at the end of the first line. Repeat for each of the other lines except the last. Do this only in the first label.
  2. Click the Synchronize Labels button in the upper left. Now all the labels have paragraph marks at the end of each line.
  3. In the first label on the page, select the field you wish to hide if it is blank. In our example, that is <ad2>. Click Insert > Section on the menu bar. In the Insert Section dialog (see Figure 42), select the Hide checkbox. In the With Condition box, type the field name (ad2 in this case) and EQ "". Click Insert.
    The <ad2> line in the first label may disappear. Do not be concerned if this happens.

[[Image:|thumb|Figure 42: Setting the condition for a hidden section]]

  1. Click Synchronize Labels. Now all the labels look the same.
  2. Select Tools > Mail Merge Wizard. In Step 1 of the wizard (Figure 23), select Use the current document. Then select step 8 (Save, print, or send). A labels document is created, and the last page of the Mail Merge Wizard remains open (Figure 43).
[[Image:|thumb|<center>Figure 43: Saving the document you just created
  1. Select Save starting document and click the Save starting document button. This saves all your work setting up the labels.
  2. The dialog remains open. Select Save merged document and Save as single document, or select Print merged document. All the pages of labels are printed or saved.
[[Image:|thumb|<center>Figure 44: Saving or printing the merged labels

Removing blank lines from envelope addresses

After you set up the envelope (see Printing envelopes), use the technique described in steps 1 through 5 in Removing blank lines in addresses to remove the blank lines. Then continue with printing the envelopes as described in Merging and printing the envelopes.

Content on this page is licensed under the Creative Common Attribution 3.0 license (CC-BY).
Personal tools