DYMO Label Printing from Microsoft Access VBA

DYMO printers are very popular for thermal label printing enabling the printing of labels directly from Microsoft  Office (Excel, Word, etc.), QuickBooks, CardScan and many other applications.  Common uses are printing address labels, file folder labels and name badges.

This can be done manually with easy to use DYMO templates but a recent request was to automatically send information to DYMO label printers from a Microsoft Access custom application.  Below is the approach when using VBA.

First, create your label template using the DYMO tools that are installed as a result of setting up the DYMO label printer.

Paste the below code into a VBA module and the call the PrintLabels function from your Access application. In this example we are passing a Project Name and Initials to the function to create file folder labels.

Option Compare Database
Option Explicit

Global DymoAddIn As Object, DymoLabels As Object

Function PrintLabel(sProjectName As String, sInitials As String)

Dim path As String
Dim q

On Error GoTo Error_Handler

path = “E:\LabelFileLocation\FileFolderLabel.label” ‘use the DYMO label template/file that you created.

Call CreateOLEObjects ‘create Dymo OLE objects

DymoAddIn.selectprinter (Application.Printer.DeviceName) ‘assuming default printer is a DYMO printer

DymoAddIn.Open path ‘open label in DLS

DymoLabels.SetField “PROJECTNAME”, sProjectName ‘PROJECTNAME and INITIALS are fields on the label template and sProjectName and sInitials are variables being passed to those label fields
DymoLabels.SetField “INITIALS”, sInitials

q = DymoAddIn.Print(1, True) ‘print the label

Call DestroyOLEObjects ‘ destroy objects

Error_Handler:

Select Case Err.Number
Case 0:
Exit Function
Case Else:
MsgBox “Error: ” & CStr(Err.Number) & ” : ” & Err.Description
End Select

End Function

Function CreateOLEObjects()

On Error GoTo Error_Handler

Set DymoAddIn = CreateObject(“Dymo.DymoAddIn”)
Set DymoLabels = CreateObject(“Dymo.DymoLabels”)
‘check if successful
If (DymoAddIn Is Nothing) Or (DymoLabels Is Nothing) Then
MsgBox “Unable to create DYMO connection objects”
End If

Error_Handler:

Select Case Err.Number
Case 0:
Exit Function
Case Else:
MsgBox “Error: ” & CStr(Err.Number) & ” : ” & Err.Description
End Select

End Function

Function DestroyOLEObjects()

On Error GoTo Error_Handler

Set DymoAddIn = Nothing
Set DymoLabels = Nothing

Error_Handler:

Select Case Err.Number
Case 0:
Exit Function
Case Else:
MsgBox “Error: ” & CStr(Err.Number) & ” : ” & Err.Description
End Select

End Function