Consulting

Results 1 to 2 of 2

Thread: Simulating Mail Merge from Excel to Word Using Bookmarks

  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

  2. #2
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    As posted, that code line refers to:
    ThisWorkbook.Path & "" & "Sobi AER Form - SP 01Jun2020_savebutton - TEST.docm"
    which is missing a path separator, vis:
    ThisWorkbook.Path & "/" & "Sobi AER Form - SP 01Jun2020_savebutton - TEST.docm"
    Regardless there are numerous other fundamental problems with your code.

    You might do better to actually automate a real mailmerge, for which see Run a Mailmerge from Excel, Sending the Output to Individual Files in the Mailmerge Tips and Tricks thread at: https://www.msofficeforums.com/mail-...ps-tricks.html
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

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
  •