Bfree
02-09-2021, 12:20 PM
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.
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
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.
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