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.
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 (#text, suggestion) 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. If you want to make sure that Mail Merge displays text exactly the same as you see in Excel you can run the AllCellsToText macro which creates a copy of the worksheet with All Cells Converted to Text Values as displayed in Excel (.text formatting vs .value).
You can convert things in place to text such as zip codes which should have been defined as text anyway and not as numbers with a macro fix zip codes -- see below in several places macro fixUSzip5 will convert them to text.
You can use a helper column to provide the text values of the numbers to be used
in Mail Merge. If the sheet is strictly to provide data to Mail Merge then use of
a helper column would have very little impact on your data.
i.e. =TEXT(A7,"#,###.00;#,###.000;0.00_);,@") or other format of your choice
or with a more generic function that simply uses the text version of what you see
For people comfortable with MS Word macros and particularly for Excel 2003 users and others who don't want to do anything in Excel to change formatting you can change things in Word though I expect these methods are more difficult even for people who prefer Word: Microsoft Office Assistance: Answer Box: Numbers don't merge right in Word, by Connie Miller
Your mail merge appears to be working just fine, but suddenly you notice that prices, dates and other numbers don't look right in your merged documents.
|2||307-555-1212||Daley||Jean||390 Waterdown Rd||Two Horses||WY||12345-1111|
|3||701-555-3434||Lyndhurst||John||1441 W Drive||Any Town||ND||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.
I am not using gummed mailing labels, but am printing on plain paper in a laser printer. Labels will be cut apart and attached to envelopes using 1 7/8 inch clear tape.
Biggest stumbling block with using Mail Merge and an Excel database: Be sure to have your Excel spreadsheet as the first worksheet tab in your workbook. Even using a defined name, it appears you must have the worksheet as the first worksheet tab in your workbook.
To move the worksheet to the first sheet position, grab the worksheet tab with a left-click (don't release) and drag it to the left of the other worksheets (then release). [Mouse Tutorial (word doc)] [Using the Mouse (HTML)]
The second biggest stumbling block is failure to use text zip codes.
FixUSzip5() corrects US 5-digit zipcodes by conversion of 5 digit numbers as text. Left as numbers the leading zeros of Eastern US states will be dropped which will mess up the address and the postal bar coding. Five digit zip codes are an anchronism anyway. In the US you should be using zip+4 which would automatically be accepted as text when typed into Excel. In the rest of the world there may be letters and/or special spacing. Best to not think of zipcodes as numbers but as text. Format your column as text, before typing in zip codes.
If your data is already in Excel and some of 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 so as elsewhere would always recommend that zip codes and phone numbers always be text.
For readability in Excel, I would align the entire zipcode column left aligned. This will not affect Mail Merge using Word.
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
2. Data Source --> Get Data --> Open Data Source --> (i.e. c:\temp\xyzfile.xls)
Entire Spreadsheet, Filtered Spreadsheet, or from a Named area
(i.e Sheet19 or ziplabels).
a Filtered Spreadsheet treats what you see after filtering as the content.
Read more about Auto Filter on my pages, or about using Advanced Filters on
Debra Dalgleish's site, and some other references.
Reminder: Be sure to have your Excel spreadsheet as the first worksheet tab in your workbook.
revision to alleviate formatting problems such as with currency
so you use what you see in Excel: (#DDE)
In the Confirm Data Source dialog box, click the MS Excel Worksheets via DDE (*.xls), and
then click OK. (also look for addtional DDE references below)
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)
<<First name>> <<Lastname>>
<<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.)
Biggest stumbling block with using Mail Merge and an Excel database: Be sure to have your Excel spreadsheet as the first worksheet tab in your workbook. Even using a defined name, it appears you must have the worksheet as the first worksheet tab in your workbook. You must also select 'Microsoft Excel :Workbooks' in the bottom of the selection dialog, though this may only apply if you've also used Access as a database in the past (just something to watch for).
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.
The easiest way to remove duplicates without harm to your original input is to use filtering
Example using Advanced Filter to remove duplicates:
To eliminate filtering initiated with Advanced Filter
- Select Column A
- Data, Filter, Advanced Filter -- $A:$A will already be filled in
- check the Unique Records box, and press OK
- Data, Filter, Show All
- if that doesn't work then try Data, Filter, Advanced Fiter (don't make any entries)
Some people more familiar with MS Word than Excel might prefer to remove duplicates within Word. Mail Merge FAQ or specifically the article Suppress Duplicate Records.
In my testing a width of 3 inches appears to be required when
adding postal barcoding with
MS Word 95, which limits labels to 2-UP with 8 1/2 x 11 inch paper.
|Dimension||Description||Count||Across||Down||Avery Label Number|
|1/2 x 1 3/4"||Return Address Label||80 labels/sheet||4||20||# 5267, 8167|
|1 x 2 5/8"||Mailing Label||30 labels/sheet||3||10||# 5160,5260,8160|
|1 x 4"||Address Label||20 labels/sheet||2||10||# 5161,5261,8161|
|1 1/3 x 4"||Address Label||14 labels/sheet||2||7||# 5162,5262,8162,8662|
|2 x 4"||Mailing/Shipping Label||10 labels/sheet||2||5||# 5163,8163|
|3 1/3 x 4"||Shipping Label||6 labels/sheet||2||3||# 5164,8164|
|2 3/4 x 2 3/4"||Diskette Label (3.5)||9 labels/sheet||3||3||# 5196,8196|
|2/3 x 3 7/16"||File Folder Label||30 labels/sheet||2||15||# 5266,5366,8166|
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 [archived] most of which are probably defined in MS Word labels. ( List of Avery labels).
|Avery #||Avery #||Avery #||Avery #||Avery #||Avery #||Side
|Horiz Pitch||Vert Pitch|
|Avery #||Avery #||Avery #||Avery #||Avery #||Avery #||Side
|Top Marg.||Horiz Pitch||Vert Pitch|
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.
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.
|2||555-1212||Jane||Doaks||11712 North College||Carmel||IN||46033||[x]|
|3||(555) 555-1213||John||Doaks||11712 North College||Carmel||IN||46033-5064||[x]|
|5||=HYPERLINK("http://www.mapquest.com/cgi-bin/ia_find?link=btwn%2Ftwn- map_results&random=565&event=find_search&SNVData=&uid=&address="&D2&"&city="&E2 &"&State="&F2&"&Zip="&G2&"&Find+Map.x=0&Find+Map.y=0","[x]")|
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 SubSee 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 better Excel example that creates a new sheet, repeats the row the number of times indicated in a column of your choice, changes the repeat number to a 1, and can be invoked from another macro to specify the repetition indication column can be found in another posting Label Printing, 2003-08-25.
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
Additional information on rearranging input data for use such as in creating labels can be found on my snake columns page.
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.
- 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.
- 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
An example of how to code an email address in HTML with multiple recipients, along with a subject title and the start of a message.
Send email to the webmaster (please click on link to see example generated).
<p>Send email to the <a href="mailto:MyClub@yahoo.cXm%3b MySelf@msn.cXm?subject=My Club Inquiry&body=concerning Website http//www.geocities.com/myclub%0C...">webmaster</a>
The %3B is hex for a semicolon; and, the %0C is hex for carriage return. The space after %3B between recipients is optional.
Some Links for creating Mailto: links in HTMLSome very good links have come up since I originally worked these things out, these may be all you need to generate the link that you need for an HTML page.
Should be able to add an attachment in the HTML but haven't been successful with anything like c:\\ — resembling: &attachment=\\temp\tsthtml.htm — of course the file would actually have to exist on the user’s system for it to actually work, so it’s really a matter of seeing if it can work rather than actually being usable on a web page without using a FORM.
- Batch files - E-mails generated by batch files, quick explanation.
- Unleashing the Power of mailto URLs, This will actually generate everything from:, to;, cc:, bcc:, subject;, body: via a form. (mailto: Test) builds email ready to send.
Using VBA to generate an Email
The above is what you are actually creating in the VBA examples, so it may help you in understanding some of the coding. You may have add some coding of your own in Excel VBA to convert some additional characters that might interfere.
As shown above you can create a hyperlink, but you may run into problems with length. You will probably see that some characters are substututed for in John's macros (see tip #86 & #92). If you want cc and bcc you can generate -- mailto:xyaa?cc=xyab&bcc=xyac&subject=xyae&body=xyae
A1:E1 -- to:, subject:, value1, value2, send
B2: For your information
E2: =HYPERLINK("mailto:" & A2 & "?subject=" & B2 & "&body=" & "You have paid " & C2 & " and still owe " & D2, "[mail]")
Additional things you may need to do in your own mail program with Excel VBA'-- Replace carriage returns with %0D%0A (hex) msg = Application.WorksheetFunction.Substitute(msg, vbCrLf, "%0D%0A")
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. Actually use use of the colon after the 1st word in subject (Email:) is something to avoid as it will not be sorted just like Re:, FW:, are not sorted.ActiveWorkbook.SendMail Recipients:="email@example.com", _ Subject:="Email: ...", ReturnReceipt:=True ActiveWorkbook.SendMail "firstname.lastname@example.org", "Email: " & Range("A1") _ & " " & Range("B1")Ron de Bruin has a page, Some Coding examples for use with Sendmail. Also see the related area below, and ...
- Send email with an attachment
- Sending Personalized Email from Excel, John Walkenbach, Tip 86, Using Outlook
- Mail Merge - Without Word, John Walkenbach, Tip 92, in Excel, of course. (This is the one with the Elephant -- Elephants R Us).
- Event macro to help compose Email by doubleclicking on subject topic.
- Email from Excel and from HTML
Importing Excel Data Into Word (Office) and retain ability to use Excel tools in Word. (LockerGnome 2005-10-07).
Simple conversion of Excel to HTML to include images by composing the HTML coding within the Spreadsheet using an HTML Tag and Symbol ignorate conversion [see posting] also see
Show Picture Showing a picture from a list of pictures in one spot using validation to show picture from another sheet via a validation list.
KB285055 - HOW TO: Create Watermarks in Word 2002, unrelated to mail merge
How to create a Mail Merge by Beth Melton and Dave Rado. [http://www.mvps.org/word/FAQs/MailMerge/CreateAMailMerge.htm]
Mail Merge in Word 2003, by Linda F. White at Wellesley College
Creating a Mail Merge Data Source
by Beth Melton. [http://word.mvps.org/FAQs/MailMerge/CreateADataSourceContent.htm]
From Data Sources: Word, Excel, Access, Text, Outlook, Outlook Express
Take a look at Cindy Meister's web site (.ch is Switzerland)
and Mail Merge documentation in MSDN by Cindy Meister.
See reference to use of Mulitple items per condition i.e. several invoices per customer.
Word newsgroup that has mail in the name
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.
Excel newsgroup for General questions:
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.
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
available as well as shareware and commercial packages.
BarCode1 FAQ Page « see topic: Can Word, Access, Excel, or Clipper be used for
an application where bar codes can be scanned and printed?
You can get bar code fonts for free, or you can pay for them, whichever you prefer.
Bar Codes other than US Postal Bar Coding (above), 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. It is not a simple case of typing some data and switching the font to an EAN or UPC font, you need a macro, see the UPC/EAN Page. All of the above is on Bar Code 1 information pages by Russ Adams.
Barcode fonts, Luc Devroye, McGill University, where to get fonts.
EAN-13 Barcode Symbology, Modulus 10 check digit calculation and VBA coding«, Bar Code Software Center. In the USA will be known as UPC-13 symbol and will be in effect by January 1, 2005 for all US retail scanning systems.
Free 3 of 9 barcode font « and "Free 3 of 9 Extended", Matthew Welch's "I Shot the Serif" fonts (free).
|*314-34-1234*||entered as *314-34-1234* — you would need to install "Free 3 of 9" to see this displayed, you can compare it to a picture here. This is the author's own site, and his font(s) may not be included in any collection requiring payment of any fee. If installed you should see the barcode at the left in Internet Explorer, but to see in Firefox you would have to also modify Firefox. (alternative: use a Google search on code39.ttf, you should be able to find additional free versions)|
BarFonts, a collection of PostScript Type 1 fonts for printing bar codes, available under the GNU General Public License.
MS Office Bar Code Macros & VBA Functions for Excel, Access and Word on Windows or Macintosh Systems, information but charges heavily for actual fonts, etc. Bizfonts and IDAutomation.com are anything but free use of term "free download" is a term designed to mislead you -- the information is free, but the usage of fonts and coding is anything but free. (commercial site). Exception is Free Barcode Font Code 39 TrueType Download (free for personal use).
How Bar Codes Work, Understanding (commercial site).
You might also check out a Google web page search using "excel printing ean-13 freeware", or do some searches at The FreeSite.com -- Free_Fonts
"BobDawg's Pad - UPC-A Generator, enter 11 digits the 12th digit checksum will be created for you.
Barcode Maker v1.0.0, is a utility that creates Code 3 of 9 barcodes (Code 39) from user entered text. The resultant image (.bmp) can be copied to the clipboard and pasted into any program capable of accepting images.
sample "inventory" spreadsheet using bar code software, Taltech, download, contains a VBA function that does a lookup in a worksheet column for data read in from a bar code scanner and if found can either increment or decrement a "quantity" in the column next to the bar code number. [ref]
The following articles are very well written but like other content at Microsoft including access to the MS Knowledge Base are .asp or .aspx web pages and as such are best viewed with a decent (fast) computer, with decent (high speed) access, and with a decent (IE) browser. (IE users should keep Task Manager active so they can tell when IE causes performance problems.)
Microsoft Office Assistance: Answer Box: Print mailing labels in Word 2002, Connie Miller
Microsoft Office Assistance: Word mail merge: A walk through the process, Word 2002, Connie Miller, describes what Mail Merge is and how it works tregardless of your database.
MS KB Articles:
Please send your comments concerning this web page to: David McRitchie send email comments
Copyright © 1997 - 2007, F. David McRitchie, All Rights Reserved