Nick Peers reveals how to save time and master mail merge in LibreOffice using a combination of Base, Calc and Writer
Anyone who’s had to write the same letter to many different people, dutifully substituting names and addresses for each one, will appreciate what mail merge can offer. (So will those too lazy to write names and addresses on envelopes at Christmas time). Thankfully, LibreOffice Writer possesses a tool that can simplify the process in the form of its Mail Merge Wizard. A mail merge works by taking a list of contacts and automatically inserting key information from that list into specific parts of a document – eg the address block in a letter, or the personalised greeting that opens it. The contacts list is maintained separately and can originate from one of three places: a database, a properly formatted spreadsheet or a CSV file.
LibreOffice even allows you to use an existing Thunderbird or Icedove address book as the source for your contacts list, although this involves converting it into a read-only database using the File > Wizards > Address Data Source… option. For the purposes of this tutorial, though, we’re going to focus on using a properly formatted spreadsheet as the basis of our contacts list. This spreadsheet can then be directly imported into the Mail Merge wizard (see Mail merge using Writer and Calc, below) or used to populate a Base database that gives you greater flexibility over its use.
Set up your contacts list
Your contacts list should – in the grand manner of all databases – be organised into fields and records. Each individual’s contact details form the basis of each record, and the component parts of that record are organised into fields: such as first name, surname, street address etc. This data is then represented as a table, with each field assigned its own column and individual records recorded along each row.
The first row in any spreadsheet should contain the column headers, which help identify each field. If you’re starting out from scratch, open Calc and create a blank database. Type each field name into the columns along row A – here’s an example of what to include: first_name, last_name, address, town, county, country, postal code, home_phone, work_phone, mobile, email, website, category.
Obviously, you may need to include more details – eg some addresses require a second line, so include address1 and address2. Similarly, you may have no need for a country or website, so feel free to exclude these. One particularly important column header is category. This allows you to assign each contact to a particular group, such as ‘home’ or ‘work’. To find out more about this field – and add it to an existing list of contacts – check out the Prep Calc Database for Transfer box (see 'Prep Calc database for transfer').
With your column headers in place, you’re now free to record each contact’s details. Remember, each contact takes up a row in your database, so your first record is recorded in row 2, the second in row 3 and so on. Don’t forget to save as you go, and once you’re done, congratulations – you’ve created your contacts database. Don’t worry too much about recording people’s information in alphabetic order – simply select the column containing your surnames and click the ‘Sort Ascending’ button on the toolbar should you want to keep things nice and neat.
Transfer to Base
As things stand you’re now ready to jump straight into using Writer’s Mail Merge tool (the box on p77 reveals how this works using your Calc spreadsheet). But while Calc is adequate for the task, there are reasons to go further and use the data from your spreadsheet to create a fully blown database using LibreOffice’s database component, Base.
First, Base supports more complex databases – including relational databases that allow you to create multiple tables of data and link them together. If, eg, you have a large DVD collection that you lend out to friends, you could track lending using three tables: your original contacts list, a second table listing all your DVDs, and, finally, a third table that allows you to link DVDs with the people who’ve borrowed them.
Second, Base supports queries, which allow you to create custom subsets of your contacts list based on a series of filters. In this tutorial, we’ll use queries to allow you to set up mail merged documents targeted specifically at contacts that reside in a particular group or category of contacts, but you can go further. You could set up a query to filter your contacts by the town they live in, or their surname (Base allows you to match contacts based on multiple criteria, so eg you could include three or more different surnames to cover everyone in your family).
Base also provides a better way of inputting data into your contacts list by allowing you to set up a form-based entry screen, which is easier to navigate than a table. And finally, you can use your contacts list to generate reports of your data using Base’s Reports tools too – eg a neat way to generate a formatted printout of your contacts.
The good news is that moving from Calc to Base doesn’t mean you have to start your contacts list again from scratch. Here’s how to import your data from your spreadsheet: first, open Base and the Database Wizard will open. Leave ‘Create a new database’ with the default ‘HSQLDB Embedded’ option selected and click ‘Next’. Leave the default settings as they are and click ‘Finish’. Browse to your desired location – typically Documents, then give your database a name and click ‘Save’.
Now switch to Calc and open your spreadsheet. Select the data to be copied – including the all-important column header – and choose Edit > Copy. Move back to Base, make sure in you’re in Table view and choose Edit > Paste to open the Copy Table window. Leave ‘Definition and data’ selected, then make sure both boxes – including ‘Create primary key’ – are ticked before clicking ‘Next’. Review which fields you want to import – click ‘>>’ to import them all, or select each one in turn and click ‘>’ to add them to the list. Click ‘Create’. You should see ‘Table 1’ appear under ‘Tables:’ double-click it to verify all your records have been imported. Close the window when you’re done. Now your database has been created, it’s time to add some queries to it, which will allow you to filter your contacts according to friends, work colleagues or whatever criteria you’ve set. Follow the three-step walkthrough to do so (see 'Create database queries to filter your records').
Let’s merge
Once your contacts list and queries are in place, you’re ready to start taking advantage of them in Writer. The good news is that a mail merge wizard is provided for letters and emails, which makes the process as straightforward as possible. It works by generating a single document that contains multiple pages or sections, one for each person in your contact list.
The six-step walkthrough steps you through the entire wizard: select your data source, which is one of your database’s queries for a filtered list, then set up your two mail-merged elements, namely the address block and personalised greeting. Then it’s on to creating the document, adding personal touches to individual letters and finally saving, printing or emailing the finished result. Make sure you’re happy with the generic version of your letter before you jump to step seven of the wizard, where you can edit specific letters if you wish. If you go back and edit the main document, those personalisations will be lost.
Once you’ve finished creating your mail-merged letter, you’re given four choices: the first is to save the starting document, which saves the basic document. Second is ‘Save merged document’, which gives you the option of saving a single multi-page document or saving each personalised document separately – choose a filename, and Writer will append a number to the end of each one. You can also save a specific range of documents by entering the start and end numbers.
Option three allows you to print off your merged documents – again you can print them all or specify a range. Finally, you can use the email field in your contacts list to send out emails of the document to your contacts (plus manually enter addresses in the CC and BCC fields by clicking ‘Copy to…’). Specify a subject, choose to send the message as plain text, HTML or in an attachment (OpenOffice, PDF or Word are supported) and again opt between sending to everyone or specific people in the list only. Click ‘Send documents’ and then – if necessary – enter your email account information into LibreOffice for it to use to send the email.
Printing labels and envelopes
One final way in which you can use your database of contacts in Writer is to print out a series of envelopes (Insert > Envelope) or labels (File > New. Labels). Both work in a similar way: in the case of labels, leave the Address box unticked – this simply refers to your own return address as defined under Tools > Options > LibreOffice > User Data. Next, select your contacts list from the Database drop-down menu, then pick the table or query containing the addresses you wish to use from the Table menu beneath it.
Next, you need to select each database field in turn and click the left arrow button to add them to the Label text box. Things can get a little tricky here with placement – eg, don’t forget to add a space between the first and last name fields, plus add a line return between each part of the address.
Once done, use the Format options to set the dimensions of your label. Click the Brand dropdown menu to see if your labels are covered – there’s Avery, Herma, Leitz, Sigel and Tower. If it’s here, click the Type dropdown menu to pick the exact sheet; if not, switch to the Format tab to enter this information manually using your labels as a guide. Finally, the Options tab allows you to distribute your labels across an entire page or print them singly, plus configure your printer using the Setup… button.
When you’re ready, click the ‘New Document’ button and wait for Writer to generate the labels. You won’t actually see the names and addresses in your contacts list – just a series of field names. This is normal – when you select File > Print, you’ll be warned that your document contains address fields and asked if you want to print a form letter: click ‘Yes’.
The Print dialog will appear with your chosen contacts shown – you can then print them all, select a numbered range or hold the left Ctrl button as you click individual rows next to contacts to pick and choose exactly which addresses to print. You can then choose to output the file to your printer or save it as a file that, when opened, replaces the field information with the actual names and addresses of your chosen contacts.
Create a mail merge letter
1 Select contacts
Open Writer to a blank document and select Tools > Mail Merge Wizard. Leave ‘Use the current document’ selected and click ‘Next’. Verify Letter is highlighted and click ‘Next’ again. First, select your database and click ‘Change Table…’ to select which filtered list you wish to use from the available queries (click ‘Preview’ to verify it’s the right list). Click ‘OK’ twice.
2 Edit address block
Click ‘More…’ to choose the address block you want to use – select the closest template to your needs and click ‘Edit…’. Add new elements to the list by dragging them from the left-hand pane into position. Click and drag existing elements to reposition in the list, or select an unwanted element and click the ‘<-’ button to remove it. Click ‘OK’ twice when you’re done.
3 Match fields
Next, click the ‘Match Fields…’ button. Use the drop-down menus next to each element to match them to the fields in your database – you don’t need to match every one, so leave any element you don’t plan to use marked as ‘< none >’. Use the Address block preview window to check your fields match correctly, and click ‘OK’ when you’re done followed by ‘Next’.
4 Set up greetings
Opt to provide a generic greeting by unticking ‘Insert personalized salutation’ and choose from the General salutation drop-down menu. Alternatively, leave it ticked to provide a personal one using your contacts list. If your greetings are gender specific, select your gender field from the ‘Field name’ drop-down menu, then select the value (typically ‘F’ or ‘Female’) that specifies it’s female.
5 Create personalised salutation
Click the ‘New…’ button next to Female. Drag the Salutation element into the top box and type the greeting (such as Dear) into the Customize salutation box. Next, drag First Name into the list and then finally drag Punctuation Mark in. Select this and insert a comma (,) into the Customize salutation box. Use the Preview box to check everything looks okay and then click ‘OK’.
6 Compose and send
Click ‘Next’ to choose where the address block and salutation appear on the page. Click ‘Next’ then ‘Edit Document…’ to write your actual letter. When it’s finished, click ‘Return to Mail Merge Wizard’. Click ‘Next’ and all the individual documents will be generated as separate pages. You’ll have the opportunity to personalise individual letters, then click ‘Next’ to save, print or email the merged document. LXF
Mail merge using Writer and Calc
If you don’t have time to get bogged down in databases then it’s possible to perform a mail merge operation using your contacts spreadsheet as the source – you can even set up filters to include only a subset of your records.
Once your Calc spreadsheet is up and running, create a blank document in Writer and choose Tools > Mail Merge Wizard. The actual steps you follow are practically identical to those in the sixstep walkthrough (see 'Create a mail merge letter'), but there’s one crucial difference – when you come to select your Address List, click ‘Add…’ to browse for your spreadsheet file. Once it’s in the list, highlight it and click the ‘Filter…’ button. You can add up to three filters to specify exactly which records in the list.
Adding a filter involves selecting three elements: field name, condition, and value. To send a letter, eg, to all your work contacts, after clicking ‘Filter…’ you’d select your category field under ‘Field name’. Set condition to ‘like’ and enter the category (‘Work’) in the Value box. If you want to add other conditions, select the field name, then choose whether the condition can be matched on its own (select OR) or needs to be matched in addition to the original condition (AND).
Create database queries to filter your records
1 Create basic query
You’ll need to create separate queries for each group of contacts you have in Base. Switch to Queries view in the left-hand pane, then double-click ‘Use Wizard to Create Query’. Click ‘>>’ to add all available fields to the query. Click ‘Next’, then use the dropdown menus to ensure results are sorted in the desired order (typically surname and then first name). Click ‘Next’.
2 Finish setting up query
Leave ‘Match all of the following selected’, then click the Fields drop-down menu to select your group or category field from the list. Leave Condition set to ‘is equal to’ and enter the desired group value (such as ‘home’ or ‘work’) into the Value box. Click ‘Next’, leave ‘Detailed query’ selected and click ‘Next’ again. Leave the aliases as they are, then click ‘Next’ again.
3 Name, test and repeat
Finally, give your query a more easily identifiable name – eg ‘Query_Group_Work’. Click ‘Finish’ to view the filtered list of contacts. Close the window, then repeat the steps to set up separate queries for each group in your contacts list. The procedure is identical except you need to select the table rather than a query from the Tables dropdown menu in the first step of the wizard.
Prep Calc database for transfer
Before you import your spreadsheet list of contacts into Base, open it in Calc and first verify you have column headers (such as first_name, last_name, address and so on) in row 1 – Base will create the relevant fields from these headers. If you’ve not done this, select the first row by clicking the ‘1’ to the left of it, then rightclick and choose ‘Insert Rows Above’. Enter your field names into the new blank row.
When entering or editing field names, make sure there are no spaces between words by using an underscore character (‘_’) as a substitute – for example, ‘first_name’.
Finally, you need to add one or two extra fields to your list: name the first field ‘category’ or ‘group’, which allows you to filter contacts accordingly into groups such as ‘home’, ‘family’, or ‘work’. The second field is only applicable if someone’s gender is important when writing the salutation in your letter (eg, Dear Mrs Smith). Name this ‘gender’, then assign M to males and F to females.
If you’ve got a large number of contacts, filling in their category and gender by hand is going to be a tedious process, so speed things up by typing the first category – say ‘home’ – into the first cell where it appears. Now rightclick this cell and choose ‘Copy’. Next, hold the left Ctrl key as you click in each cell where you want ‘home’ to appear. Once they are all highlighted, you just need to right-click and choose ‘Paste’, and you should see it’s quickly added to those cells too.