I am trying to do a VBA mailmerge to create certificates for my nonprofit that has leadership schools for youth. I have instructors across the country who use a scoring matrix to type in scores for all assessments. I want to make printing graduation certificates easier for them. I am new to macros but I have figured it out how to get word to open and create the certificates for the students. A leadership school can have anywhere between 10 and 100 students so I have enough rows to accommodate this on the spreadsheet. However, when the script is run, I can not figure out how to get it to skip blank cells. Can anyone help? Here is the code.
Code
Sub WordMailMerge()
'Step 1: Declare your variables
Dim wd As Word.Application
Dim wdDoc As Word.Document
Dim MyRange As Excel.Range
Dim MyCell As Excel.Range
Dim txtRank As String
Dim txtLastName As String
Dim lastRow As Long
'Step 2: Start Word and add a new document
Set wd = New Word.Application
Set wdDoc = wd.Documents.Add
wd.Visible = True
Dim txtFirstName As String
wdDoc.PageSetup.Orientation = wdOrientLandscape
'Step 3: Set the range of your contact list
Set MyRange = Sheets("Certificates").Range("A2:A121")
'Step 4: Start the loop through each cell
For Each MyCell In MyRange.Cells
'Step 5: Assign values to each component of the letter
txtRank = MyCell.Value
txtFirstName = MyCell.Offset(, 2).Value
txtLastName = MyCell.Offset(, 1).Value
'Step 6:Insert the structure of template document
wd.Selection.InsertFile _
ThisWorkbook.Path & "" & "certificate of graduation JLS.docx"
'Step 7: Fill each relevant bookmark with respective value
wd.Selection.Goto What:=wdGoToBookmark, Name:="Rank"
wd.Selection.TypeText Text:=txtRank
wd.Selection.Goto What:=wdGoToBookmark, Name:="FirstName"
wd.Selection.TypeText Text:=txtFirstName
wd.Selection.Goto What:=wdGoToBookmark, Name:="LastName"
wd.Selection.TypeText Text:=txtLastName
'Step 8: Clear any remaining bookmarks
On Error Resume Next
wdDoc.Bookmarks("Rank").Delete
wdDoc.Bookmarks("FirstName").Delete
wdDoc.Bookmarks("LastName").Delete
'Step 9: Go to the end, insert new page, and start with the next cell
wd.Selection.EndKey Unit:=wdStory
wd.Selection.InsertBreak Type:=wdPageBreak
Next MyCell
'Step 10: Set cursor to beginning and clean up memory
wd.Selection.HomeKey Unit:=wdStory
wd.Activate
Set wd = Nothing
Set wdDoc = Nothing
End Sub
Display More