How To Make Mailing Labels From A Spreadsheet Using Open/Libre Office

Let's get this out of the way right now: The procedure is horrible for non-technical users. With Open/Libre Office you get fabulously more than you pay for, but that does not include a specialized application for printing labels from a mailing list. Instead, a combination of low-level features give us what we need.

First, Upgrade Libre Office to 4.x

Earlier versions of Libre Office had bugs which positioned labels incorrectly.
In Libre Office, do Help->About to see which version you have.

On Windows or Mac, download Libre Office 4.x here.

On Ubuntu 12.04 you have to use a special repository to get Libre Office 4.0 (as of May 2013):

sudo add-apt-repository ppa:libreoffice/libreoffice-4-0 # once only
sudo apt-get update
sudo apt-get dist-upgrade # or   sudo apt-get install libreoffice

Can I use Open Office?

Yes. These instructions were tested with OO 3.4.1, but the page size defaulted incorrectly (set it manually to A4 or Letter etc. at Format->Page, Page tab). Be sure to select "Synchronize contents" when creating the mail-merge file, and insert the "next record" token at the end of the label (these things are explained below).

Overview of the Mail-Merge Procedure

  1. Create a spreadsheet with your mailing list (name, address, etc.)
  2. Create a dummy database to interface the spreadsheet to the mail-merge machinery.
  3. Create a mail-merge document containing a grid of labels.
  4. Edit the mail-merge doc to format labels using db field references.
  5. Process the mail-merge doc to generate another text document with plugged-in values from the spreadsheet.
  6. Print the last-mentioned document onto labels.

How-to in Detail

  1. Put your data in a spreadsheet with column titles in the first row (e.g. "Name", "Address" ...)
    We will use the file name "mailinglist" as an example. Any supported file type is okay (.ods, .xls etc.)

  2. Create a dummy database which interfaces the spreadsheet to the label generator:

    The "mail-merge" machinery only works with data from a database; it can not read a spreadsheet directly. You must manually create a separate "database" file to serve as a conduit. This "database" won't contain any data, but will obtain data from the spreadsheet when needed:

    1. Be sure the spreadsheet has been saved (File->Save or Control-S).
    2. Start Open/Libre Office if no document is open.
      Then: File->New Database
      • Click "Connect to an existing database"
      • Select "Spreadsheet" in the drop-down menu.
      • Click Next>>
      • Browse to your mailing-list spreadsheet.
        Click Finish>>
      • Again browse to the folder containing the spreadsheet and choose a related name for the database file, for example "mailinglist_db" (an .odb file will be created).

  3. Create a mail-merge text doc containing labels.
    A special procedure creates a grid of similar labels:

    1. Start Open/Libre Office if no document is open.
      Then: File->New Labels
      • On the Options tab, select "Synchronize contents".
      • On the Labels tab:
        Select the label brand/category in the "Brand" menu (most people in the U.S. will use "Avery Letter Size"), and a specific product number in the "Type" menu. You can also specify raw dimensions on the Format tab.
      • In the "Database" drop-down menu, select the database you just created.
        In the "Table" menu, select "Sheet 1" (or the appropriate sheet).
      • Do not enter label fields now, but wait until after the document is fully created (this avoids a problem with line-breaks which interferes with suppressing empty lines). But if db fields already appear, just leave them as-is.
      • Click "New Document" to create the label mail-merge document.
    2. Save the new document ( File->Save As or Control-S). Once again browse to the folder containing the spreadsheet and choose a related name, for example "mailinglist_mmerge" (it will be an .odt file).

    The remaining steps can be done after closing and re-opening the mail-merge document.

  4. Edit the mail-merge doc to format labels using db field references

    Note: Edit only the first label on the page at the upper left, then click the floating Synchronize labels button to copy your changes to the other label positions.

    When editing label text, be sure to click inside the label border (when the cursor has an I-beam shape). If you click the border itself then the entire frame object will be selected, indicated by green resize handles. You don't want to change the frame in any way, so if this happens click outside the page to de-select the frame.

    1. Insert "database fields" where data values should be merged in.

    2. Mark paragraphs to be hidden if their data field(s) are empty:

      In the example above "ADDRESS2" might sometimes be empty; in that case we want to to avoid inserting a blank line in the output. To accomplish this feat, the paragraph containing mailinglist.Sheet1.ADDRESS2 should be conditionally hidden if the ADDRESS2 data field is empty for a particular label:

      • View->Data Sources if needed.
      • View->Hidden paragraphs checked
      • View->Non-printing characters enabled
      • View->Field Names checked
      • Click immediately before or after "mailinglist.Sheet1.ADDRESS2" to prepare to insert. Be careful to not select the db reference itself or you'll end up replacing it.
      • Then: Insert->Fields->Other (a "Fields" dialog opens)
      • Click the Functions tab, then Hidden Paragraph
      • Enter an expression in the "Condition" box like this:

        Drag the "ADDRESS2" column header from the Data Sources area to the "Condition" field in the Fields dialog (View->Data Sources to display Data Sources). Then insert an exclamation point in front of the resulting database reference. You should end up with something like this:

                       ![mailinglist.Sheet1.ADDRESS2]

      • Click Insert once only, then Close

      You should see Hidden Paragraph inserted into the label (visible only with "View->Field Names").

    3. Add margin spacing: The boxes in the grid correspond to the physical edges of the labels, so the text must be inset from the edges to avoid registration problems while printing:

      • Add top margin: Click in the first paragraph (i.e. the top line) in the label, then Format->Paragraph and set "Spacing Above paragraph" to 0.1"; click OK.
      • Add left margin: Select all the paragraphs (lines) in the label, Format->Paragraph, and set "Indent Before text" to 0.18" or similar; Click OK.
      • Finally, adjust the font size so all lines fit, taking into account whether you expect any data values to be long enough to cause line-wraps. It's helpful to disable "View->Field Names" at this point to reduce clutter. Select all paragraphs (all lines) in the label, then use the font-size drop-down in the tool bar to set an appropriate size. Note: You can type in fractional sizes which aren't in the menu, for example "11.5".

    4. Click the floating Synchronize labels button.
    5. Save your work (File->Save or Control-S).

    Now you have a mail-merge document which contains one page of labels, with database-field references where the actual data will be plugged in.

  5. Process the mail-merge doc to generate another text document with the final label images.

    1. Open the label mail-merge document (mailinglist_mmerge.odt in our example) if not already open.
    2. File->Print (or Control-P)
      A pop-up will ask "Do you want to print a form letter?" — click Yes.
    3. A "Mail Merge" window will appear:
      • Check "All" records
      • Check "File" instead of "Printer"
      • Check "Save as a single document"
      • Click OK
      Navigate to the same folder as before and enter a related name, for example "mailinglist_forprint".
    4. Open the new document (mailinglist_forprint.odt) and look it over.
      If there are missing, duplicated, or scrambled names, see "
      Trouble Shooting".

  6. Print the final document onto your labels.

    If you export to PDF instead, be sure to un-select "shrink to fit" or other scaling options when using it to print labels.

Trouble Shooting

If blank lines appear in the middle of labels:
Re-visit the procedure to hide paragraphs which contain empty data fields. A "Hidden Paragraph" token must be inserted in each paragraph which might have no data, using an appropriate "Condition" expression. Note: If a line contains multiple db fields, and all of them might be empty, then the "Condition" expression should be something like ![mailinglist.Sheet1.FIELD1] && ![mailinglist.Sheet1.FIELD2]

Also make sure there are no hard line breaks; a new line should be started only by starting a new paragraph. To verify this, use View->Non-printing characters and look for paragraph symbols (¶) and verify that no line-break symbols (↵) are present.

If the same address is printed on every label on a page:
The "next record" token is missing. In Libre Office this token is provided automatically, but in Open Office you must explicitly insert it. It is visible only with "View->Field Names" in effect.

To restore the "next record" token:

  1. Edit the mail-merge file (mailinglist_mmerge.odt in our example).
  2. Temporarily reduce the font size to 6 pt as suggested above.
  3. Place the cursor just after last database field in the first label
  4. Insert->Fields->Other,
    Click the Database tab,
    Click "Next record",
    Click Insert and Close,
  5. Restore the desired font size if you reduced it temporarily.
  6. Click the floating Synchronize labels button, then save.

If parts of different addresses are mixed together:
The "next record" token is in the middle, rather than after the last db field. This causes the first part of one record to be combined with the last part of the next record. Remove the token and re-insert it at the end, as described in the previous item.

If some records are skipped:
Probably there are multiple "next record" tokens present. See above.

I don't see any "floating Synchronize labels button" !
It's in a separate small window which can appear anywhere on your screen, and might be hidden when the document doesn't have input focus. Note: When the mail-merge file is first created with File->New Labels, the "Synchronize contents" option must be selected on the Options tab. It it wasn't, you'll have to re-create the file from scratch.

I moved my files to another folder or computer and now nothing works!
The connections from the mail-merge file to the dummy database, and from there to the spreadsheet use absolute file paths. That means that if the files are moved to a different location the connections stop working.

To fix the database file (mailinglist_db.odb in our example), open it and use Edit->Database->Properties to change the path to the spreadsheet document. Use Test Connection to verify the path.

To fix the mail-merge file (mailinglist_mmerge.odt in our example), open it and do Edit->Exchange Database and then browse to find the database file. It should then appear in the "Available Databases" panel. Click the triangle to view the sheets and select "Sheet1" (or as appropriate). Click Define.

Feedback / Contact

Please email comments & corrections to james_avera at Yahoo's mail service.



This guide was developed using Libre Office 4.0.2.2 on Ubuntu 12.04 LTS x86_64
Relevant Open Office documentation is here.
Last updated Fri Feb 28 23:28:49 2014 jima.