MailMerge on OpenOffice and LibreOffice

by Mike on March 29, 2013

in Training

In its most common use, MailMerge allows you to customize a standard letter you want to mail out to a bunch of people. You have your letter and you have a file with their names and addresses in. Using MailMerge you put the two together and end up with one individually addressed letter for each person.

In principle MailMerge can be used for almost anything – not just letters. A good MailMerge system allows conditional elements too. Suppose you worked at a clinic and were sending out letters to people about their test results. You might be able to MailMerge in paragraphs either letting them know the test results were clear, or advising them to contact former partners.

There’s something we should admit here. OpenOffice and LibreOffice are not that great at MailMerge. Never have been. Sure, there’s a MailMerge wizard to help you, but it’s more Ron Weasley than Hermione Granger. It means well, but sometimes confuses more than it helps.

Just one example. The wizard asks you to pick a data source (i.e. the file with your list of names and addresses). You would think that it would let you pick a spreadsheet or a csv (comma-separated) file. And you’d be right, it does let you. But then it won’t do anything with it. Behind the scenes, the wizard tries to convert your spreadsheet into a database file, but (in my testing at least) silently fails, so you’re left staring in frustration at a grayed-out “Next” button with no clues as to how to get it working.

So, bearing that in mind, here’s how to do a simple MailMerge in LibreOffice – and it’s pretty much the same for OpenOffice.
1. Check you have the Base application installed

To use MailMerge you need to have a database file to merge with. Your first job is to make sure you have that file, or you won’t get very far. This is pretty simple, but requires a few extra steps. The first is to check that you have the Base component installed (the Database application) – it isn’t there by default on all distros. If you don’t have it, then get it.

2. Create your list of people in LibreOffice Calc

You need to end up with a file or database with the information in it – in this case the names and addresses of people you’re sending a letter to. Calc is probably the easiest tool for this task. Remember to break up the address so you have address line 1, address line 2, zip code and city as different fields or columns. For reasons you’ll see later, it may be useful to also have a sex column – putting an F by every female will make your life easier when you come to use the wizard.

3. Launch LibreOffice Base and convert your data file to an odb database file.

When you launch Base, the Database wizard gives you several options. Select “Connect to an existing database” and then choose “Spreadsheet” as the type of database to connect to (yes, I know this is some distance from being intuitive, but go with it).

Click on next and then follow through the wizard. You first select the spreadsheet file to open, then you’re prompted to name and save your new database.

Once you’ve saved your new database file, it’s job done (for this part). If you want to check that you’ve got the correct file with the right data, look at the screen that comes up next. Under the ”Tables” section of the screen, double-click on “Sheet 1” and your data should display.

It’s worth mentioning that, in compensation for having to convert your data file into odb format, LibreOffice Base does at least work with a wide range of file types so if you’ve got data in an Oracle or MySQL database, in an Outlook or Thunderbird address book, in a plain text file or an Excel spreadsheet, it should be able to handle it.

4. Create your document

Open up a blank document in LibreOffice Writer. From the pull-down menu go to Tools > MailMerge wizard. In the wizard click on Step 3 (“Insert address block”) – since you don’t need to change the defaults on the first two steps. Under step 3, you need to select your address list (that odb file you saved earlier).

If your document needs to include an address block (as a standard letter will), click the tickbox.

The really important part is matching the fields – part 3. You need to make sure that the MailMerge wizard knows which fields are which – and it doesn’t figure it out automatically. Click on “Match Fields” and pair up your fields to the ones it expects.

Not sure if you’ve got it right? Take a look at the sample addresses at the bottom of the wizard window – if they look right, then you’re OK. It’s worth checking a few records – it might be that some are longer than others and you want to make sure they’re all correct.

You can choose from a number of pre-defined address blocks, or create your own, but the wizard won’t let you continue until all the fields are matched (another design decision seemingly aimed at making your life more difficult that it needs to be – it could easily default to leaving unmatched fields blank).

5. Completing your mail merged document

Creating a salutation comes next. Instead of just accepting a salutation or title field (e.g. Mr, Mrs), LibreOffice wants you to tell it how to distinguish men from women, and expects you to have a field for sex in your data source. Quite why, I have no idea, but aren’t you glad you included it as a separate field earlier! (If you’ve no sex field, you can work round this by not using the wizard).

Going through the rest of the wizard, you’ll see that it allows you to position the address block and salutation, to edit the standard portions of your letter (i.e. where it’s the same to every person) and then to make any customisations to specific letters – either to amend one in some way, or to exclude it.

Finally, the wizard allows you to print or save your document in all its glory.

Creating more general MailMerge documents

The MailMerge wizard does a serviceable job if you want to create a mailmerged letter or email.

You might not, though, and MailMerge is much more flexible.

LibreOffice and OpenOffice give you this level of flexibility. From the pull-down menu go to Insert > Field > Other > Database. Select “MailMerge field” and click on the + symbols on the right hand side to drill down to the individual fields. You can add additional data sources from this window too – selecting the odb file in the same way as with the wizard.

Conclusion

Although still well behind many other office suites in usability, MailMerge in OpenOffice and LibreOffice is now a decent, flexible tool with a reasonable wizard. If you take the time to find your way around it, you’ll find that it serves you well.

 

Previous post:

Next post: