Excel can be used to supply the data to mailmerge in MS Word.  I found this a little tricky first time but have done this from both XL95 and XL2000.

Excel makes a nice table column for name, address, city, zip state, zip code.  Excel does not give you reformatting options to create another sheet using the information, you would have to write a macro. Word will convert your table to labels without any problem and maintain the label boundaries; therefore, Excel by itself is not suited to the task of creating labels, but Excel with Mail Merge in Word works just fine.

Directions for preparing data in Excel to print Labels

Column headings are required in row 1 in Excel for field identification in Mail Merge.  Failure to have column headings will show up in Word as AutoMergeField, AutoMergeField1, AutoMergeField2 etc.  They only go up to AutoMergeField4 so failure to provide headings will limit you to the first 5 columns.

If you are using XL2000, move the tab for the sheet to the far left and create a name for the data:
  i.e.  select  A1:G50  then supply a name (ziplabels)  in the name box to left of formula bar. This will work in prior versions also. But could not simply select a sheet in XL2000 as I could in XL95.  Actually the named range works better anyway in all versions of Excel.  Named ranges are known and available to the entire workbook.

The three lines in your address must represent 3 cells in a row in your Excel file,  and should begin with the first in Column A.   Though material in Word appears that you can have all kinds of fields, I wasn't able to select them.  If you use a Named area you have access to all your fields and can ignore this paragraph.

Format the entire Excel sheet as text so that zipcodes will be text no matter where used.   Failure to do this will result in 5 digit numbers being zero suppressed and treated as numbers which sort before text entries of 9 digits with a hypen.  This will facilitate use of  SepLastTerm  macro should you need to split out zip codes for sorting.  You may not be able to do this if some of your zipcodes are actually entered as numbers of less than five digits.
 
 

	A	B	C	D
1	Addressee	Address1	City, State zip	zipcode
2	Jean Daley	390 Waterdown Rd	Two Horses, WY 12345-1234	12345-1111
3	John Lyndhurst	1441 W Drive	Any Town, ND 14532	14532

The above is just an example you can use any fields you want but I think mail appears to allow only a limited number of fields so you want to have your information begin in Column A or close to it.

Directions for Word to create mailing labels

Start with a blank Word document

If you redo the mail merges reply to allow reuse of current document wiping out previous data.  Actually if I do redo it, I find it easier to use Edit --> Select All, and then Delete.

File --> Save As --> (supply path and file for permanent file)

Tools --> Mail Merge

1. Create --> Mailing Labels --> Active Window  or Change Document type
2. Data Source --> Get Data --> Open Data Source --> (i.e. c:\temp\xyzfile.xls)
    Entire Spreadsheet, Filtered Spreadsheet (or Named area
    a Filtered Spreadsheet treats what you see after filtering as the content
i.e Sheet19 or ziplabels)
    Set up Main Document
    Laser/Avery Standard/5261 Address  ( 2 up - 1" x 4"  -- H x W)
    Insert Merge Field  (hit return key between fields, when necessary for formatting)
       <<Addressee>>
       <<Address1>>
       <<city, state zip>>
   Insert US Postal Bar Code, 
    Mail Merge document is not empty,
    Continuing will replace all text in the document with new text.   [OK]

Inserting US Postal Bar Code will find zipcode using either  city,state zip line or  zipcode column.  (In XL2000 you select the field.)

Don't be concerned that zipcode will look like 012344321 without any hyphens or that you see Delivery point US bar code will print here until merged. )

Some people with XL97 report that mail-merge doesn't find your spreadsheet unless it is the first tab in the workbook.  They're also the ones that say when you come to select a data source, you must select 'Microsoft Excel :Workbooks' in the bottom of the selection dialog and all will be well.  I think this only applies only if you've also used Access as a database in the past.

Avery® web pages of interest.  Avery label numbers are used in MS Word.  For templates go to the download area then to templates.  (Guess what -- a Microsoft site) i.e. (changed) http://officeupdate.microsoft.com/templategallery/default.asp  The following are examples of what may have been available six months earlier at Avery.

• (dead) Technical Bulletins
• (dead) Dimensions of Avery Products Shows all dimensions by number but does not name the labels.  (dead) Dimensions of Avery Products -- still can't find it try Google
• (changed) aOn line Catalog Search for labels
• (dead) Using Avery® Labels with Microsoft® Word for WindowsTM Versions 2.0 & 6.0 and Word for Macintosh Version 6.0
• (new) Avery® Label Wizard for MS Word 2000

Summary of Most Commonly used Labels

The following listings are some of the selections from MS Word for laser labels mainly on 8 1/2 by 11 sheets.  A more complete list can be found at Dimensions of Avery Products most of which are probably defined in MS Word labels.  ( List of Avery labels. )

Width	Hgt	Type	Across	Down	Labels /sheet	Avery  #	Avery #	Avery #	Avery #	Avery #	Avery #	Side Margin	Top Margin	Horiz Pitch	Vert Pitch
2.63"	1"	Address	3	10	30	5160	5260		6460	8160	8660	0.19"	0.5"	2.75"	1"
4	1	Address	2	10	20	5161	5261			8161		0.16	0.5	4.19	1
4	1.33	Address	2	7	14	5162	5262			8162	8662	0.16	0.83	4.19	1.33
4	2	Shipping	2	5	10	5163				8163	8663	0.16	0.5	4.19	2
4	3.33	Shipping	2	3	6	5164			6464	8164		0.16	0.5	4.19	3.33
8.5	11	Full Sheet	1	1	1	5165			6465	8165		0	0	N/A	N/A
3.44	0.67	File Folder	2	15	30		5266	5366	6466	8166		0.53	0.5	4	0.67
1.75	0.5	Return Address	4	20	80		5267		6467	8167		0.28	0.5	2.06	0.5

Width	Hgt	Type	Across	Down	Labels /sheet	Avery  #	Avery #	Avery #	Avery #	Avery #	Avery #	Side Margin	Top Margin	Horiz Pitch	Vert Pitch
2.75	2.75	Diskette (3.5)	3	3	9	5196				8196		0.13	0.5	2.75	3
4	1.5	Diskette	2	2	4	5197						0.16	1	4.19	1.5
3.5	1.67	Audio Tape	2	2	4	5198						0.5	0.5	4	1.67
3.06	1.83	Video Face	2	5	10	5199-F						1.07	0.92	3.3	1.83
5.81	0.67	Video Spline	1	15	15	5199-S						1.34	0.5	N/A	0.67
2.83"	1"	Address	3	10	30			5660				0	0.5	2.83	1
4.25	1	Address	2	10	20			5661				0	0.5	4.25	1
4.25	1.33	Address	2	7	14			5662				0	0.84	4.25	1.33
4.25	2	Shipping	2	5	10			5663				0	0.5	4.25	2
4.25	3.33	Shipping	2	3	6			5664				0	0.5	4.25	3.33
1.75	0.5	Return Address	4	20	80			5667				0.3	0.5	2.05	0.5

A more complete list of Avery labels.

US Postal Cards

For Single postcards I have printed on 4x6 file card stock.

For double cards, You can specify your own custom form:
The 3611 Avery prints on 8 1/2 x 11 stock. You can make up your own custom label as follows:

Tools, Labels and Envelopes, Options, 3611 Postcard, now choose   New Label button on right so that you start with the 3611 dimensions, and change to your own requirement probably changing the vertical pitch from 4-1/2 inches to 4-1/4 inches since you will be doing your own cutting.

Finishing Touches

Preparation of zip codes, if using Excel

If your data is already in Excel and some the zipcodes appear as less than 5 digits even when looking at the cell on the formula bar, you can apply the zipcode formatting under Format--> Cells, which is actually a format of 00000 Anything else already there or entered such as 00123-1234 will be treated as text because it is not a number, so not to worry.  Having a mixture of text and number zipcodes will affect sorting by zipcode though.

For readability in Excel, I would align the entire zipcode column left aligned.  This will not affect Mail Merge using Word.

Including a picture on the label

Now you should be able to grab your picture and move it anywhere on your label and your text will wrap the way you want it to.

Address Book from Outlook Express

Name and Address Listing with MAP Reference

Printing Multiple Labels based on Column A

The following macro will repeat rows based on number in Column A, which would be needed by Mail Merge to print multiple labels with same content.

 

Sub RepeatRowsOnColumnA() 'Prepare multiple rows for Mail Merge labels ' based on number of labels in column A 'David McRitchie, programming, 2001-09-20 ActiveSheet.Copy Before:=ActiveSheet Application.ScreenUpdating = False Dim vRows As Long, v As Long On Error Resume Next Dim ir As Long, mrows As Long, lastcell As Range Set lastcell = Cells.SpecialCells(xlLastCell) mrows = lastcell.Row For ir = mrows To 2 Step -1 If Not IsNumeric(Cells(ir, 1)) Then Cells(ir, 1).EntireRow.Delete ElseIf Cells(ir, 1).Value > 1 Then v = Cells(ir, 1).Value - 1 Rows(ir + 1).Resize(v).Insert Shift:=xlDown Rows(ir).EntireRow.AutoFill Rows(ir). _ EntireRow.Resize(rowsize:=v + 1), xlFillCopy 'Rows(ir).EntireRow.Interior.ColorIndex = 36 ElseIf Cells(ir, 1).Value < 1 Then Cells(ir, 1).EntireRow.Delete End If Next ir Application.ScreenUpdating = True End Sub See Nick Hodge's modification 2001-09-23 of the above to run from MS Word, creating Word input by creating a separate Excel workbook then using it.

A B C 1 1 B1 AA 2 2 B2 BB 3 a B3 drop 4 4 B4 CC 5 2 B2 DD 6 1 B8 EE 7 3 B9 FF 8 1 B10 GG

A B C 1 1 B1 AA 2 2 B2 BB 3 2 B2 BB 4 4 B4 CC 5 4 B4 CC 6 4 B4 CC 7 4 B4 CC 8 2 B2 DD 9 2 B2 DD 10 1 B8 EE 11 3 B9 FF 12 3 B9 FF 13 3 B9 FF 14 1 B10 GG

Printing an Excel Form, Multiple times

The following reply was posted by Tom Ogilvy (2001-10-07, programming), to print an Excel sheet many times changing the name, and some other content.  This example shows changing one of the cells in the pattern for each cell in Column A of the sheet with the names to be inputted.

1. Assume list of names is on Sheet2 in A1:A900, the actual range will be found by coding based on Column A having content without empty gaps.
2. Assume form is on a worksheet named Form1 and the Name from 1st column of "Sheet2" goes into cell B9 of "Form1", EmployeeNo from 2nd column of "Sheet2" goes into cell D9, and the Code from 3rd column goes into Cell C10.

Dim rng as Range
With Worksheets("Sheet2")
Set rng = .Range(.Cells(1,1),.Cells(1,1).End(xldown))
End with
for each cell in rng
  worksheets("Form1").Range("B9").Value = cell.Value              'from Col A
  Worksheets("Form1").Range("D9").Value = cell.offset(0,1).Value  'from Col B
  Worksheets("Form1").Range("C10").Value = cell.Offset(0,2).Value 'from Col C
  worksheets("Form1").Printout
Next

Sending a Workbook as Email

This isn't Mail Merge but is kind of related.  If the subject is omitted then the workbook name will be used as the subject.  More information on hyperlinks can be found on my Build TOC page.

ActiveWorkbook.SendMail Recipients:="xxx@yyy.com", _
    Subject:="Email", ReturnReceipt:=True

Related Items

Creating a Table from a single column, such as used in address labels,

see topic within Snake Columns for a solution using Worksheet Functions.

naddr2ss,  Convert 1-up labels to Spreadsheet format.

The 1-Up labels must be separated by at least one blank line to start a new spreadsheet row.

Reformat or Rearranging Data in Columns. (extracted from My Excel Pages Home Page)

Macros especially useful for reformatting name and address listings, and for creating test data.  Rearrange columns by splitting, joining, or reversing columns of data.  Join() can be used as a reversal of a correctly executed text to columns.  Lastname() can be used as a put lastname first is not already done.  FixUSzip5() corrects US 5-digit zipcodes by conversion to 5 digit numbers as text.  SepTerm() can be used as a more limited version of text to columns that only separates from the first word. SepLastTerm() separates last word from beginning of text.  TrimALL() is used to TRIM leading and trailing spaces from selected range.  ReversI() is used to reverse the order of items in a row, column, or range.  RotateCW() is used to rotate a range of cells 90 degrees preserving formulas and formats.  Selection area must include cell A1.  MarkCells() is used to create test data within the invoked range.  MarkSepAreas() includes cell address and area number for creating test data across multiple ranges.  i.e. A1-1, B1-1, B2-2,C2-2,D2-2.  Additional material includes use of fill handle and creating a sheet with short cut keys for reference.

Control Word from Excel

Send information to Word (e.g. creating a new document) and retrieve information from Word (e.g. reading information from a document).  -- Erlandsen Data Consulting

Control Excel from Word

Send information to Excel from Word (e.g. creating a new workbook) and retrieve information from Excel (e.g. reading information from a workbook).  -- Erlandsen Data Consulting

Sending Personalized Email from Excel, John Walkenback, Tip 86, Using Outlook

Mail Merge - Without Word, John Walkenback, Tip 89, in Excel, of course

Zip+4 Lookup System -- http://www.usps.gov/ncsc/lookups/lookup_zip+4.html

and US postal abbreviations.  Additional Information from the US Postal Service:  Question #1 concerns batch processing is not available, but they offer a one-time free service for small files -- Diskette Coding service. http://www.usps.gov/ncsc/services/diskcode.html

Still more information on Mail Merge

Take a look at Cindy Meister's web site (.ch is Switzerland)
   http://homepage.swissonline.ch/cindymeister

Merge Pictures and Words - Oh My!, by Cindy Meister, creates a catalog with words and pictures from an Excel database.
   http://www.computorcompanion.com/LMMArticle.asp?ID=126

Word newsgroup that has mail in the name
   news://msnews.microsoft.com/microsoft.public.word.mailmerge.fields
and where I saw Cindy Meister's web site.

Some more websites for Word -- these are MVP sites should provide a good starting point.
   http://www.mvps.org/links.html#word as of 2000-08-01 there are 14 Word MVP sites.

The word MVPs have created http://www.mvps.org/word, and related MailMerge FAQ page http://www.mvps.org/word/FAQs/index7.html  Site Navigation buttons at top (ouch).

MS Office newsgroup for General questions for all of MS Office.
   news://msnews.microsoft.com/microsoft.public.office.misc

Excel newsgroup for General questions:
   news://msnews.microsoft.com/microsoft.public.office.misc
mailmerge questions generally get answered when a Word person wanders over to Excel.

Then of course there is doing your own search to see how close you can get to an answer.
   xlnews.htm

 

If you are looking for Bar Code information to use with Excel would suggest starting at Russ Adam's own FAQ page, where you will see that free fonts and software are availble as well as shareware and commercial packages.       BarCode1 FAQ Page       http://www.barcode-1.net/pub/russadam/faq.html Bar Codes other than US Postal Bar Coding, the most common is UPC 39     Universal Product Code (UPC) and EAN Article Numbering Code (EAN) Page,    Bar Code1 FAQ Page,  Plug-ins, VBX, etc.,  Fonts,   and Free and shareware barcode TT fonts    all on Bar Code 1 information pages by Russ Adams.  EAN-13 Modulus 10 check digit calculation, Bar Code Software Center, You might also check out a Google web page search using "excel printing ean-13 freeware".

Microsoft Articles:

• How to: Create a MailMerge in Word using Excel data. [broken link]
• In and Out of the Classroom with Office 97: Creating a Merge File -- Productivity Exercises with Office 97 Creating a Merge File

MS KB Articles:

• Q141991 - WD: How to Create Envelopes Using Mail Merge
• Q141922 - WD97: How to Start a Mail Merge
• Q141992 - WD: How to Create Mailing Labels Using Mail Merge Feature
• Q142756 - WD97: How to Design and Set Up Mail Merge Data Sources
• Q169968 - WD97: How to Copy Custom Label Information to Another Computer
• Q170130 - WD: How to Create Business Cards in Microsoft Word
• Q178716 - WD98: How to Create a Merge Document Using a Text Data Document
• Q180599 - WD97: How to Use Query to Merge Two Excel [xl97] Files for Data Source
• Q187383 - WD: How to Start a Mail Merge or Q187384 in WD98.
• Q196952 - WD: Word Could Not Re-Establish DDE Connection to MS Excel
• Q212314 - WD2000: How to Use a Microsoft Excel Data Source for Mail Merge (converter, DDE, ODBC)
• Q220607 - HOWTO: Automate Microsoft Word to Perform Mail Merge from Visual Basic
• Q241498 -- XL2000: How to Use an Excel Database to Send E-Mail -- this does not use mail merge, also see Sending Personalized Email from Excel, Tip 86 John Walkenbach.
• Q294683 -- WD2002: How to Use Mail Merge to Create Form Letters

Please send your comments concerning this web page to: David McRitchie mailto:DMcRitchie@msn.com.