Sending Christmas cards: Creating mailng labels automatically
A family member sent out an emergency email plea for help:
I bought labels: Avery 8160, which have very clear instructions about how to enter names and addresses manually, but nothing about how to use data from an Excel file to do so.
Avery.com tells me what fancy things to buy. Excel Help only explains about its own internal addresses. Word allows me to outline and print labels, but help only explains how to import items from Excel into a text.
Can someone please tell me which buttons push?
I replied that if he sent me the spreadsheets, I could find a solution for him. After all, I’m the family member who actually *works* for the company that makes these great products Excel and Word!
I started dreading that the data was not well formatted for labels. For example, is all the data in a single Excel cell ? Is it all in separate cells? Is there a separate first name, last name, title, city, zip column? How about spouse names (Mr. and Mrs. John Doe)? Are there columns for email address? How much work would have to be done to clean up the data so it has a consistent format?
As it turns out, the data was fairly well formatted for labels. Column 1 was exactly formatted as “Mr. and Mrs. John Doe”, columns 2 & 3 were exactly formatted for labels as “123 Anywhere St”, “City, State Zip” so there wasn’t much cleanup to do.
There were 3 spreadsheets: Local to Hawaii (where I used to live and most of my family still does), Overseas (which to Hawaiian people means non-US), and Mainland US.
After a little thought, I came up with a few possible solutions.
- Use FoxPro, import the Excel data, use automation with Word to create the labels
- Import the data to Outlook (using Foxpro or Excel) and then write an Outlook macro: this meant he’d have to stop storing data in Excel and start using Outlook.
- Write an Excel Macro to scan through the data and automate Word to create the labels.
- The code could be written in Foxpro, Word, or Excel.
- I could create the label document somehow and email it back to him, but that meant he depended upon me every time he needed a new set of labels.
Solution 3 seemed the simplest, but I chose to prototype the automation in FoxPro because I can manipulate live objects (like an Excel range or a Word Selection object) from the command window and the intellisense would be very helpful. Also, Foxpro doesn’t require pre-registering COM objects, like VB, Word or Excel to get Intellisense. Intellisense requires an accurate typelibrary. However, the typelibrary could specify that a method returns a generic object (could be a cell or a range or a value perhaps), and then no intellisense will show for that. In VFP, the intellisense will work because it’s manipulating a live object, and when its instantiated, the type information can be obtained dynamically. Also, the powerful VFP debugger allows you to single step the code, examining the values and even executing further commands.
I used the VFP Object Browser to show the Word TypeLibrary to get some of the constants, like wdPrinterManualFeed an wdCell
The 8160 label sheet has 3 labels across, so the loop has to do a single MoveRight after the 3rd column.
This is the FoxPro prototype code:
#define TRUE .t.
#define FALSE .f.
#DEFINE wdPrinterManualFeed 4
#define wdCell 12
PUBLIC oWord as word.application
PUBLIC oLbls as WORD.Document
PUBLIC oExcel as excel.application
oExcel=CREATEOBJECT("excel.application")
cFile="d:\calvinh\ba\Christmas Local Addresses Dec 2004.xls"
oExcel.Workbooks.Open(cFile)
oWord=CREATEOBJECT("word.application")
With oWord as WORD.Application
.Visible = true
.Documents.Add
.MailingLabel.DefaultPrintBarCode = False
oLbls=.MailingLabel.CreateNewDocument("8160","", ;
"ToolsCreateLabels1",.f., wdPrinterManualFeed)
* ExtractAddress:=False, PrintEPostageLabel:=False, Vertical:=False)
.Documents(2).Close(false)
ENDWITH
nRow=2 && first row has no data: just column headings on some spreadsheets
WITH oExcel
nCol=1
DO WHILE .t.
oRange=.Range("a"+TRANSFORM(nrow))
IF ""=oRange.Text
EXIT
ENDIF
cName=.Cells(nrow,1).Text
cAddress=.Cells(nrow,2).Text
cCSZ=.Cells(nrow,3).Text
WITH oWord.Selection as WORD.Selection
.TypeText(cName)
.TypeParagraph
.TypeText(cAddress)
.TypeParagraph
.TypeText(cCSZ)
.MoveRight(wdCell)
nCol=nCol+1
IF nCol=4
nCol=1
ELSE
.MoveRight(wdCell)
ENDIF
ENDWITH
nRow=nRow+1
ENDDO
ENDWITH
It was fairly straightforward to translate this to VB for an Excel macro which could be used.
Open the spreadsheet in Execl
Choose Tools->Macro->Macro
Give the macro a name, like “labels”, then click “Create”. The Visual Basic Editor comes up
Choose Tools->References then put a checkmark on Microsoft Word 11.0 Object Library, then OK (yours might have an older version number than 11.0)
Make the code look like below by pasting it in. Then hit the F5 button.
It automates Word to create a mailing label document, scans through the spreadsheet and fills in the labels.
Sub Labels()
Dim oWord As Word.Application
Set oWord = CreateObject("word.application")
With oWord
.Visible = True
.Documents.Add
.MailingLabel.DefaultPrintBarCode = False
Set oLbls = .MailingLabel.CreateNewDocument("8160", "", _
"ToolsCreateLabels1", False, wdPrinterManualFeed)
.Documents(2).Close (False)
End With
‘ nRow = 2 ' first row has no data: just column headings on some spreadsheets
nCol = 1
Do While True
If "" = Range("a" & nRow) Then ' blank cell in first column ends loop
Exit Do
End If
cName = Cells(nRow, 1).Text
cAddress = Cells(nRow, 2).Text
cCSZ = Cells(nRow, 3).Text
If "" <> cAddress And "" <> cCSZ Then
With oWord.Selection
.TypeText (cName)
.TypeParagraph
.TypeText (cAddress)
.TypeParagraph
.TypeText (cCSZ)
nCol = nCol + 1
.MoveRight (wdCell)
If nCol = 4 Then
nCol = 1
Else
.MoveRight (wdCell)
End If
End With
End If
nRow = nRow + 1
Loop
End Sub
On the phone, he had an older version of Word and Excel, so the “MailingLabel.CreateNewDocument” line didn’t compile correctly. I didn’t have the older version of Word handy, so I asked him to start Word, record a Macro, choose Tools->Letters and Mailings->Envelopes and Labels, specify Avery 8160, then New Document. (That’s how I figured out many of the lines of the code above: recording a macro.) We inspected the Word macro, copied/pasted it into the Excel macro, and he was very happy to see the Word label document being filled automatically with the Excel address data.
45517
Comments
Anonymous
December 12, 2004
Alternately, couldn't he have just used the Word mail merge feature and imported the data from the Excel file using the built-in Word functionality?Anonymous
February 15, 2006
http://incompetech.com/beta/maillabel/
I just made an online thing that does this.
Give it a shot... (ok, ok... I'm 2+ years late)Anonymous
March 10, 2006
Hi Calvin,
I would like to know if there is a way to mailmerge a word document to multiple EMAIL ADDRESSES WHICH ARE ENTERED IN A SINLGE cell of an excel worksheet...not each email address in each cell..
Also, to let you know i use MS Outlook client at office.
Please also let me know how to automate it in Excel using VBA.
Warm Rgds,
JunoonAnonymous
March 17, 2006
Great information for us users. Keep up.Anonymous
April 21, 2006
Congratulations on your site i like the design very muchAnonymous
May 29, 2006
your website is flashy and wonderful but get us more postsAnonymous
December 18, 2006
thoughts from a professional developer I do not agree. Go to http://www.bluehotels.info/plein_Greece/coif_Dodecanese%20Islands/chthonian_Rhodes_1.htmlAnonymous
January 29, 2007
thoughts from a professional developer I do not agree. Go to http://www.alljobz.info/apogee_United%20Kingdom/deposit_England/requisite_Blackpool_1.htmlAnonymous
March 15, 2007
thoughts from a professional developer I do not agree. Go to http://www.freecareers.info/escapade_Republic%20of%20Ireland/victim_Leinster/act_Dublin_1.htmlAnonymous
June 19, 2007
Sometimes friends or relatives might hear that you’re an expert with using computers, and might ask youAnonymous
June 21, 2007
My brother and future sister in law wanted special word search puzzles created as wedding favors. TheAnonymous
June 06, 2008
A family member sent out an emergency email plea for help: I bought labels: Avery 8160, which have very clear instructions about how to enter names and addresses manually, but nothing about how to use data from an Excel file to do so. Avery.com tellsAnonymous
June 10, 2008
PingBack from http://grant.thedigestonline.info/excelmacrolabel.htmlAnonymous
June 16, 2009
PingBack from http://topalternativedating.info/story.php?id=13735