Mail Merge OpenOffice Labels, OpenOffice.org 2.0 (Repost)

Here's how to do labels.

First, get your data in a spreadsheet, text file, address book, and create a database. You can do this by choosing File > New > Database. 

This post contains instructions for spreadsheets.

This post contains instructions in step 2 for spreadsheets or text files.

Then, once you've got the database set up in OpenOffice.org, you're ready to go.

1. Choose File > New > Labels. (To do Envelopes, open an OpenOffice.org Writer document, and choose Insert > Envelope.)

2. In the Labels tab of the Labels window, select the database  you created in the Database dropdown list. You're looking for the name of the database you created by choosing File > New > Database, not the spreadheet, addressbook, or text file containing the data.

(Click the picture to see a larger image.)

3. Select a table from the Table list. This will be Sheet1 or whatever the sheet name is, if you are using a spreadsheet to hold your data.

4. Select the first field you want to use from the Database Field list.

5. Click the arrow next to it to insert it in the Label Text field.

Type a space after the field and you can add the next field, such as LastName.

6. Use the Database Field list to insert any other fields you need. If you want fields on the second line, click after the last character of the last field you inserted, in the big text box, and just press Enter. If you need to change the arrangement later in the created label document, you can.

7. In the Brand dropdown list, select Avery Letter Size if you're not using A4.

8. In the Type dropdown list, scroll through the billions of labels. Select the type of label you're using, 8160 Inkjet Address is a good one but just use whatever is on the envelope of labels.

9. This step and step 10 , and step 15, are optional but recommended. Click the Options tab.

10. Select the Synchronize Contents option IF you want to apply formatting, like a different font or colors or adding graphics, and make those changes apply to all of your labels.

11.Click the Setup button next to the printer display.

12. Select the printer you want to print to.

13. In the printer options (this will vary according to your operating system) specify the appropriate paper feed or tray. For now, select Manual Feed.

14. Click the New Document button at the bottom of the window. The labels will appear. This is how it's supposed to look. You won't see the data, you'll see the field names like first name. When you print, the correct data will appear.

15. OPTIONAL: IF YOU SELECTED THE SYNCHRONIZE CONTENTS CHECKBOX

If you need to make changes, like adding spaces, rearranging fields, or changing formatting, do so in the upper left label. Make the text an interesting font, or make it the size you need. You can also right-click on the border of the upper right label, choose Frame, go to the Border tab, and give it a background color.

Then click Synchronize to apply those changes to all labels.

16. Choose File > Print. A message will ask if you want to print a form letter. Click Yes.

17. If you want to print labels for only certain records, you can select them in the scrolling list of records. Select one, press Ctrl, select the next, and so on. Or you can select a range of records like 1-20.

18. If you want to just print all the labels, choose All.

19. When you're ready, just click OK. You'll be prompted again to choose your printer. Print normally.

Note: If you have any problems printing, check your printer setup using your operating system setup tools.

Note: You can save the label document and just go back to it again when you need to use it again.

---

---

Comments

[Robert Jung]

ok, I now get how to do labels. And I think I understand the link to the database. But, what I don't get is the how to sync the data, which is on a spreadsheet, into the database. I can initially create that link. But let's say, I need to modify the spreadsheet, keeping all of the fields. Just the data that I'm merging has changed. How do I tell the data source to sync up to the latest version of the spreadsheet. It seems that it should be automatic, given the connection, but I don't see it updating.

thanks,
Rob

[ben dalton]

Rob,

I created a database (from File>New...) from a spread sheet I have already filled with info.

Like you say, I didn't think it was updating when I made changes. But when I closed the database, and then re-opened it, and double clicked on one of the tables (e.g. "Sheet1"), the changes had gone through.

If this isn't working for you, try right clicking on a table in your database (e.g. "Sheet1") and choose Database > Connection Type. Then 'Spreadsheet' from the drop down menu. Then select your spread sheet, and click 'Test Connection'.

[Steve Kinney]

Great start - it all works as advertised. Only one little problem here: It seems impossible to get Open Office to populate more than one page (sheet of labels) at a time. For instance, if you are printing 300 addresses on labels that come 6 per page, it seems necessary to re-populate the ONE page 50 times by hand to do it.

I must be missing something. My co-worker in the cubible next door, who originally found the problem, must be missing the same thing. Any clues? (Open Office 2.1 on WinXP.)

[Dr Phil]

Been struggling with the "only one sheet" problem my self. THe trick is in this bit:

>>Choose File > Print.
>>A message will ask if you want to print a form letter. Click Yes.

I don't think it will play if you just hit the "print it now" button...

[deedhoet]

One problem:
It's not vertically centered. How? How to delete the second row too? It's useless!

And I think it will be much better if we can see the record value than seeing

,
As if we click the "Data to Fields" button from Data Source View. It's disabled if I dont do the 15th step. Is it bug or something else?

I'm using OOo v 2.0 for Windows.

[deedhoet]

Another question: how to show the "Mail Merge" window? Mine isn't looking like that, there is no "Record" and "Output" dialog.

Thx in advance.

[Ravan Asteris]

I figured this part out already, BUT
A) How do you get it to do other than default fonts without having to twiddle every label? and
B) How do you get it to put the Post Office bar code on the label. If you're bulk mailing, this is importanr!

[Adam]

This step in the process does not occur for me:

>>Choose File > Print.

>>A message will ask if you want to print a form letter. Click Yes.

Not that it does not work, I never get the message. I choose "print" from the file menu. The "print" dialog box appears, I click print, and it prints (and not the information I desire either).

I am running OO 2.0, have even reinstalled it, and nothing. I have spent hours upon hours trying to get it to print 700+ labels from a DB. I cannot find anything related to a "form letter" when dealing with labels in Writer.

My DB is fine, it is connected to the open document in Writer and all the other steps go smoothly other than:

>>A message will ask if you want to print a form letter. Click Yes.

Which as said before, does not happen.

Any help would be greatly appreciated, as I am going crazy trying to get this to work, and have no other program to print labels :(

[Solveig]

Hi Adam,

See this blog entry.
http://openoffice.blogs.com/openoffice/2006/10/how_to_get_the_.html

I couldn't find the file but it seems to work for everyone else.

Solveig

[adam]

Thanks SOOOO much Solveig! You saved me :) That blog entry covered my problem. The config for the message pop-up was set to "false". Changed it to "true", saved the file and was in business. Thanks again!!

[Solveig]

Glad it worked! That's one of the settings that I think should be under Tools > Options, at least, or off of the Print Options button, not buried deep in the bowels of the XML.

Solveig

[Arthur]

All my address dbs are as tables in Word (having installed Open Office only recently when I upgraded to Vista). Is there a way I can get a link to them to produce labels -or do I have the chore of transferring all the information to Open Office? CsZ4u2

[Solveig]

Hi Arthur.

Open the .doc files in Writer. Copy the table contents, create a new spreadsheet, and paste. Then create a database based on that new spreadsheet, and you can connect to that database to create labels.

http://openoffice.blogs.com/openoffice/2007/01/mail_merge_in_o.html

Solveig

[Arthur (again!)]

Solveig - many thanks. I created the spreadsheet datablase with ease, but trying to register it produces the message that the file does not exist. What am I doing wrong?

[Solveig]

Hi Arthur,

Just be sure the spreadsheet is the right format (spreadsheet not word), that the data is headings in the first row and data in the 2nd and subsequent rows, and be sure the file is closed.

By trying to register, you mean saving in the final window of the File > New > Database wizard?

solveig

[Arthur (yet again!)]

Solveig - The spreadsheet database is filed in OpenOffice.org Calc and formatted the way you describe. I used 'HELP Registering and Deleting a Database' to register the file so that I could access its contents for producing labels (but I can't).

Another conundrum: If I enter an Avery Label type (8160) should it automatically format?

Thanks again

[Suse]

Solveigh, thanks for your helpful page!

Now I've got another problem: I want to print labels using 436 records, so I check all records in the dialog box, but OO decides to leave out records (always the same) and I end up with only 156 labels (13 sheets with 12 labels each). I also tried 1 to 436 records in the dialog box - the result is the same.

Any idea? Writing all the adress labels for the shipping of the christmas parcels wouldn't be so funny *sheepish smile*

[Suse]

Found the solution: the field "next record" was inserted more than once!

[Solveig]

HOW many times have I done that? Yep, that's a stickler. I have modified my approach so that I make the font for that tiny tiny gray box nice and big, so it's easier to see doubles.

Glad you found the solution!

Solveig

[Arthur (yet again!)]

Solveig - any suggestions for Arthur (yet again!) message of November 14. Like Suse, I do not relish the prospect of writing out 100s of Christmas address labels.

[Solveig]

Hi Arthur,

I'm not sure what you mean by automatically format, I'm afraid.

I think there must be something wrong with the spreadsheet. Copy and paste the content into a news spreadsheet and save it in .ods, then create a new database, be sure to specify that it's of type spreadsheet, and leave the checkbox in the last window, saying you want to register the database, marked. That should work.

Solveig

[Arthur (yet again!)]

S - I appreciate - and admire - your patience! I have again done as you said, but in 'Labels' I still cannot get Sheet 1 to show in the Table window, nor the address fields in Database. In 'Database' after creating the neww database where do I specify it is a spreadhseet, as I cannot see such an option in the the Database Wizard?

Arthur

[Ian Davidson]

Done all that, labels printing OK from databse file.
Is there a quick and easy way to get it not to print empty fields?
thanks,
ian

[Ian Davidson]

Done all that, labels printing OK from databse file.
Is there a quick and easy way to get it not to print empty fields?
thanks,
ian

[Solveig]

Hi Ian,

Yep!

http://openoffice.blogs.com/openoffice/2007/05/suppressing_bla.html

Solveig

[JIm]

This doesn't work. I make labels, it sees the fields, I print to a file, and it prints 28 copies of the same label. Also, I can't just select a bunch of labels to format at once. If I actually send the labels to the printer, it does run them through a file, one sheet at a time, so I only have the last one. At that time, the label is read-only, so I can't change it. At this point, I would gladly pay 50$ for a copy of Microsoft word, if such a thing would run un linux. Creating labels there is easy.

[Judy]

I've spent hours today trying to format the address labels for avery 5160! It doesn't seem to matter what I put in it won't print correctly. Also it is not showing all of my tables and it won't delete the one I was using! Aghhhh!

[sal m]

Hi and thank you so much for your site, I learned a lot. I would like to see how to prevent only one address from printing per the page (over and over). thanks in advance.

[Siew Wei]

Thanks!

I have tried to print label but without success. Finally I got it right this time.

[Gail]

I have tried and tried to get the label info to come up... did all the things suggested and still get "First, Last, Address, City, St, Zip" showing on the sheet for printing

HELP!!!

[Brad]

Hello,
I appreciate your posting so much helpful information. I just wish it worked. Sounds so good, however, I've notice that a good number of other people are not able to get Open Office to work either. I have struggled of days trying to simply print mailing labels from, spreadsheet, database, text document, and more. Does not seem possible!
Looks like I will need to splurge this weekend on Microsoft Office 2007 in order
to print some labels. I would have liked to support the idea of a free and open program, but once again, this seems more like a wish than a reality. Simply can't afford the time to work with programs that don't work.

[Solveig]

See also this video.

http://openoffice.blogs.com/openoffice/2007/11/rough-draft-vid.html

[Phil]

Solveig
this is a great instruction well done, helped me a lot as a newcomer on OOo label-printing! Many thanks for your help.
Phil

[jz]

I can't get the data to show when I go to print it doesn't ask me if to print a form letter please respond

[dan wait]

Thank you for this - a great help!

[dan wait]

Thank you for this - a great help!

[Brendan]

Thanks for this handy how-to. I spent quite a while looking around for one this succinct.

[Sarah Kunze]

I have followed the directions, but I get one solid sheet of one address, not a sheet of all records. All records are selected, but entire sheet is Jeff Jones, help?

[Brad Brown]

Been trying to 2 years to merge labels now!
Never works!
The closest I have gotten is to get to where it shows the description of the fields as in the window above and where you indicate that is how it should be. NOT SHOWING THE ACTUAL ADDRESSES! Then I follow through with the rest of the steps and print and only get a print out of the fields without the data.

Have wasted hundreds of dollars worth of ink and paper and worked with an instructor at a local community college where they use the Open Office. He has agreed that they too will quit using it if this doesn't work.

Any money saved over the cost of Microsoft is lost in wasted time and supplies. Since most people are familiar with Microsoft, this is an unnecessary wast of time that can be use for course work.

I have tried to work with this program and the tech support and it seems to be fruitless. I will show the correspondence and information to people to let them know of the frustration working with this program before the decide to purchase their computers. This way they will be able to get the package with Microsoft Office included and save hours of headaches and disappointments.

[Ray Barner]

I have worked on printing labels for days and can't get it to work. Every thing goes well until I hit print. Then I get the form letter question and answer yes. It tell me the data source can't be found. I try to select the right source but it never works. I have reinstalled twice still the same. The data source it refers to have been deleted long ago.

Any one else? Any solution.

[Bruce Thompson]

Thanks Solveig, it works perfectly!

I'm using OpenOffice.org 3.0 on Vector Linux 5.9 with Avery A4 J8160 mailing labels, 21 per sheet.

Just to be sure, I first printed a test page (on scrap paper) and that was good but the formatting was not as I wanted it. To correct the formatting I went back to Step 1 and created a new "Label" file and used the "Format" tab at step 9. I then saved the format for next time.

By the third sheet of scrap paper, everything was as I wanted it and the actual label printing went without a hitch.

When I printed, I only printed one page at a time by selecting "From" (under "Records") "1 to 21" and "22 to 42" etc, and manually fed the Avery sheets in. I felt uneasy with selecting "All" records.

I made the spreadsheet containing the data, with the handy DataForm extension. It makes things so easy. Like I have done with this tutorial, I followed your instructions step-by-step (from the first link at the top of the page "instructions for spreadsheets). A couple of times I tried to second-guess your instructions but you were always right.

My advice to those who are having problems: make sure you are following the instructions exactly.

[Solveig]

Hi Bruce,

I'm glad it worked! The tweaking can be a bit of a pain and you're right to be cautious with the printing at first.

I'll check out the Data Form extension.

Solveig

[Adam McFarland]

Hi Solveig -

Just wanted to thank you for this post. I am a long-time MS user who recently switched to OO and started to get frustrated trying to do a mail-merge since the interface was a bit different. Googled it, found your site, and now I've got my labels and can move on to more important stuff :)

All the best,

Adam

[Solveig]

Hi Adam,

Glad it worked! It's not obvious, but I find it quite reliable.

Solveig

[actual test]

Very Nice,Thanks for sharing

[Don]

Hello,

I done the the above about 10 times now, and I get 30 pages of labels, instead of 30 different contacts labels on one page. What am I missing?

Thanks,
Don

[Karan]

Thanks you thank you thank you... It is midnight and I dont know how many more hours I would have wasted had it not been for this tutorial.

[Freda Greene]

OMG!!!! Thank-you Solveig!! You are a genius...I have been working on this for HOURS. The sychronize button was the trick! This stuff is so complicated, and I surely thank-you. I have my labels and now I am ready to go on to my next project. Thank-you, thank-you...Freda

[R Monteiro]

Thanks for your tutorial. In Open Office 3.1 everything works but I cannot change the fonts or print size!Can you help?

[Beth]

I'm sorry we didn't read your blog earlier. I wish we read this page four hours ago. Thank you, thank you, thank you!