Mail Merge in Libreoffice

Update 2013-08-20: Here is a bug report I submitted about unclear documentation.

A less developer oriented post today. I find there to be an incredible lack of tutorials on how to mail merge in LibreOffice. So I made my own.

How to Mail Merge with LibreOffice

This tutorial assumes you have an xls or ods file with data you want to “merge” to a document.

Making labels?

Then do this instead. The documentation is still rather unclear. You will need to use the Edit, Exchange databases as described below. I’ve also noticed if I try to print directly as the documentation would leave you to believe, it gives me blank pages in between each sheet! To solve this, set up the labels but instead of hitting print, select Tools, Mail Merge Wizard as proceed as described below. Essentially you are still performing a mail merge, except by starting from New, Labels, it will take care of the otherwise complex formatting of a label.

Step 0 if using Ubuntu – Ubuntu doesn’t include the full LibreOffice suite by default (see bug report). It includes a minimal version. If you try to use a feature that isn’t installed it will crash or just not work. Lovely. Install the full version of libreoffice in by opening Ubuntu software center, search for Libreoffice, click install. Or via command line
sudo apt-get install libreoffice

  1. Ensure you have proper headers on your spreadsheet and save it. Take note where you save it to!

    Compiz why did you make this blue?

  2. In Writer, Click Edit then Exchange Database… Then click Browse.  Select the file you just made

    Why is it named exchange database?
  3. Click View, Data sources (or press F4)

  4. On the left is a list of data sources. These should include the file you just made. Select it, then Tables, then Sheet1 (or the name of the sheet you want)

  5. You will see the data from the spreadsheet. Click and drag the column you want into your document. For example if I wanted First Name, I would click and drag the First Name column title and not an individual cell like Bob. You will notice it appears gray in your document. You are free to cut and paste it or change the formatting. These words will be replaced with the data in your spreadsheet. Note that Libreoffice can’t do conditional fields. For example let’s say you have

    City, State, Zip

    Some people have one address but not the other. Libreoffice will insert a blank line no matter what. You can get around this by using the “address block” feature but that’s very limited and won’t work for every use case. You can also try making conditional sections but with such difficulty in doing that, you might as well just make your text document in Python.

  6. Click Tools, Mail Merge Wizard or click the envelope icon in data sources. This stuff is mostly stupid and deals with preformatted address blocks, etc. I’ve never under any circumstance use them but you will need to disable them all. Work through each step and click next to continue:

    1. Select starting Document: Select current document.
    2. Select document type: In this example we intend to print or save the file so select Letter.
    3. Insert address block: Ensure “This document shall contain an address block” is unchecked.
    4. Create salutation: Ensure “This document should contain salutation” is unchecked.
    5. Edit document: By clicking the left and right arrow we can preview each page.
    6. Personalize document: This screen shows us the finished document which is editable.
    7. Save, print or send: What do you want to do this the finished document? You may save it, print it, or email it (may require additional setup). Save merged document will save the finished multi page document and Save starting document will save your template for later use.

Suppressing blank mail merge lines

Thanks to Juanito for the comment. You can in fact suppress blank lines (ex address 2) though it’s anything but intuitive.

  1. Insert field
  2. Ensure you are entering paragraphs instead of line breaks. Click view, nonprinting characters to check. Ex paragraph mark Screenshot from 2013-03-19 14:29:10
  3. Hover over field until name appears, e.g. mailmergedata.Sheet1.Address2, and note the name. If the name has spaces do this [file with spaces.Sheet1.Adddress 2]
  4. Place the cursor to the left of the field. 
  5. Click Insert, Fields, Other
  6. Click the Functions tab
  7. Select Hidden Paragraph
  8. In the Condition box, enter: NOT [field name from step 2], e.g. NOT [mailmergedata.Sheet1.Address 2]
  9. Click Insert (this might not cause any visible change)
  10. Click Close
  11. Click the View menu and make sure Hidden Paragraphs is unchecked
  12. Merge!

Overall thoughts? Libreoffice is terrible. A hugely common use case is convoluted. Looks like Office 2010 also requires this conditional paragraph suppression too. The world marches backwards.