OpenOffice Mail Merge

Create a spreadsheet with names, addresses etc…, create a database file linked to the spreadsheet, and then create envelopes or letters (the template) to specified recipients from the database. What follows is a manual method for setting up the database. You may also use the Mail Merge Wizard, but what follows offers more control and a better understanding of what is happening.

1. create a new spreadsheet file
2. label the top row of each column for the data you will input below (i.e.: last name, first name, address 1, etc…)
3. input each person as desired in the rows below
4. (optional) you may wish to filter recipients based on specific criteria
   • create extra columns for codes, or holidays such as Easter, Thanksgiving, and Christmas
   • if you create columns with names instead of using codes, put an x or similar identifier in each corresponding column if you want that person to be included in that list
5. save the file and note the name and location for the next step

* You may also use Tools → Mail Merge Wizard to automatically create the database

1. create a new database file
2. in the database wizard, select “Connect to an existing database”, “Spreadsheet”, and click Next
3. browse for the spreadsheet file you just created, and click Open, then Next
4. select 'Yes, register the database for me“
5. uncheck “Open the database for editing”
6. click Finish, and save the database file (doesn't matter where, but probably put it in the same folder as the spreadsheet)

• if you selected “Yes, register the database for me” from the previous section, then the database should already be recognized by OpenOffice

1. open the Options dialog: Tools → Options (Preferences on the Mac)
2. OpenOffice Base → Databases
3. New
4. Browse… for database file (.odb)
5. Click Open, OK, OK

* The template can be an envelope, letter, or anything else where you select individual fields from your database and orient them wherever you like, with formatting and fonts.

1. create a new Text Document
2. Insert → Envelope…
3. Click the Printer tab
   1. select the appropriate feed direction for your printer
4. Click the Format tab
   1. select the proper envelope under Size → Format, or enter a custom size
   2. position the Addressee / Sender accordingly
5. Click the Envelope tab
   1. select your database from the Database drop down menu
   2. select your sheet name (probably Sheet1)
   3. select a desired Database Field
   4. click the arrow to add the field to your Addressee block
   5. type enter after the field you just added, or do whatever you like to format it accordingly
   6. add other fields as desired, while typing commas or other things when necessary
   7. type your address in the sender box
6. Click New Doc. to create a new document with the proper information
7. save the envelope template document (suggestion: same folder as spreadsheet, Example: “Envelope - No 10.odt”)

1. create a letter as desired, until you get to the point where you want recipient data to appear (addresses, salutations,e etc…)
2. View → Data Sources
3. expand your database until you get to <database name> → Tables → Sheet1 (or whatever your sheet is called)
4. when you click your sheet, you should see a listing of your columns
5. click and drag from the column heading of your choice into your main document, where you want this data to appear
6. press return or format accordingly
7. drag another column heading into your document, as desired
8. when you are finished dragging columns, close the Data Sources dialog (View → Data Sources, or use the toolbar button)
9. save your letter template document (suggestion: same folder as spreadsheet, Example: “Letter - Easter.odt”

1. with your envelope or letter template open, File → Print
2. on the dialog asking if you want to print a form letter, click “Yes” (DO NOT CHECK THE WARNING BOX! See form letter warning section below if you messed this up)
3. if you are presented with a dialog asking if you want to print field names, click No
4. (optional - filtering) when presented with columns of your data, click on a cell that has a distinguishing characteristic of your choice, such as a cell with an “x” in an Easter column, or a specific code, and click the “AutoFilter” button
   • your records should be filtered based on each row having the same content that was in the cell you just filtered
   • if you make a mistake, you can click the “Remove Filter/Sort” button to reset everything
5. click Print
6. follow your normal print dialogs accordingly

If you made the B I G mistake of checking the “Do not show warning again” box when printing, you need do the following:

• close OpenOffice
• find your user preferences
• edit the file user/registry/data/org/openoffice/Office/Writer.xcu in a text editor, such as TextWrangler (Mac) or Crimson Text Editor (Windows)
• find the section with <prop oor:name=“AskForMerge”>, find the “false” text and change it to “true”
• save the file
• OpenOffice should now present you with this warning dialog again
• (the long explanation of this is below)

• original URL: http://openoffice.blogs.com/openoffice/2006/10/how_to_get_the_.html

If you've done mail merges before, you've seen this message.

What you should do, every time, is to click Yes and leave everything else alone. Then the mail merge will print with the contents of your data source.

What would make sense is to mark the Do Not Show Warning Again checkbox and click Yes, thinking that every time thereafter you'll be able to print the mailmerge correctly, with the contents of your data source, just like you did this time, but without that pesky message popping up.

You'd be logical, but you'd also be wrong because of the wacky design of the program. If you mark the checkmark, then from that day forward you will print, instead of a mail merge, a list of fields like <Firstname> and <Lastname>.

So: How do you get that dialog box to come back so you can print a mail merge correctly?

Answer: Here's how it's supposed to work. People say this works. I can't find a file with the relevant flag in it on my machine but if it works for you, great. It should work.

« Reader David Beroff offers some fine suggestions after his successful implementation, which I have updated the instructions with.»

1. Close OpenOffice.org.

2. Find the Writer.xcu file. It's in one of these locations.

~/.ooo-2.0-pre/user/registry/data/org/openoffice/Office/Writer.xcu

Documents and Settings\[users]\Application Data\OpenOffice.org2\ user\registry\data\org\openoffice\Office\Writer.xcu

3. Make a backup copy of it. Just copy the file in your file manager and paste it somewhere else.

4. Open it with an Ascii editor (i.e Notepad or 1stPage or some such program.)

5. Look for <prop oor:name=“AskForMerge”> and set the value to true. To do this, look for “AskForMerge=False” and type “True” where it says “False.”

6. Save the file.

Now you'll get the message popping up again when you print a mail merge. Leave the checkbox alone and click Yes, and you're golden.

As good as the above functionality is, it may not be enough to suite your needs. Perhaps you have address 1, and address 2, but you don't want a blank space to appear when no address 2 exists. Maybe you don't have an address to print out, and you want to hand deliver to certain people. These situations require advanced fields in OpenOffice.

If you don't want a line break after address 1, when no address 2 exists, you must add a “Hidden Paragraph” designator after your address 2 field.

1. move your cursor after the address 2 field
2. Insert → Fields → Other
3. click the Functions tab
4. click the “Hidden Paragraph” type
5. in the Condition Field, type the following:
   • (this is specific to our list, and yours may differ according to database name or file name)
   • ![Addresses0.Sheet1.Address 2]
   • ! means “if there is nothing in this field”, Addresses0 is our database name, Sheet1 is the sheet name, and Address 2 is the field (column)
   • if we don't have anything in Address 2, there won't be a blank line after Address 1
6. click insert
7. do the same AFTER any field you wish

You may want to hide things based on conditions. If you are hand delivering to certain recipients, you may not have an address in the file for them. In this case, you wouldn't want the comma to appear between the city and state.

1. DO NOT type a comma and space between the city and state fields
2. if you already have a comma and space, delete them so that the city and state fields are touching
3. move your cursor between the city and state
4. Insert → Fields → Other
5. click the Functions tab
6. click the “Hidden Text” type
7. in the Condition Field, type the following:
   • (this is specific to our list, and yours may differ according to database name or file name)
   • ![Addresses0.Sheet1.City]
   • ! means “if there is nothing in this field”, Addresses0 is our database name, Sheet1 is the sheet name, and City is the field (column)
   • if we don't have anything in the city, the comma and space will not print
8. click insert
9. do the same for any text you need to control visibility with