Mail Merge,  Printing Labels using Mail Merge with data from Excel
Home page:
[View without Frames]

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   (#preparation)

See notes on zip codes if you are having a problem with zip codes in Excel.

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.

Directions to print a letter | labels | email using Office 2007 (#office2007)

For Office 2007, there are no menus and you will have to use the Tabs in Microsoft Word.  Mail Merge is one area that may have actually seen some improvements at least for those doing it for the first time.  With Excel 2007 it will not be neccessary to have your Excel database as the first worksheet because your worksheet or a range will be your table and will be presented to you as a choice.

Excel 2007 The columns must have labels in Row 1, for this example using “Phone”, “Last name”, “First name”, as seen in the table later.

Here is a video that might be easier to follow: Mail Merge using Word 2007 and Excel 2007 - You Tube

«firstname» «lastname»
«City», «ST» «Zip»
Phone: «phone»

General letter to concerned citizens:
Blah, blah, blah

MS Word 2007

  1. Make additions to your Excel table and save the table (you do not need to delete names, selection can be done later in Word)
  2. You must have your Word document already set up before starting the Label Merge.
  3. If doing Labels you must choose the Label type. i.e. Avery form 5395 (Name Tags) and suggest you show the ruler so you can see horizontal area/pattern of labels.
  4. start with the Mailings (tab)
  5. Start Mail Merge:
  6. Choose {Letters, Email Messages, Envelopes, Labels, ..., Step by Step Mail Merge Wizard}
    Suggest you start with the Wizard choice at the bottom of the dropdown for first time use.
  7. Use an Existing List
  8. Fill in filename -- Browse for the list from the My Computer at the left, then enter the path and file name of the Excel file in the dialog.
  9. Select Recipients: select your saved Excel workbook, then the sheetname (i.e. Sheet160$), You should also see each of the fields as excel columns here
  10. Edit Recipients List: (one by one)
    or, skip selecting recipient if sending to everyone in list
  11. Arrange your labels
  12. Write & Insert Fields:  then individually use the Insert Merge Field (dropdown) as needed in your documents.
  13. Preview your labels to see what a label looks like (you may be seeing just the tags, not the names in preview)
  14. Complete the merge
  15. Finish:  Finish & Merge
      {Edit individual documents | Print Documents | Send Email Messages}

Numbers not Rendering correctly in Mail Merge (#rendering)

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
    i.e. =personal.xls!GetText(D1)

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.
 1 Phone  Lastname Firstname  Address1   City ST zipcode
 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.

Directions for Word to create mailing labels   (#uspostalbarcode)

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.

Preparation of zip codes, if using Excel (#zipcd)

You should bring your data into Excel as text and maintain fields in Excel as text for best results, if possible.  Text is by default formatted left aligned, numbers are right aligned.  A mixture of 5-digit zip codes and zip+4 obviously should be left aligned for readability.  If the data is coming into Excel as a .csv file change the extension to .txt so you have control with the text import wizard and select the appropriate column(s) as Text (not General, not a date field).

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 type
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)  The following are examples of what may have been available six months earlier at Avery.

Filtering to remove duplicates (#filtered)

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
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.

Summary of Most Commonly used Labels   (#avery)

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  20   # 5267, 8167 
1 x 2 5/8"   Mailing Label   30 labels/sheet  10   # 5160,5260,8160 
1 x 4"   Address Label   20 labels/sheet  10   # 5161,5261,8161 
1 1/3 x 4"   Address Label   14 labels/sheet    7   # 5162,5262,8162,8662 
2 x 4"   Mailing/Shipping Label   10 labels/sheet    5   # 5163,8163 
3 1/3 x 4"   Shipping Label     6 labels/sheet    3   # 5164,8164 
 2 3/4 x 2 3/4"   Diskette Label (3.5)     9 labels/sheet    3   # 5196,8196 
2/3 x 3 7/16"   File Folder Label   30 labels/sheet  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).

Width Hgt Type Across Down Labels
Avery # Avery # Avery # Avery # Avery # Avery # Side
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
Avery # Avery # Avery # Avery # Avery # Avery # Side
Top Marg. 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.    ||     Weights and Sizes of Papers (metric vs. US)

US Postal Cards (#postalcards)

US Postal requirement: (jan 10, 1999)
  for Postcard Rate Dimensions:
    Minimum: 3-1/2 by 5 inches by 0.007 inch thick
    Maximum: 4-1/4 by 6 inches by 0.016 inch thick
I didn't print double postcard information at the time, but you can look at

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   (#finishing)

Font:  Word uses the settings in the 'Envelope Address' styles in Format -> Styles as the defaults for the envelope; you can [Modify] the font choice there if you would prefer a different default.

Including a picture on the label   (#picture)

Haven't tried this but found this posting by Jimmy D. in 2000-01-02.
I created a template for Avery 8163 and inserted a JPEG file into a label. After inserting your picture, clicked on the picture so the picture place holders show.  From the menu bar selected Format, Picture, then the Layout tad.  Under Wrapping Style, if you select Square or Tight, you will be able to wrap your text completely around the picture.  Now click OK.

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   (#wab) -- see topic "Updating the Address Book or just Printing it from an Excel spreadsheet", information there includes creating/moving Address Book, and moving storage location of postings and email to another disk.  Windows Address book is something you should make a point of backing up (backup.htm).

Name and Address Listing with MAP Reference   (#mapref)

1Phone First LastnameStreetCity/Town STZipcodeMap
2 555-1212 Jane Doaks11712 North CollegeCarmelIN 46033[x]
3 (555) 555-1213  JohnDoaks11712 North CollegeCarmel IN46033-5064 [x]
5 =HYPERLINK(" 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]")

Printing Multiple Labels based on Column A (#multilabel)

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 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.
11B1 AA
11B1 AA

Additional information on rearranging input data for use such as in creating labels can be found on my snake columns page.

Printing an Excel Form, Multiple times (#form)

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

Web pages with multiple email addresses for webmaster (#mailto)

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//">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 HTML

Some 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.

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
 C2: '$1,000.00
 D2: '$814.74
 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")

Sending a Workbook as Email (#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.  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:="", _
    Subject:="Email: ...", ReturnReceipt:=True
ActiveWorkbook.SendMail "", "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

More Excel and Word things (#excel_in_word)

Importing Excel Data Into Word (Office) and retain ability to use Excel tools in Word. (LockerGnome 2005-10-07).
On-site macros and/or pages.  My pages are oriented to Excel so any macros that I mention in this on-site portion are for Excel.
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.
More Mail Merge with Excel as Database (#Word)
Mail Merge Labels with Word XP,
Word XP changed things, you may have to read this document for doing special things.  In earlier versions of Word connection to the data file was by DDE.  Microsoft has replaced this default setting by a new OLEDB connection method. This has benefits and drawback. The benefits are that Word can now connect to client/server databases such as SQL or Oracle without resorting to an intervening interface such as MSQuery. One downside is that OLEDB provides direct access to the data and thus Word is expected to provide the formatting.
Additional Related web pages related to using Excel and Word together, but not related to Mail Merge (#control)
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 See comments in code, you will need to set a reference in the VBE (Visual Basic Editor) for example in Excel 2000 -- Tools, References, [x] Microsoft Word 9.0 Object Library
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

Additional Links related to labels, name and address, zipcodes, or SendMail but not related to Mail Merge (#other)

Also see Sending a Workbook as Email in a topic above.
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 -- Elephant's R Us).  Compare to one on Microsoft's site -- Using Microsoft Excel To Do a Bulk Mailing in Microsoft Outlook
International Address Formats (Postal/Mailing Addresses) and Other International Mailing Information
MATCH Worksheet Function in a macro as a substitute for hyperlinks,
in a name and address list.  Solution involves a Double-Click Event macro (#match example).
Some example Codes with for using Sendmail, Ron de Bruin, has several examples and has developed it to quite an extent. 
Unleashing the Power of mailto URLs, By Robert Husted
Initiate Email by double-clicking on a cell with the subject, .
NetPost Mailing Online Upgrade Information, Service of US Postal Service, to print postcards, letters,
for an additional one cent per item.  Postcards can be printed on both sides.  Uses your .csv mailing list.  How to Print Your Marketing Postcards for a Penny and Address Them for Free, by Jason Van Orden
Zip+4 Lookup System --
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.
Zip Code Database Project (,  The Zip Code Database project exists to provide US Zip Codes in their entirety; latitude and longitude coordinates included! See the home page for the distance calculation algorithm.  (Also see How to "Find store locations near you" in VB? - Visual Basic)
Free Templates For Printing Labels And Business Cards In An Instant ~ Windows Fanatics, autofillPDF-Labels 1.0 has free, fillable PDF label templates for U.S. letter-size label sheets.
Excel pages belonging to individual current and former Microsoft, Excel MVPs lists about 40 Excel MVPs, eleven are known to have Excel web pages.  Microsoft now also maintains a list of current MVP's and MVP sites.

Merge Pictures and Words (#photos)

Merge Pictures and Words - Oh My!, by Cindy Meister, creates a catalog from text wording and external pictures from an Excel database.  [photos, photographs]  Also see extracting JPG / GIF files from an Excel document (charts). Computer Companion, E-zine article
   Linking to Photos, Debra Dalgleish (short posting), similar, less explanation.

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

Still more information on Mail Merge   (#more);

If you want specific information on printing labels from Excel with Mail Merge,
I think you are already on the best page.

How to create a Mail Merge by Beth Melton and Dave Rado.   []

Mail Merge in Word 2003, by Linda F. White at Wellesley College

Creating a Mail Merge Data Source by Beth Melton.   []
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. as of 2000-08-01 there are 14 Word MVP sites.

The word MVPs have created, and related MailMerge FAQ page  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.

Bar Code information (#barcode)

You will need a barcode font in order to print barcodes.  Due to screen resolutions, you will not be able to see the font displayed properly on the screen.  Also due to your printer's resolution, you will probably have to print them significantly larger than you see them on printed on products in order to see and properly scan them.

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 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).

UPC barcode

You might also check out a Google web page search using "excel printing ean-13 freeware", or do some searches at The -- 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.

Barcode Applications for Excel (#barcode_xls)

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]

More informative pages on Mail Merge, but watch your .asp or .aspx (#more_MS)

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.

Microsoft Articles:   (#mskb)

MS KB Articles:

This page was introduced on Feb. 19, 1999. 
[My Excel Pages -- home]    [INDEX to my site and the off-site pages I reference] 
[Site Search -- Excel]     [Go Back]    [Return to TOP

Please send your comments concerning this web page to: David McRitchie send email comments

Copyright © 1997 - 2007,  F. David McRitchie,  All Rights Reserved