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