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”

if you have any trouble getting your printer to cooperate, you can also try making a letter size template and positioning the fields in the right place for your printer; typically this would be to make a landscape oriented letter size document

it may be easier to save the document to a pdf instead of actually printing it so you can send the pages you want to the printer as many times as you need in case of feed issues, etc…

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 BIG 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: How to Get the "Do You Want to Print a Form Letter?" Message Back for OpenOffice Mail Merges

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