Any way to print labels from Excel doc w/out using Word mailmerge

[jwc123]

Isn't there some way to create a mailing list/labels from an Excel document
without doing the whole Word Mailmerge thing?

 

[Gord Dibben]

Trial and error with each label a single block of cells.


Gord Dibben MS Excel MVP

 

[Shane Devenshire]

Hi,

Yes, but its more work. You need to manually setup a print range that will
match your labels and then create a macro to cycle through each recipent's
data populating the label and then printing it. If you are only doing one
label at a time you can use Auto Filter to populate the label and then
manually print it.

 

[JCS]

Hi,

If you know how to work in Access you can accomplish this very easily and
quicky. I've done this in both Word and Access and Access is much better.
Create your list in Excel, then link your workbook to your Access database.
Create a query and base your label report off of the query. It sounds harder
than it really is, but once you've created the label report just run your
report. You do this once only.

HTH.
John

 

[jaf]

Hi,
Of course.
You need a layout for the label and a print range.
You need a list of data.
You need a macro.

I'm assuming it's a label not a form letter, and the data is on sheet 2.
Column A=a number that we use as a lookup value.
Column B=Name
Column C=Street # & street name
Column D= Additional address data if required (apt #, PO Box)
Column E= City/town
Column F=State
Column G=Zip or postal code

Put some data in the list.
1
Clark Kent
47 Rural Rte 2.
Kent Farm
Smallville
KS
66684

Then select the range and do a Insert>name>define and name it table1.

Let's assume sheet1 A1:B4 is the print range.
We will use C1 as the cell to drive the list.
So we start by placing the value 1 in C1.
In A1 enter the lookup formula =vlookup($c$1,table1,2). Press enter and you should see Clark Kent in the first line.
In A2 enter the lookup formula =IF(VLOOKUP($C$1,table1,3)<=""," ",VLOOKUP($C$1,table1,3))
In A3 enter the lookup formula =IF(VLOOKUP($C$1,table1,4)<=""," ",VLOOKUP($C$1,table1,4))
In A4 enter the lookup formula =VLOOKUP($C$1,table1,5)&", "&VLOOKUP($C$1,table1,6)&" "&VLOOKUP($C$1,table1,7)

Use print preview to size the cells & margins for your label.
Now all you need is a macro to print the label.

Open the VBA editor by typing ALT F11
Insert a module.
Copy & paste this into the module.

Sub MyLabelPrint()

LabelCount = Range("table1").Rows.Count 'how many labels to print

For MyLabels = 1 To LabelCount
Sheets("Sheet1").Cells(1, 3) = MyLabels 'changes the lookup number
Application.Calculate 'in case we go to fast
Sheets("sheet1").PrintOut Copies:=1, Collate:=True 'print 1 copy
Next

End Sub

And run the macro using the default printer.

John