Simulating Mail Merge from Excel to Word Using Bookmarks
1. Word and Excel v16
2. Attempting to assign specific cells in excel to bookmarks in a already created word doc.
3. What is bolded is where the code stops and turns yellow.
4. I have searched the site and can't find anything to help. I am very new to VBA.
Any help is gratefully appreciated.
Code:
Sub WordMailMerge()
Dim wd As Word.Application
Dim wdDoc As Word.Document
Dim MyRange As Excel.Range
Dim MyCell As Excel.Range
Dim txtPATID As String
'Open AE Form
Dim strFile As String
strFile = "J:\Pharmacovigilance\Kineret\Kineret AE reports- form\Sobi AER Form - SP 01Jun2020_savebutton - TEST.docm"
Documents.Open strFile
'Set the range of your contact list
Set MyRange = Sheets("321906_Kineret").Range("A4:A23")
'Start the loop through each cell
For Each MyCell In MyRange.Cells
'Assign values to each component of the letter
txtPATID = MyCell.Offset(, 1).Value
'Insert the structure of template document
wd.Selection.InsertFile _
ThisWorkbook.Path & "" & "Sobi AER Form - SP 01Jun2020_savebutton - TEST.docm"
'Fill each relevant bookmark with respective value
wd.Selection.GoTo What:=wdGoToBookmark, Name:="PATID"
wd.Selection.TypeText Text:=txtPATID
'Clear any remaining bookmarks
On Error Resume Next
wdDoc.Bookmarks("PATID").Delete
On Error GoTo 0
'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
'Set cursor to beginning and clean up memory
wd.Selection.HomeKey Unit:=wdStory
wd.Activate
Set wd = Nothing
Set wdDoc = Nothing
End Sub