Mailing Labels using printer object
here is some simple code to print mailing labels from a database or any other input method, using the VB printer object
it allows for you to set any size of label or qty of label to page, either in code or from user selection
there is no provision in this code to make sure that the text will fit on the label, either in width of text or lines per label, you will need to limit user choices to what will fit and work, the textwidth here is controlled by the database field sizes, so i didn't need to worry about it
the code is hacked out of much larger sub, so you will need to declare all your own variables etc. i have tried to comment everything to make it easy to use
VB Code:
'change to the printer you want to use first
' Make sure that the printer paper size is correct for the label sheets in use, otherwise you can spit out many sheets of labels, ie printer set for Letter, when the labels and code are for A4 size
With Printer
.Font.Name = "Arial" 'optional setting printer fonts etc
.Font.Size = 10
End With
'these options can be selected by user input from textbox or option buttons. design your own form
la = 3 'labels across page
ld = 15 'labels down page
np = la * ld
labelw = 7 * 567 'width of label centimeters for inches change 567 to 1440
' if any vertical gap between labels add to label width
labelh = 2.5 * 567 ' height of label in cm for inches change 567 to 1440
' if any horizantal spacing between labels add to label height
tmargin = 500 'top margin from top of page to first line printing position
lmargin = 600 ' left margin from left edge of page to first character
' margins must be big enough for the selected printer to be able to print to
' test the margins and adjust
' if the page has ½" to edge of label, a starting value for top and left margins might be 900
' if the label goes to the edge of the page then try about 250
For i = 0 To DataRS.RecordCount - 1
If (i + np) Mod np + 1 = 1 And Not i = 0 Then Printer.NewPage
lmarg = ((i + la) Mod la) * labelw + lmargin
topline = (((i + la * ld) Mod la * ld) \ la) * labelh + tmargin
' print each label from database records or whatever
With Printer
.CurrentY = topline
For j = 0 To DataRS.Fields.Count - 1
'where DataRS is a recordset with the fields in the order to be printed
.CurrentX = lmarg
Printer.Print DataRS.Fields(j)
Next
End With
DataRS.MoveNext
Next
Printer.EndDoc
Re: Mailing Labels using printer object
i have updated the code above to fix a bug with newpage, and added a warning about incorrect paper sizes at the printer
when helping some one with this code i noted that the original code was for tractor feed labels on custom size paper 15" page length
as in most cases now you would be using A4 or letter pages of labels, you have to use the correct sizes for your actual labels, a good solution for label height would be labelh = Printer.Height \ ld rather than using the nominal size of the label eg 15 * 2cm would be longer than A4 page.height, 1.95cm would probably work OK, but i now prefer the former.
testing with a pdf printer driver is great, but some testing with real labels is required to align printing to labels, which can not be seen on plain paper pdf
Re: Mailing Labels using printer object
Cool
Thank you. I'll Look into it. Sorry for the delay in responding.. Got hit with the flu.