VBA Mail Merge – Create Stickers Using Excel and Word Mail Merge

In this tutorial, We will go through the process of generating label stickers using Excel VBA and Word Mail Merge.

Creating Data File

Let’s have data in excel sheet like this. Save this file as “Label Tool.xlsm”

VBA Mail Merge

Creating A Word Template

This is pretty easy. let’s follow these steps

  1. Open MS Word with a blank document
  2. Insert a table on first page and resize it so that it fits on entire page. see the screenshot below –
    VBA Mail Merge
  3. Or, if you know your physical Label sheet size you can insert the label from “Start Mail Merge” in Mailings optionsVBA Mail Merge
  4. Purpose is to create the sections, so that we can insert our records here by using Mail Merge And fit this table so that it can print perfectly on the physical label sheet. if you insert table. you might need to strugle adjusting it to print perfectly on label sheet.
  5. Go to Mailings Option — >  Select Recipients –> Use an existing List, it will ask to select a file. Select “Label Tool.xlsm” file we created in the first step. it will ask to select a sheet, Select the sheet you have your data on.
  6. Go to Mailings Option, Insert the fields in word table cells like showing below.VBA Mail Merge
  7. Notice that, After first cell, every cells have “<<Next Record>>”, You can insert this from “Rules” in Mailing OptionsVBA Mail Merge
  8. Now save this file with “Label Template.docx” and close the file.
  9. Now Open up your “Data.xlsm” file and let’s do some coding here.

Wrting VBA Mail Merge Program

Here is the code that we will insert in our Excel file, Important thing to remember is that we need to save both files at the same location.

Sub DoMailMerge(strFileName As String, strTemplate As String)

Dim objWord As Object
Dim objWD As Object
Dim objDoc As Object
Dim strPath As String
Dim strDataSource As String

On Error Resume Next
Set objWord = GetObject(, “Word.Application”)
If objWord Is Nothing Then
      Set objWord = CreateObject(“Word.Application”)
End If
On Error GoTo 0
objWord.Visible = False
On Error GoTo Err:

strPath = ThisWorkbook.Path & Application.PathSeparator & “Labels ” & Format(Now, “MMDDYYYYHHMMSS”)

strDataSource = strFileName
If objWord Is Nothing Then Exit Sub
Set objWD = objWord.Documents.Open(strTemplate)
With objWD.MailMerge
      .OpenDataSource Name:=strDataSource, _
      Connection:= _
      “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & strTemplate & “;Mode=Read;Extended Properties=””HDR=YES;IMEX=1;””;JetOLEDB:System database=””””;Jet OLEDB:Registry Path=””””;” _
, SQLStatement:=”SELECT * FROM `Data$`”
      .Destination = 0 ‘– 1 = Print 0 = New Document
      .Execute
End With

For Each objDoc In objWord.Documents
      If objDoc.Name Like “Form Lett*” Then
            Exit For
      End If
Next objDoc

objDoc.SaveAs2 strPath
objDoc.ExportAsFixedFormat Replace(strPath, “.docx”, “.pdf”), 17
objWD.Close False
objWord.Quit
Application.Wait Now + TimeValue(“00:00:01”)
Kill strPath & “.docx”

End Sub

Now, lets write a procedure that will call the above procedure.

Sub Main()
      Dim strTemplate As String
      strTemplate = ThisWorkbook.Path & Application.PathSeparator & “Label Template.docx”
      If Dir(strTemplate) = “” Then
            MsgBox “Sorry, We couldn’t find MS Word template here ” & vbCrLf & vbCrLf & strTemplate,vbCritical
            Exit Sub
      End If
      DoMailMerge ThisWorkbook.FullName, strTemplate
End Sub

And thats it!!, Now your macro is ready to print the lable in Pdf file, that you can print on physical label sheet.

Download a working file from here

Label-tool.zip (7 downloads)

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *