The office where I am network administrator switched most users to OpenOffice.org (OOo) back at version 1.1, and has followed the upgrade process to the current version 2.3 (a few poor users who have to exchange documents outside the office with high fidelity are still clinging to their MS Office 97). Our receptionist does a lot of general secretarial duties, including lots of letters, envelopes, and labels that involve mail merge. Since this seems to be a sticking point for many people, I am putting everything I have learned from helping her and have gleaned from various sources on the Internet together in this tutorial.

Mail merge overview

In OOo there are lots of different ways to do mail merge. It took some trial-and-error to find the best methods for us, and that is what I will be describing here. The first choice to make is database format. Our receptionist had always used Excel spreadsheets as data sources for MS Word, so we first tried using OOo’s Calc spreadsheets. It turns out that this choice leads to several limitations. For example, if you find a mistake in your data while you are doing the mail merge, you must return to the spreadsheet to fix the mistake, then completely close OOo and open it again before continuing the merge. Also, each spreadsheet must be set up as a new data source. I then ran across a suggestion to use dBASE files, which have been the perfect solution.

We found the dBASE file format to be the perfect solution for everyday mail merge tasks

We also made the decision to avoid the normal mail merge tools provided in OOo 1.1, which evolved into the Mail Merge Wizard in 2.x. While the wizard may be good for limited cases, it does not handle the variety of mail merge tasks we needed. It is actually fairly simple to go through the steps manually once you get used to them.

Creating data sources

The first step in preparing for any mail merge task is to set up your data source. If you will be doing a lot of mail merge with a lot of data sources, it is appropriate to plan ahead how your files will be laid out. Our receptionist has a directory for each department. When using dBASE files as your data source, you only need to set up one data source for each directory (as will be explained later), and can add and remove files as needed. If you are working with existing data, you can probably convert it by opening it in Calc and saving it as a dBASE file. This works for spreadsheets, comma-delimited (CSV) files, and any other filetype that Calc will open. In the first row of the file, specify field names. A limitation of dBASE files is that field names can only be ten characters—Calc will shorten whatever you put in to this length. You may also wish to specify the field type and length. Character fields may be specified as “FIELDNAME,C,10”, and dates may be specified as “FIELDNAME,D”, while numeric fields may be specified as “FIELDNAME,N,4,2”. On numeric fields, the first number is the length allowed before the decimal, and the second number is the number of digits stored after the decimal. If you do not specify these, Calc will determine the proper values based on the data in the column. I recommend that you specify zip codes especially, either as character or as number with no digits after the decimal, so you won’t receive any surprises when you merge. When you save the file, Calc will ask you to choose a character set. The default “Western Europe (DOS/OS2-850/International)” has always worked for us.

After you have one or more dBASE files, it is time to set up your data source. From any OOo application, click on File→New→Database. You will be taken to the Database Wizard. On the first screen, select “Connect to an existing database”, and choose dBASE as the type. On the next screen, either click on Browse and locate the directory where you stored your dBASE files, or type in the full path of the directory. Again I emphasize, you only point to the directory, not to an individual dBASE file. If you use the file browser, click OK when you are in the directory with the dBASE file(s). On the final screen of the wizard, leave the default to register the database, but probably turn off “Open the database for editing”. When you click Finish, you will be presented a Save dialog. I refer to the file saved here as a database connection—it contains only the information for OOo to access the data, not the data itself. Give it an appropriate name and save it wherever you wish. I prefer to designate a single directory for all of these files.

OOo database connections point to a directory of dBASE files, not an individual file

You can verify that the data source is set up properly by opening either Writer (the word processor) or Calc, then selecting View→Data Sources (or pressing F4). The document portion of your window will split vertically. You will see a list of your data sources on the left side of the top area (see figure 1). If you click on the + next to the name of your new data source, and on the + next to Tables, and on the name of the dBASE file, you will see your data on the right side. From this view you can edit the data, as well as add and delete records.

Figure 1: viewing data sources in OOo

Using mail merge for letters

Now it is time to put the data to use. Open Writer and start your form letter. You may either type your entire letter first and then add the fields to be merged, or you may add the fields as you go. There are (at least) two ways to add fields. Using View→Data Sources, you may click on a column header (field name) and drag it to the letter in the spot where you want the field. Our receptionist did not care for this approach because she did not have as much control over where the field ended up, could not view as much of the letter at a time with the data view at the top, and had no control over formatting of numbers, dates, etc. The other method is to place your cursor where you want the field, and go to Insert→Fields→Other…, which opens the Fields dialog box (see figure 2). Go to the Database tab, and click on “Mail merge fields” on the left, then open up your table on the right and select the desired field. If any special formatting needs to be applied, use Format→User-defined→Additional formats… (if necessary) at the bottom.