Consulting

Results 1 to 2 of 2

Thread: Simulating Mail Merge from Excel to Word Using Bookmarks

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Newbie
    Joined
    Feb 2021
    Posts
    1
    Location

    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.

    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
    Last edited by macropod; 02-09-2021 at 04:40 PM. Reason: Added code tags & formatting

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •