Basic Mail Merge Tasks in OpenOffice
Once again, I found myself in the position of having to scrounge together various bits of OpenOffice.org documentation which logically "should" all be together in one place... except they don't seem to be.
So, all I really want to do is use a "mail merge" function to print the address side of the postcards; no big deal, right? Except that there are four postcards on each 8½"x11" cardstock sheet.
I started by using the wizard (Tools > Mail Merge Wizard...) that comes with OpenOffice.org 3.4.1 by default, and find that others before me have come to a similar conclusion: It does fine with "typical" tasks, but poses some challenges if one wishes to customize things a bit.
One blogger reported that you can still get an earlier, much simpler version: Tools > Customize > Menus, Menu > Tools. Add > Documents, Mail Merge. Add, Close. Use the arrows to move it to, say, just before the (current) Mail Merge Wizard. I don't think that this helps my own needs much, but it was nice to learn how to add "hidden" functionality.
Let's assume that you have already created a spreadsheet with the mailing address data (or whatever) in an ODS (OpenOffice Calc spreadsheet) file, as well as the base document with the desired layout in an ODT (OpenOffice Writer) file. e.g., I split the page into a 2x2 table, with no borders, and placed my (fixed) return address in the upper left and a small picture in the lower left corner of each card. Hint: I anchored the picture to the return address with a fixed vertical offset, rather than risk it bouncing around depending on the number of lines of the destination address.
Associate the data
Select View > Data Sources. Oddly, I couldn't find an "Add" button directly, but right-click in the upper left pane. Apparently Registered Databases > New > Browse only lets you use ODB files, which I don't have. So, one way that I found on my own is: File > Wizards > Address Data Source > Address Book Type > Other external data source, Next. Settings. Spreadsheet, Next. Select the ODT file, Open. Finish. Field Assignment. Tie the column names to the pre-defined fields; I had to use User1 for the "extra" Addr2 line. {sigh} OK. Browse to place the new ODB file where you want it. Uncheck Make this available to all. Finish. Back to Registered Databases > New > Browse, select the ODB, OK, OK. Seems incredibly complex. I found a pointer to an article about using a spreadsheet as a "poor man's database" (p. 16 of the 55 pp. PDF) which suggests File > New > Database > Connect to an existing database > Spreadsheet. Next. Browse. Select your ODS file. Open. Next. Yes, register the database. Uncheck Open the database for editing. Finish. Place and name the new ODB file; you'll probably want to match the name (and directory) of the ODS file.
I'm absolutely kicking myself at this point, because I absolutely swear that I had successfully created this registered database once before, when I was experimenting in the last hour, without creating a named ODB file, but I just can't recall what my steps were anymore. :-( (Trust me, I've really been trying!) The only thing I recall was that there was a simple checkbox near the bottom of the file selection dialog box, labeled "Spreadsheets", and once I selected that, I was able to open the desired ODT file directly. Aargh! Anyway, moving on....
Updates: It's entirely possible that I was drawing data from one of my earlier experiments, which had apparently placed ODB files in my Documents folder, without OO actually advising me that this was happening. Also, as several articles point out, you can't edit your ODS and your ODT files at the same time, but that's really not a huge deal.
Lay out the fields in your base document
Insert > Fields > Other > Database > Mail merge fields, select the database, table and column (field), then Insert. Or you can simply left-click on the column name in the Data Sources pane and drag it to where you want in the document. :-)
Eliminating blank address lines
(i.e., Some records have a second address line or company name, and others don't.)
Insert > Field > Other > Functions > Hidden Paragraph, Condition: "not(Addr2)", Insert, Close.
(Source)
Update: Place this invisible field after the Addr2 line, just before the "real" paragraph break. It seems to act as a negative paragraph marker, i.e., cancel out the real break if there is not an Addr2 line. Double-negative logic, but incredibly useful.
Place multiple records on the same page
Insert > Fields > Other > Database > Next record, select database/table, Insert.
The next-record field won't be visible, but it can still be copied/pasted as needed. :-)
(Source)
Create a new file with the merged data
You can "print" the merge with File > Print. Answer "yes" to the form letter pop-up, and then you can select Output: File.
Except right now I'm getting nothing. :-( I need to put this down now and get some fresh air. Things should be more clear after a break.
(Source)
Updates: I still can't manage to output to a file with that method, but you can output to a (virtual) printer driver provided by the Foxit PDF Reader, and save the PDF file from there. Until I can figure out a cleaner way, I'm just going to use Tools > Mail Merge Wizard, and then jump immediately to step 8, (save/print/send), then Save merged document, Save as a single document, Save Documents, select file name and location, Finish. The filename will be modified slightly, but you'll still find it fine, at which point, you can edit it if desired, and then File > Export as PDF.
Another source: This article from the same author also covered some of the above points.
Update: OpenOffice continues to strike me as being quirky as h*ll, but I'll still take it over having to pay for Microsoft Office.
So, all I really want to do is use a "mail merge" function to print the address side of the postcards; no big deal, right? Except that there are four postcards on each 8½"x11" cardstock sheet.
I started by using the wizard (Tools > Mail Merge Wizard...) that comes with OpenOffice.org 3.4.1 by default, and find that others before me have come to a similar conclusion: It does fine with "typical" tasks, but poses some challenges if one wishes to customize things a bit.
One blogger reported that you can still get an earlier, much simpler version: Tools > Customize > Menus, Menu > Tools. Add > Documents, Mail Merge. Add, Close. Use the arrows to move it to, say, just before the (current) Mail Merge Wizard. I don't think that this helps my own needs much, but it was nice to learn how to add "hidden" functionality.
Let's assume that you have already created a spreadsheet with the mailing address data (or whatever) in an ODS (OpenOffice Calc spreadsheet) file, as well as the base document with the desired layout in an ODT (OpenOffice Writer) file. e.g., I split the page into a 2x2 table, with no borders, and placed my (fixed) return address in the upper left and a small picture in the lower left corner of each card. Hint: I anchored the picture to the return address with a fixed vertical offset, rather than risk it bouncing around depending on the number of lines of the destination address.
Associate the data
Select View > Data Sources. Oddly, I couldn't find an "Add" button directly, but right-click in the upper left pane. Apparently Registered Databases > New > Browse only lets you use ODB files, which I don't have. So, one way that I found on my own is: File > Wizards > Address Data Source > Address Book Type > Other external data source, Next. Settings. Spreadsheet, Next. Select the ODT file, Open. Finish. Field Assignment. Tie the column names to the pre-defined fields; I had to use User1 for the "extra" Addr2 line. {sigh} OK. Browse to place the new ODB file where you want it. Uncheck Make this available to all. Finish. Back to Registered Databases > New > Browse, select the ODB, OK, OK. Seems incredibly complex. I found a pointer to an article about using a spreadsheet as a "poor man's database" (p. 16 of the 55 pp. PDF) which suggests File > New > Database > Connect to an existing database > Spreadsheet. Next. Browse. Select your ODS file. Open. Next. Yes, register the database. Uncheck Open the database for editing. Finish. Place and name the new ODB file; you'll probably want to match the name (and directory) of the ODS file.
I'm absolutely kicking myself at this point, because I absolutely swear that I had successfully created this registered database once before, when I was experimenting in the last hour, without creating a named ODB file, but I just can't recall what my steps were anymore. :-( (Trust me, I've really been trying!) The only thing I recall was that there was a simple checkbox near the bottom of the file selection dialog box, labeled "Spreadsheets", and once I selected that, I was able to open the desired ODT file directly. Aargh! Anyway, moving on....
Updates: It's entirely possible that I was drawing data from one of my earlier experiments, which had apparently placed ODB files in my Documents folder, without OO actually advising me that this was happening. Also, as several articles point out, you can't edit your ODS and your ODT files at the same time, but that's really not a huge deal.
Lay out the fields in your base document
Insert > Fields > Other > Database > Mail merge fields, select the database, table and column (field), then Insert. Or you can simply left-click on the column name in the Data Sources pane and drag it to where you want in the document. :-)
Eliminating blank address lines
(i.e., Some records have a second address line or company name, and others don't.)
Insert > Field > Other > Functions > Hidden Paragraph, Condition: "not(Addr2)", Insert, Close.
(Source)
Update: Place this invisible field after the Addr2 line, just before the "real" paragraph break. It seems to act as a negative paragraph marker, i.e., cancel out the real break if there is not an Addr2 line. Double-negative logic, but incredibly useful.
Place multiple records on the same page
Insert > Fields > Other > Database > Next record, select database/table, Insert.
The next-record field won't be visible, but it can still be copied/pasted as needed. :-)
(Source)
Create a new file with the merged data
You can "print" the merge with File > Print. Answer "yes" to the form letter pop-up, and then you can select Output: File.
Except right now I'm getting nothing. :-( I need to put this down now and get some fresh air. Things should be more clear after a break.
(Source)
Updates: I still can't manage to output to a file with that method, but you can output to a (virtual) printer driver provided by the Foxit PDF Reader, and save the PDF file from there. Until I can figure out a cleaner way, I'm just going to use Tools > Mail Merge Wizard, and then jump immediately to step 8, (save/print/send), then Save merged document, Save as a single document, Save Documents, select file name and location, Finish. The filename will be modified slightly, but you'll still find it fine, at which point, you can edit it if desired, and then File > Export as PDF.
Another source: This article from the same author also covered some of the above points.
Update: OpenOffice continues to strike me as being quirky as h*ll, but I'll still take it over having to pay for Microsoft Office.