Mailing Labels using printer object

[westconn1]

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

'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

[westconn1]

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

[peteleeb]

Cool

Thank you. I'll Look into it. Sorry for the delay in responding.. Got hit with the flu.