Excel Workbook to Word Template + Mail merge VBA

Question

Hello and Thanks in Advance!

I have been trying for a while to automate something that I do on daily basis, Which is to Mail Merge from an Excel Workbook Which updates every day with new registrations using VBA, The purpose I need the script is that it has to produce a new mail with the same Template for only checked rows in the Checklist I have created when it's finished it also needs to create labels.

How it looks:

The file contains 8 columns in which the data is stored(name, gender, age, doctor's name, address, city, hospital, checkbox(v) all at Sheet1.

What I tried:

I created A VBA Script that adds a checklist in row G and only the checked rows are extracted into a new sheet(Sheet2) this way it isolates only the needed info from the table.

I tried to search online for a code but wasn't successful in finding any that even close to what I asked.

Goal:

Excel - Extracting the Data from the second sheet(Sheet2)

Word - Getting the info from the excel table(sheet2) and mail merge into a template via script.

I'm kinda new in VBA so sorry if I asked too much and hope you will help!

So whats the issue? Your VBA code is throwing an error? or works but not as expected? Also, please provide the code that you have — Zac
I might have been vague, I will clarify I didn't succeded in writing a working code that will resolve this problem, I only wrote some parts that should simplify the process of resolving it (I need to grab the info in the second spreadsheet(excel) and using mail merge insert the info into a word template(which is a letter).) @Zac — Mougatsu
The only way to help with this is by showing your code. It's ok to have code that's not working! That allows for us to see what you have tried, and users can then ask more questions or provide answers with what can be fixed or changed. — Robert Todar
Hey @RobertTodar I didn't write and I couldn't even get started, I watched couple of videos on the subject but couldn't get myself even to write the basic code because of the complexity of the task. So I asked if someone can solve this problem — Mougatsu

Scott Cannon Scott Cannon · Accepted Answer · 2021-01-14T20:42:38

I am working on something very similar to what this post seems to be talking about. I have managed to import the data into word from excel but can't find a way to get the data from the array I stored it in to the specific merge field locations I need.

I need to generate 10 labels per sheet of paper in two columns If I mail merge it I can get the left columns. but the way "next record" functions screws up everything if I try to do the right side records because it will switch to the next record without any way to go back and finish the first label.

Sub OpenExcelFile()


    Dim oExcel As Excel.Application
    Dim oWB As Workbook
    Dim RowLoc As Integer
    Dim ExcelArray(1 To 10000, 1 To 6) As Variant

    RowLoc = 1

    Set oExcel = New Excel.Application
    Set oWB = oExcel.Workbooks.Open("C:\Users\UserName\Documents\Sticker Maker.xlsm")
    oExcel.Visible = True
Do While oWB.Sheets("Barcode").Cells(RowLoc, 1) <> ""
    ExcelArray(RowLoc, 1) = oWB.Sheets("Barcode").Cells(RowLoc, 1)
    ExcelArray(RowLoc, 2) = oWB.Sheets("Barcode").Cells(RowLoc, 2)
    ExcelArray(RowLoc, 3) = oWB.Sheets("Barcode").Cells(RowLoc, 3)
    ExcelArray(RowLoc, 4) = oWB.Sheets("Barcode").Cells(RowLoc, 4)
    ExcelArray(RowLoc, 5) = oWB.Sheets("Barcode").Cells(RowLoc, 5)
    ExcelArray(RowLoc, 6) = oWB.Sheets("Barcode").Cells(RowLoc, 6)
    RowLoc = RowLoc + 1
Loop

    RowLoc = 1
    ActiveDocument.MailMerge.DataSource.DataFields("Job_Name").Value = ExcelArray(RowLoc, 1) 'this fails because it's read only
    oWB.Close
    Excel.Application.Quit
End Sub

I just want to find SOME way to fill out these labels for every record in the file creating new pages ever 10 labels.

Excel Workbook to Word Template + Mail merge VBA

1 Answers