I have a excel spreadsheet of names and address I need to do a mail merge and I can't figure it out

Asked by Alison Bernazzani on 2010-09-22

I have an excel spreadsheet in openoffice with almost 200 names and addresses. I need to do a mail merge using AVERY LABELS
(Template 5160)
Could you please guide me through the process. The help tool didn't give me the proper directions.
Thank you.

Question information

English Edit question
Ubuntu openoffice.org Edit question
No assignee Edit question
Last query:
Last reply:
Tony Pursell (ajpursell) said : #1

Hi Alison

Firstly, OpenOffice.org handles labels separately from Mail Merge (which is only for letters). This does cause some confusion!

You start on labels with File > New > Labels which gives you a Labels dialogue where you can set up your labels and specify the format of the labels. On that dialogue you also specify a Database and a Table in the Database. That Database can be a spreadsheet and the Table can be a worksheet in the database. You can then select a field (assuming you have column headers) and insert it into the label text.

However, before you can use an Excel spreadsheet as a Database you need to register it as a OOo Database. To do this:

- File > New > Database
- Click on 'Connect to an existing database' and select 'Spreadsheet' from the drop down list. Click Next >>
- Browse to your Excel spreadsheet (I think it needs to be a .xls). Click Next >>
- Uncheck 'Open the database for editing'. Click Finish. You will be asked to save the Database definition file, You can give it the same name as your spreadsheet and OOo will add a .odb extension.

Now, back in the Labels dialogue, you will find it in the drop down list of Databases and be able to set up your label, When you have done that, click New Document. You will get what I call a Merge Master. Then do File > Print. and select Yes in the dialogue that asks you 'Do you want to print a form letter'. This gives you a Mail Merge dialogue where you can do all sorts of things. I will leave you to explore, but for now click on File for the Output, then OK. You will be asked to save a document containing the labels. I would also suggest your save the Merge Master document because you can open it and do File > Print any time you want these labels again.

Just one tip, before I finish. Print your labels on plain paper first and check the registration with the actual Avery labels sheet. I find the lines are too tight to the left and need to be spaced in a bit.

Hope that all helps


PS If this answer your question, pleas mark it as Solved

t (ctcases) said : #2

You are a genius! I found this question/answer after numerous searches and you were the only one who had the absolutely right answer ... thanks!

Tony Pursell (ajpursell) said : #3

Tony Pursell suggests this article as an answer to your question:
FAQ #1433: “How do I print Labels from data in a spreadsheet?”.

Can you help with this problem?

Provide an answer of your own, or ask Alison Bernazzani for more information if necessary.

To post a message you must log in.