To all Excel/Word Boffins!
Thread Starter
Join Date: Jan 2006
Location: United Kingdom
Age: 42
Posts: 138
Likes: 0
Received 0 Likes
on
0 Posts
To all Excel/Word Boffins!
Hi Guys,
I need a bit of help with Office 2007, ok when I say "a bit" I acually mean a lot!
Heres what I want it to do:
I want to have a spreadsheet in excel with names and addresses of candidates on it, and then I want to use mail merg(?) to take that info and move it across to word and have it set up on labels ready to print off...now I seem to have a few issues with this, the main one being that I can't do it!
Can anyone help me out on this?
Also how should I set it up in Excel, should I have separate columns for Address line 1, address line 2 etc?
Any help would be greatly appreciated!
Many thanks!
Ant
I need a bit of help with Office 2007, ok when I say "a bit" I acually mean a lot!
Heres what I want it to do:
I want to have a spreadsheet in excel with names and addresses of candidates on it, and then I want to use mail merg(?) to take that info and move it across to word and have it set up on labels ready to print off...now I seem to have a few issues with this, the main one being that I can't do it!
Can anyone help me out on this?
Also how should I set it up in Excel, should I have separate columns for Address line 1, address line 2 etc?
Any help would be greatly appreciated!
Many thanks!
Ant
Join Date: Aug 2005
Location: Guadalix de la Sierra
Age: 57
Posts: 59
Likes: 0
Received 0 Likes
on
0 Posts
If you have it, and have used it at all, I would really recommend using Access, not excel for the data storage part of what you are doing.
I'm not sure how to do it with Excel (which is partly why I'm telling you to try with Access
) but it is fundamentally a spreadsheet, and data storage is more suited to databases.
I'm not sure how to do it with Excel (which is partly why I'm telling you to try with Access
) but it is fundamentally a spreadsheet, and data storage is more suited to databases.
Spoon PPRuNerist & Mad Inistrator
While I agree with Jamongris about Access, especially for large quantities of data (I have several such), what I suggest you do is copy the spreadsheet data into a Word Table, such that the data follows the following format (for example):
Title FirstName LastName Address1 Address2 City PostalCode Country
All the above will be column headings, and each row will contain the applicable data for each record.
This is the "data source" in Mail Merge speak.
Now create the Merge Document - this is the document that will layout the labels, import the data and print the sheets of labels.
You need to be careful to ensure that the label sizes and spacing matches the labels you actually have. DON'T leave double-sided printing enabled!
Next, set up the label fields in the first label on the sheet -
«Title» «FirstName» «LastName»
«Address1»
«Address2»
«City»
«PostalCode»
«Country»
Note that these must match precisely the column headings above (this is automatic, if you are doing it right, as you are selecting from the data source).
The mail merge wizard will now populate the rest of the labels - note that «Next Record» now appears at the start of each subsequent label.
Play around with the position and formatting to ensure that the labels fit the addresses over the whole sheet - you can print to ordinary paper to verify.
Now perform the mail merge to pull the data from the source into the merge file - merge to a new document and save it (you can modify the labels individually before printing). Again, it's worth printing on plain paper to verify before wasting labels!
Should be lots of help and assistance in Word Help and on the MS site.
SD
Title FirstName LastName Address1 Address2 City PostalCode Country
All the above will be column headings, and each row will contain the applicable data for each record.
This is the "data source" in Mail Merge speak.
Now create the Merge Document - this is the document that will layout the labels, import the data and print the sheets of labels.
You need to be careful to ensure that the label sizes and spacing matches the labels you actually have. DON'T leave double-sided printing enabled!
Next, set up the label fields in the first label on the sheet -
«Title» «FirstName» «LastName»
«Address1»
«Address2»
«City»
«PostalCode»
«Country»
Note that these must match precisely the column headings above (this is automatic, if you are doing it right, as you are selecting from the data source).
The mail merge wizard will now populate the rest of the labels - note that «Next Record» now appears at the start of each subsequent label.
Play around with the position and formatting to ensure that the labels fit the addresses over the whole sheet - you can print to ordinary paper to verify.
Now perform the mail merge to pull the data from the source into the merge file - merge to a new document and save it (you can modify the labels individually before printing). Again, it's worth printing on plain paper to verify before wasting labels!
Should be lots of help and assistance in Word Help and on the MS site.
SD
Agree with you, SD
Altho' I have Office, I don't speak Access - too old / too lazy / too stupid to learn.
So I use exactly the same process you suggest & it works. But on some labels I find (in Word 2003) that it's a bit iffy in trying to print half-labels at the bottom of one page & the next at the top of the next. So I agree: trial print one page on plain paper. (and prob. a print preview for the rest, just to check.)
Altho' I have Office, I don't speak Access - too old / too lazy / too stupid to learn.
So I use exactly the same process you suggest & it works. But on some labels I find (in Word 2003) that it's a bit iffy in trying to print half-labels at the bottom of one page & the next at the top of the next. So I agree: trial print one page on plain paper. (and prob. a print preview for the rest, just to check.)
Join Date: Apr 2000
Location: West Midlands, UK.
Age: 73
Posts: 294
Likes: 0
Received 0 Likes
on
0 Posts
By creating a mail merge in Word, and then linking to a list in Excel, you can turn worksheet data such as the addresses of your customers into printable mailing labels.
In Microsoft Excel, set up the data to use in the mail merge.
Make sure the data is in list (list: A series of rows that contains related data or a series of rows that you designate to function as a datasheet by using the Create List command.) format: each column has a label in the first row and contains similar facts, and there are no blank rows or columns within the list. You'll use the column labels to specify what data goes where in the mail merge.
Make sure the column labels clearly identify the type of data in the column; this helps you select the right data as you construct the mail merge. For example, labels such as First Name, Last Name, Address, and City are better than Column 1, Column 2, Column 3, and Column 4.
Make sure you have a separate column for each element that you want to include in the mail merge. For example, if you're creating form letters and want to use each recipient's first name in the body of the letter, make sure you have a column that contains first names, rather than a single column with both first and last names.
If you want to refer to each recipient by title and last name, such as Mr. Smith, you can use a single column containing both titles and last names, or use one column for title and another for last name.
To make the list easy to find when you select it for the mail merge, give it a name (name: A word or string of characters that represents a cell, range of cells, formula, or constant value. Use easy-to-understand names, such as Products, to refer to hard to understand ranges, such as Sales!C20:C30.):
Select the entire list.
On the Insert menu, point to Name, and then click Define.
Type a name for the list.
Click Add, and then click Close.
Save and close the workbook that contains the data.
Switch to Microsoft Word.
On the Tools menu, point to Letters and Mailings, and then click Mail Merge Wizard.
In the first two steps of the Mail Merge Wizard, select the document type and start the document. If you're creating mailing labels, click Labels in Step 1, and then click Label options in Step 2 to select the size and type of labels to print.
In the third step, under Select recipients, click Use an existing list, and then click Browse.
In the Look in list, click the folder in which you saved the workbook with your data, click the workbook, and then click Open.
In the Select Table dialog box, locate and click your list. Make sure the First row of data contains column headers check box is selected, and then click OK.
In the Mail Merge Recipients dialog box, click any column labels in your data that correspond to the Word identifiers on the left. This step makes inserting your data in the form documents easier. For more information about matching fields, see Word Help.
If you want to include only selected recipients in the mail merge, click Edit recipient list and select the recipients you want.
Use the rest of the wizard steps to write, add recipient information, preview, personalize, save, and print or e-mail your documents. For information about these steps, see Word Help.
Regards
Cron
In Microsoft Excel, set up the data to use in the mail merge.
Make sure the data is in list (list: A series of rows that contains related data or a series of rows that you designate to function as a datasheet by using the Create List command.) format: each column has a label in the first row and contains similar facts, and there are no blank rows or columns within the list. You'll use the column labels to specify what data goes where in the mail merge.
Make sure the column labels clearly identify the type of data in the column; this helps you select the right data as you construct the mail merge. For example, labels such as First Name, Last Name, Address, and City are better than Column 1, Column 2, Column 3, and Column 4.
Make sure you have a separate column for each element that you want to include in the mail merge. For example, if you're creating form letters and want to use each recipient's first name in the body of the letter, make sure you have a column that contains first names, rather than a single column with both first and last names.
If you want to refer to each recipient by title and last name, such as Mr. Smith, you can use a single column containing both titles and last names, or use one column for title and another for last name.
To make the list easy to find when you select it for the mail merge, give it a name (name: A word or string of characters that represents a cell, range of cells, formula, or constant value. Use easy-to-understand names, such as Products, to refer to hard to understand ranges, such as Sales!C20:C30.):
Select the entire list.
On the Insert menu, point to Name, and then click Define.
Type a name for the list.
Click Add, and then click Close.
Save and close the workbook that contains the data.
Switch to Microsoft Word.
On the Tools menu, point to Letters and Mailings, and then click Mail Merge Wizard.
In the first two steps of the Mail Merge Wizard, select the document type and start the document. If you're creating mailing labels, click Labels in Step 1, and then click Label options in Step 2 to select the size and type of labels to print.
In the third step, under Select recipients, click Use an existing list, and then click Browse.
In the Look in list, click the folder in which you saved the workbook with your data, click the workbook, and then click Open.
In the Select Table dialog box, locate and click your list. Make sure the First row of data contains column headers check box is selected, and then click OK.
In the Mail Merge Recipients dialog box, click any column labels in your data that correspond to the Word identifiers on the left. This step makes inserting your data in the form documents easier. For more information about matching fields, see Word Help.
If you want to include only selected recipients in the mail merge, click Edit recipient list and select the recipients you want.
Use the rest of the wizard steps to write, add recipient information, preview, personalize, save, and print or e-mail your documents. For information about these steps, see Word Help.
Regards
Cron
Join Date: Jan 2008
Location: Australia
Posts: 159
Likes: 0
Received 0 Likes
on
0 Posts
One advantage I have found for using Excel as my mailing database is tat it is easy to sort data in Excel. So, if I want to do on eletter to A through F, and another G through n etc - easy to sirt alphabetically and do separate mailings.
Another reason - easy to add fields (columns) like male/female married/single etc and then sort on these fields. So married guys cane easily be sorted for one mailing, single females for another, etc.
It is very important to have separate fields for each item of information, such as: unit no, building name, street no, street name, suburb, post code, state etc etc.
Another reason - easy to add fields (columns) like male/female married/single etc and then sort on these fields. So married guys cane easily be sorted for one mailing, single females for another, etc.
It is very important to have separate fields for each item of information, such as: unit no, building name, street no, street name, suburb, post code, state etc etc.
