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.
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