Mail Merge Macro Doesn't Connect Refresh

[StephenBart]

I have built a excel loader to prepare estimates and populate customer proposals from estimates in a macro enabled excel workbook. The macro opens a mail merged word document from a path and filename based on the contents of cell values - and then creates a pdf of a section of the word document - which works fine.

But I need it to update or refesh the data in the mail merge data before it exports the pdf. Because it does not do this I have to manually perform the mail merge in word (i.e. Mailing -> Select Existing Recipients -> Use an Existing List -> then open the workbook (the same workbook I just launched the macro from) and select the table named 'a_Proposal_Loader$' and print the now refreshed section 4 as pdf.

Is there a way to automate the mail merge and refresh the data upon opening the word doc?

Can anyone help?

Here is my code

Sub PublishWalkthru()

    Dim WordApp As Word.Application
    Dim WordDoc As Word.Document
    Dim PDFsection As Word.Range
    Dim FileName As String
    Dim PDFfileName As String
    Dim Path As String
   
    Path = Range("D58").Value & "\"
    FileName = Range("D61").Value
    PDFfileName = Range("D62").Value
   
    'Set WordApp = CreateObject("Word.Application")
    Set WordApp = New Word.Application
   
    With WordApp
        .Visible = True
        .Activate
        Set WordDoc = .Documents.Open(FileName:=Path & FileName & ".docm")
    End With
   
    'Get Section 4 for exporting to PDF
   
    Set PDFsection = WordDoc.Sections(4).Range
   
    'If the last character in this section is a Section Break (Continuous or Next Page) (ASCII 12) then adjust the PDFsection range by 1 character to exclude it
   
    If Asc(PDFsection.Characters.Last) = 12 Then
        'PDFsection.Select  'the Selection includes the Section Break character
        Set PDFsection = WordDoc.Range(PDFsection.Start, PDFsection.End - 1)
        'PDFsection.Select   'the Selection now doesn't include the Section Break character
    End If
   
    PDFsection.ExportAsFixedFormat OutputFileName:=Path & PDFfileName & ".pdf", ExportFormat:=wdExportFormatPDF, OpenAfterExport:=True, Item:=wdExportDocumentContent

    PDFsection.Collapse wdCollapseStart
    PDFsection.Select
   
    WordApp.Windows(1).ScrollIntoView PDFsection, True

End Sub

 

[StephenBart]

Sorry - I should just mention that if I open the word document from (say) windows explorer it does pull the latest mail merge data from the table, but when the macro referenced above opens the same mail merged document it does not and I have to manual refresh each time?