Writer Guide 7.2

Chapter 14
Mail Merge
Form Letters, Mailing Labels, and EnvelopesCopyrightThis document is Copyright © 2021 by the LibreOffice Documentation Team. Contributors arelisted below. You may distribute it and/or modify it under the terms of either the GNU General
Public License (https://www.gnu.org/licenses/gpl.html), version 3 or later, or the Creative
Commons Attribution License (https://creativecommons.org/licenses/by/4.0/), version 4.0 or later.
All trademarks within this guide belong to their legitimate owners.

Contributors
To this edition
Jean Hollis Weber

Kees Kriek

To previous editions
Jean Hollis Weber
Bruce Byfield
John A Smith

Kees Kriek
Gillian Polack

Felipe Viggiano
Ron Faile Jr.

Feedback
Please direct any comments or suggestions about this document to the Documentation Team’s
mailing list: documentation@global.libreoffice.org

Note
Everything you send to a mailing list, including your email address and any otherpersonal information that is written in the message, is publicly archived and cannot
be deleted.

Publication date and software version
Published August 2021. Based on LibreOffice 7.2 Community.
Other versions of LibreOffice may differ in appearance and functionality.

Using LibreOffice on macOS
Some keystrokes and menu items are different on macOS from those used in Windows andLinux. The table below gives some common substitutions for the instructions in this document.
For a detailed list, see the application Help.
Windows or LinuxmacOS equivalent

EffectTools > Options
menu selection

LibreOffice > Preferences

Access setup options

Right-click

Control+click or right-click
depending on computer setup

Open a context menu

Ctrl (Control)⌘ (Command)

Used with other keys

F11

⌘+T

Open the Styles deck in the
SidebarDocumentation for LibreOffice is available at https://documentation.libreoffice.org/en/Contents
Copyright............................................................................................................................2
Contributors.............................................................................................................................. 2
Feedback.................................................................................................................................. 2
Publication date and software version.......................................................................................2
Using LibreOffice on macOS.....................................................................................................2

What is mail merge?..........................................................................................................4
Creating and registering an address data source.........................................................4
Creating a form letter........................................................................................................6
Suppressing blank lines (optional)............................................................................................8
Saving merged documents.......................................................................................................9

Printing mailing labels....................................................................................................10
Preparing for printing............................................................................................................... 10
Printing.................................................................................................................................... 12
Editing a saved file of mailing labels........................................................................................12

Printing envelopes..........................................................................................................13
Setting up envelopes for printing.............................................................................................13
Merging and printing the envelopes........................................................................................14

Using the Mail Merge Wizard to create a form letter...................................................14
Step 1: Select starting document............................................................................................14
Step 2: Select document type.................................................................................................15
Step 3: Insert address block....................................................................................................15
Selecting the data source (address list)..............................................................................16
Selecting and optionally editing the address block.............................................................17
Matching the fields............................................................................................................. 18
Step 4: Create salutation.........................................................................................................19
Step 5: Adjust layout............................................................................................................... 21

Chapter 14 Mail Merge | 3

What is mail merge?
Writer’s mail merge provides features to create and print multiple copies of one item (such as
return address labels, promotional stickers, or t-shirt transfers) or to produce copies that include
variable information (for example names, addresses, and amounts due):•Form letters (a document to send to a list of recipients)•

Mailing labels, labels for physical file folders, and similar purposes•

Envelopes

Variable data is typically derived from a data source, as discussed below. If all output is to be thesame (for example, return address labels), the merge can use manually entered text or images.
This chapter describes the mail merge process. The steps include:
1) Create and register a data source.
2) Create and print form letters, mailing labels, and envelopes.
3) Optionally, save the output in an editable file instead of printing it directly.

Creating and registering an address data source
An address data source is a database containing the name and address records from which
mailing labels and envelopes can be derived. LibreOffice can create the database fromspreadsheets, text files including Writer documents, and databases such as MySQL. If theinformation to be used in the mail merge is currently in a format that LibreOffice cannot accessdirectly, you need to convert it, for example by exporting to a comma-separated values (CSV) file.
For a data source to be directly accessible from within a Writer document, it must be registered.
You need to do this only once.
1) From within a Writer document, or from the LibreOffice Start Center, choose File >Wizards > Address Data Source.
2) The choices on the first page of the wizard (Figure 1) vary with your operating system.
Select the appropriate type of external address book. For a spreadsheet, it is Other
external data source. Depending on your choice, the list of steps on the left may
change. Click Next.

Figure 1: Select type of external address book

4 | Chapter 14 Mail Merge

3) On the next page of the Wizard (Figure 2), click on the Settings button.

Figure 2: Starting the Settings part of the Wizard
4) On the Create Address Data Source page (Figure 3), select the Database type. In our
example, it is Spreadsheet. Click Next.

Figure 3: Selecting the database type5) On the next page (Figure 4), click Browse and navigate to the spreadsheet that contains
the address information. Select the spreadsheet and click Open to return to this dialog.

Figure 4: Selecting the spreadsheet document
6) To test that the connection has been correctly established, click the Test Connection
button in the lower right of the dialog (not shown in Figure 4). You should get themessage The connection was established successfully; otherwise, you will get an error
message.
7) Click Finish.
8) On the next page (Figure 5), click Next. You do not need to click the Field Assignment
button. (When you are using the Mail Merge Wizard, you may need to do the Field
Assignment step; see page 18.)

Creating and registering an address data source | 5

Figure 5: For a spreadsheet, do not click the Field Assignment button
9) On the Data Source Title page (Figure 6), the name and location of the new database(.odb) is shown in the Location field. You can change this name or store the database filein a different location; deselect Embed this address book definition into the current
document, then click Browse to choose the location and name the file.
You can also change the name in the Address book name field. This is the registeredname, which LibreOffice displays in data source listings. In our example, the name
Addresses was used for both.

Figure 6: Name the file and the address book10) Click Finish. The data source is now registered.

Creating a form letter
You can create a form letter manually—the simplest and most comprehensive method, which is
described here—or you can use the Mail Merge wizard starting on page 14.
1) Create a new Writer document (File > New > Text Document) or open an existing form
letter with File > Open.
2) Display the registered data sources: View > Data Sources (or press Shift+Ctrl+F4).
6 | Chapter 14 Mail Merge

3) Find the data source that you wish to use for the form letter, in this case Addresses.
Expand this folder and the Tables folder, and select Sheet1. The address data file isdisplayed.

Figure 7: Selecting the data source4) Now create or modify the form letter by typing in the text, punctuation, line breaks, and soon that will be present in all of the letters.
To add the mail-merge fields where needed (such as names and addresses), click in thefield heading and drag it to the appropriate point in the letter. Be sure to add spaces orother punctuation as needed. Press Enter at the end of each line in the address block.

Figure 8: Dragging fields to the body of the form letter5) Our example database (Figure 7) contains a field for a second address line (AD2) that isblank for some addresses. If you wish to remove any blank lines that may appear in theaddress block when the letters are printed, you can use the procedure described in“Suppressing blank lines (optional)” on page 8. Otherwise, continue to the next step.
6) The document is now ready to be printed.
a) Choose File > Print and click Yes in the message box.
Creating a form letter | 7

Figure 9: Mail merge confirmation messageb) In the Mail Merge dialog (Figure 10), in the Records section, you can choose to print
all records or selected records. To select individual records to be printed, Ctrl+click oneach one. To select a block of records, select the first record in the block, scroll to thelast record in the block, and Shift+click on the last record. You can also specify arange of record numbers to print.
c) To send the letters directly to the printer, select Printer in the Output section and click
OK. Or, you can save the letters to a file for further editing or formatting; see “Saving
merged documents” on page 9.
d) If you have not saved the original, prototype form letter document (template)previously, then you should do so now. Having a form letter template could greatly
simplify the creation of other form letters and is highly recommended.

Figure 10: The Mail Merge dialog

Suppressing blank lines (optional)
If you wish to suppress blank lines, follow this procedure:
1) Click at the end of a paragraph to be suppressed if empty, and then choose Insert >Field > More Fields to display the Fields dialog.
2) Select the Functions tab and then choose Hidden Paragraph in the Type column.

Note
When using the Mail Merge Wizard (page 14), you can select automatic suppressionof blank lines and avoid the work described in this step.

8 | Chapter 14 Mail Merge

Figure 11: Defining a conditional hidden paragraph3) Now click in the Condition box and enter the details of the condition that defines a blankaddress field. It has the general form of:![Database.Table.Database field]
For example, in our database the condition to test if the AD2 field is empty would be:![Addresses.Sheet1.AD2] as illustrated in Figure 11.
Alternative ways to express this condition include NOT Addresses.Sheet1.AD2 andAddresses.Sheet1.AD2 EQ “”.
4) Click Insert.

Saving merged documents
You may prefer to save the letters in a file, to allow for proofreading or formatting. To do this:1) In the Mail Merge dialog (Figure 10), select the records to be included, then select File in
the Output section to activate some other choices on the page (Figure 12).
2) You can choose whether to save the output as a single document (containing all of theletters) or save the letters as individual documents; and you can specify the name,location, and format of the files.

Figure 12: Saving output to a file3) Click OK. In the Save as dialog, enter a file name for the saved letters and choose afolder in which to save them. The letters will be saved consecutively as separate pages inthe single document, or numbered consecutively in individual files.
You can now open the letters and edit them individually as you would edit any other document.
Creating a form letter | 9

Printing mailing labels
Labels are commonly used for printing address lists (where each label shows a different
address), but they can also be used for making multiple copies of one label only, for example
return-address stickers, or labels for CDs/DVDs or other items.
Before beginning this process, note the brand and type of labels you intend to use. You can also
define your own label size; see Step 6).

Tip
If you are printing the same information on each label, you may find the Business
Cards dialog useful. Access it through File > New > Business Cards. The Labels
and Business Cards dialogs are very similar.

Preparing for printing
To prepare mailing labels for printing:
1) Choose File > New > Labels. The Labels dialog opens.
2) On the Labels page (Figure 13), select the Database and Table.
3) From the Database field drop-down list, select the first field to be used in the label (in thisexample, FNAME), then click the left arrow button to move it to the Label text area.
4) Continue adding fields and inserting desired punctuation, spaces, and line (paragraph)ends until the label is composed. Figure 13 shows the completed label.

Figure 13: The completed label
On some operating systems, a scroll bar will appear below the Label text box and you
can see where the paragraph ends are located. On others (as in the example), the fields
may appear in a list and you cannot see where the paragraph ends are located. You cancheck them later in the procedure (Step 8)).
10 | Chapter 14 Mail Merge

5) Select the label stock in the Brand drop-down list. The types for that brand then appear in
the Type drop-down list. Select the size and type of labels required, and then go to Step
7). If your label sheets are not listed, go to Step 6).
6) To define labels that are not on the list:a) Select [User] in the Type box on the Labels page. Then, click the Format page of theLabels dialog (Figure 14). Measure on your labels those dimensions illustrated andenter them into the respective boxes on the left side.
– Horizontal pitch is the distance between the left edges of adjacent labels.
– Vertical pitch is the distance between the upper edge of a label and the upperedge of the label directly below.

Figure 14: Specify settings, if necessaryb) You can now save the label template if you are likely to use it again. Click Save.
c) In the Save Label Format dialog that pops up, type names for your label Brand andType. Click OK.
7) On the Options tab of the Labels dialog (Figure 15), select the Synchronize contents
option.

Figure 15: Options tab of the Labels dialog
Printing mailing labels | 11

8) Click New Document. You now have a one-page document containing a series offrames, one for each label of the selected type and filled with the data source address
fields that you selected. Although this document appears to have only one page of labels,
the printed or saved output will be expanded to include enough pages for all of theselected records from the data source.
9) In this document you can see the paragraph ends if you use the Toggle Formatting
Marks toolbar button or View > Formatting Marks on the Menu bar.
10) You should also see a small window containing a Synchronize Labels button, usually inthe upper left corner of the screen.

11) If any paragraph ends or other punctuation are missing, or if you wish to change the fontor other characteristics of one or more fields or lines (paragraphs) of all the labels, you
can do so now. In the upper left label, select the field(s) that you wish to change, thenchange them manually or by selecting a paragraph style or character style. Add any
missing punctuation. Then click the Synchronize Labels button to automatically add
these changes to all of the labels.

Printing
1) Choose File > Print. The message shown in Figure 9 appears. Click Yes to print.
2) In the Mail Merge dialog (Figure 10), you can choose to print all records or selectedrecords. To select records to be printed, use Ctrl+click to select individual records. To
select a block of records, select the first record in the block, scroll to the last record in theblock, and Shift+click on the last record.
3) Click OK to send the labels directly to the printer. If you prefer to save the labels to a file,then select File in the Output section, where Save as single document is preselected. In
this case, clicking OK opens the Save As dialog, where you can enter a file name andlocation, as described in Step 6) on page 7.

Note
Blank lines in addresses on labels are suppressed automatically, as they are whenusing the Mail Merge Wizard (page 14).

Editing a saved file of mailing labels
To edit a saved file of mailing labels, open the file in the same way as any other Writer document.
You can edit individual records manually (for example, to correct a spelling error without needing
to change the source spreadsheet and regenerate the labels), but you cannot edit all labels at
once (for example, to change the font used) by a manual technique. However, you can edit theparagraph and/or character styles associated with the label records:
1) Right-click in a label record. Choose Paragraph > Edit Style in the context menu.
2) Then in the Paragraph Style dialog, you can make changes to the font name, the fontsize, the indents, and other attributes.

12 | Chapter 14 Mail Merge

Printing envelopes
Instead of printing mailing labels, you may wish to print directly onto envelopes.
The production of envelopes involves two steps, setup and printing. For general instructions on
setting up envelopes for printing using the Format and Printer pages of the Envelope dialog, see“Printing envelopes” in Chapter 7, Printing, Exporting, Emailing, and Signing.
To produce a single envelope, you can type or paste the name and address into the Addressee
box, or you can get information from an address data source as described in this section.

Setting up envelopes for printing
1) Choose Insert > Envelope on the Menu bar.
2) On the Envelope tab of the Envelope dialog (Figure 21), choose whether to add Sender
information (the “from” address) to the envelope. If so, type the information in the Senderbox. If you’re using envelopes preprinted with sender information, either leave this box
empty or deselect the Sender checkbox.

Figure 16: Choosing addressee and sender information for envelopes
3) Create the Addressee fields by dragging and dropping from the data source headings, as
described in “Printing mailing labels” on page 10, then click the New Document button tocreate the envelope template.
Alternatively, click New Document, open the Data Sources window above the envelope
template, then drag the data source headings into the Addressee box on the envelope.
4) At this point you may wish to save this document as a template.

Printing envelopes | 13

Merging and printing the envelopes
To merge addresses and print the envelopes:
1) Choose File > Print. The message shown in Figure 9 appears. Click Yes to print.
2) The Mail Merge dialog (Figure 10) appears. As with form letters and mailing labels, you
can choose to print envelopes for one, several, or all address records in the database.
3) Make your selections and then click OK to print direct to the printer. To check theenvelopes before printing them, see “Saving merged documents” on page 9 for
instructions. Blank lines are automatically suppressed.

Using the Mail Merge Wizard to create a form letter
The manual method of creating a form letter is described in “Creating a form letter” on page 6. If
you prefer to use the Mail Merge wizard, the technique is described in this section.
Open a new document with File > New > Text Document and choose Tools > Mail Merge
Wizard on the Menu bar.

Step 1: Select starting document
The Mail Merge Wizard (Figure 17) gives several options for the starting document:•

Use the current document•

Create a new document•

Start from existing document•

Start from a template•

Start from a recently saved starting document

For the purposes of this example, we opened a new text document.
Select Use the current document and click Next.

Figure 17: Select starting document
If the current document does not have a registered data source associated, a warning message
is displayed. Click the Exchange Database button to open the Exchange Databases dialog(Figure 18) and select the required database from the Available Databases list, If necessary, click
the Browse button to open a file browser and find the required database to register. Click Define
to close the dialog and associate the selected database with the document.
14 | Chapter 14 Mail Merge

Figure 18: Exchange Databases dialog

Step 2: Select document typeThe wizard can produce letters or email messages. In this example, we are producing a letter.
Select Letter and click Next.

Figure 19: Choose document type

Step 3: Insert address block
This is the most complex step in the wizard. In this step (Figure 20) you will do three things:
1) Tell the wizard which data source to use. The data source must be an existing file.
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 UK
English version of the wizard has a field called <Surname>. If your spreadsheet has acolumn called “Last Name”, you need to tell the wizard that <Surname> and “Last Name”
are equivalent. This is described in “Matching the fields” on page 18.

Using the Mail Merge Wizard to create a form letter | 15

Figure 20: Insert address block

Selecting the data source (address list)
1) If the current address list, identified beneath the Select Different Address List button insection 1 (Figure 20), is not the one you wish to use, click the button to open the SelectAddress List dialog (Figure 21) for choosing a data source.

Figure 21: Select address list dialog
16 | Chapter 14 Mail MergeIf you have not already created the address list, you may click Create to do so now. This
step will allow you to create a .csv (Comma Separated Values) file with a new list ofaddress records. If you have an address list that is not registered in LibreOffice, but which
you wish to use, click Add and select the file from the location in which it is saved.
In each of these cases a new data source (.odb file) will be created and registered.
2) Select the address list and click OK to return to the Insert Address Block dialog. We retain
Addresses as our address book for this example. The wizard can also exclude certain
records; click Filter to choose them.

Selecting and optionally editing the address block
1) In section 2 (Figure 20), select the address block to appear on the letter, define its
appearance, and choose the fields it contains. The main page gives two choices. Click
More to open the Select Address Block dialog for more choices.
2) The Select Address Block dialog (Figure 22) displays the original two blocks plus otherchoices for the format of the address block (you may need to scroll down to see all of the
choices). You can also optionally include or exclude the country (for example, only include
the country if it is not England) in the Address Block Settings section. The formats
provided are relatively common, but they might not exactly match your preference. If thisis the case, select the address block that is closest to what you want and click Edit, which
opens the Edit Address Block dialog.
3) In the Edit Address Block dialog (Figure 23), you can add or delete address elements
using the arrow buttons between the boxes. To move elements around, use the arrow
buttons on the right. For example, to add an extra space between the first and the lastnames in Figure 23, click <Last Name> and then click the Move right arrow button on theright-hand side.

Figure 22: Select address block

Using the Mail Merge Wizard to create a form letter | 17

Figure 23: Edit address block4) Click OK to close the Edit Address Block dialog, then click OK on the Select Address
Block dialog to save your changes and return to the Mail Merge Wizard.

Matching the fields
Finally, it is time to match the wizard’s fields with the spreadsheet fields, so that items like
<Surname> and “Last Name” match correctly.
1) Look at section 3 of the wizard (shown in Figure 20 on page 16). The box at the bottom
displays one record at a time, using the address block format you specified. Use the rightand 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, justbecause one or two do.
2) If the addresses do not display correctly, click Match Fields.
The Match Fields dialog (Figure 24) has three columns:– Address elements are the terms the wizard uses for each field, such as <First Name>and <Last Name>.
– Use the Matches to field column to select, for each address element, the field fromyour 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.
3) When you have matched all the fields, click OK to return to the Insert Address Blockdialog. 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 the next step.

18 | Chapter 14 Mail Merge

Note that you will not be able to continue until you have correctly matched all the fields inyour chosen address block. If you see <not yet matched> in a field position it indicatesthat the field in question is not correctly matched.

Figure 24: Match fields dialog
4) Notice the option for Suppress lines with empty fields in section 2 of Figure 20. Using
the Wizard, you do not have to create your own conditional suppression fields, as you dowhen using the manual form letter method described earlier in this chapter..

Step 4: Create salutation
It is possible to create just about any salutation you want in this step (see Figure 25).
Select This document should contain a salutation to enable the General salutation list box.
Some general texts are available in the list box, or you can enter your own text. A preview pane
displays your choice.
Select Insert personalized salutation to enable further salutation constructs.
You can, for example, use a different greeting for men and women. To do this, Writer must havesome way of knowing whether a person is male or female. In a spreadsheet, you might have acolumn 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 theFemale salutation for all women. Unfortunately, LibreOffice does not provide for other genders;
however, you can use a non-gendered salutation.
If you do not have such a column in your spreadsheet, or if you do not need to distinguishbetween genders in the salutation, then you can leave the Field name and Field value boxes
empty and use the customized content of the Male list box for the salutation to all recipients.

Using the Mail Merge Wizard to create a form letter | 19

Figure 25: Create a salutation
As an example:
1) Click the New button alongside the Male list box. The Custom Salutation (MaleRecipients) dialog opens (see Figure 26).
2) Select Salutation in the Salutation elements list on the right and drag it into box 1 on theright.
3) In the Customize salutation box, select an appropriate greeting or type your own text into
the box. Edit it as needed.
4) Select and move First Name from the Salutation elements list into box 1, using either the
Add to salutation arrow button or by dragging it. Then click the Move right arrow buttonon the right-hand side to add space between the salutation and the first name.
5) Select and move Last Name from the Salutation elements list into box 1, then click theMove right arrow button to add space between the first and the last names.
6) Finally, move Punctuation Mark across and select the comma from the choices in the
Customize salutation box.
7) The construct is shown in the Preview box.
8) Carry out any final editing. Click OK.
This method allows you to use gender neutral titles such as Doctor (Dr) and Reverend (Rev), or
titles such as Ms, or omit titles.

20 | Chapter 14 Mail Merge

Figure 26: Customizing the salutation

Step 5: Adjust layout
In Step 5: Adjust layout, 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 theleft, but you can move it up and down the page. Use the buttons shown in Figure 27 to move theelements.

Figure 27: Adjust layout
Using the Mail Merge Wizard to create a form letter | 21

You now have a Writer document into which you can type the contents of the letter. To merge thefields and print the letters, choose File > Print on the Menu bar. A message pops up, asking if
you want to print a form letter. Click Yes.
The Mail Merge dialog (Figure 10) is now displayed, where you can optionally select records toinclude or exclude (in the top section) and, in the Output section, choose to print the lettersimmediately (Printer) or save them (File) for further editing or printing at a later time.
If you choose File, you can then choose whether to save the output as a single document(containing all of the letters) or save the letters as individual documents, and you can specify thename, location, and format of the files. Click OK to save the letters. You can now open the lettersand edit them individually as you would edit any other document.

22 | Chapter 14 Mail Merge