PDFs created by Excel Mail merge macro does not change merge fields

I have copied a macro (credits: MailMerge Excel to Word individual files) into Excel where I can mail-merge data from Excel into Word Letter automatically and save the individual files as a pdf in the folder.

Unfortunately my PDFs do not contain any content of the Excel list after using the macro, but stick to the mail merge field name. This applies to all my created files.

Additionally, I would like to use the first line as controller, so I can decide which line is merged (e.g. with an "x" in the first line).

Can someone help me in both cases? Especially my first problem feels like a small mistake, but after hours of searching I gave up.. :-(

Thank you for your help.

 Sub RunMailMerge()

 Dim wdOutputName, wdInputName, PDFFileName As String
 Dim x As Integer
 Dim nRows As Integer

wdInputName = ThisWorkbook.Path & "\Letter.docx"
Const wdFormLetters = 0, wdOpenFormatAuto = 0
Const wdSendToNewDocument = 0, wdDefaultFirstRecord = 1, wdDefaultLastRecord = 3

'This will get you the number of records "-1" accounts for header
nRows = Sheets("Overview").Range("B" & Rows.Count).End(xlUp).Row - 1

' open the mail merge layout file
Dim wdDoc As Object

Set wdDoc = GetObject(wdInputName, "Word.document")

wdDoc.Application.Visible = False


With wdDoc.MailMerge
     .MainDocumentType = wdFormLetters
     .Destination = wdSendToNewDocument
     .SuppressBlankLines = True
      With .DataSource
        .FirstRecord = wdDefaultFirstRecord
        .LastRecord = wdDefaultLastRecord
    End With
     .Execute Pause:=False
End With

For x = 1 To nRows
  With wdDoc.MailMerge.DataSource
     .ActiveRecord = x
     If .ActiveRecord > .LastRecord Then Exit For
  End With

' show and save output file

'cells(x+1,2)references the first cells starting in row 2 and increasing by 1 row with each loop
PDFFileName = ThisWorkbook.Path & "\Letter - " & Sheets("Overview").Cells(x + 1, 2) & ".pdf"

wdDoc.Application.Visible = False
wdDoc.ExportAsFixedFormat PDFFileName, 17   ' This line saves a .pdf-version of the mail merge

Next x

' cleanup
wdDoc.Close SaveChanges:=False
Set wdDoc = Nothing

MsgBox "Your pdf('s) has now been saved!"

End Sub

Solution

By adding the following macro to your workbook, you can generate one PDF output file per mailmerge record.

Sub RunMailMerge()
'Note: A VBA Reference to the Word Object Model is required, via Tools|References
Dim wdApp As New Word.Application, wdDoc As Word.Document
Dim StrFolder As String, StrName As String, i As Long, j As Long
Dim strWorkbookName As String: strWorkbookName = ThisWorkbook.FullName
Const StrNoChr As String = """*./\:?|": StrName = "Letter.docx"
StrFolder = ThisWorkbook.Path & Application.PathSeparator
If Dir(StrFolder & strDocNm) = "" Then Exit Sub
With wdApp
  'Disable alerts to prevent an SQL prompt
  .DisplayAlerts = wdAlertsNone
  'Display Word - change this to False once the code is running correctly
  .Visible = True
  'Open the mailmerge main document - set Visible:=True for testing
  Set wdDoc = .Documents.Open(Filename:=StrFolder & StrName, ReadOnly:=True, AddToRecentFiles:=False, Visible:=False)
  With wdDoc
    With .MailMerge
      'Define the mailmerge type
      .MainDocumentType = wdFormLetters
      'Define the output
      .Destination = wdSendToNewDocument
      .SuppressBlankLines = True
      'Connect to the data source
      .OpenDataSource Name:=strWorkbookName, ReadOnly:=True, _
        LinkToSource:=False, AddToRecentFiles:=False, _
        Format:=wdOpenFormatAuto, _
        Connection:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "User ID=Admin;Data Source=strWorkbookName;" & _
        "Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
        SQLStatement:="SELECT * FROM `Overview$`  WHERE `Filter` = 'x'", _
        SubType:=wdMergeSubTypeAccess
        'Process all eligible records
        For i = 1 To .DataSource.RecordCount
          With .DataSource
            .FirstRecord = i
            .LastRecord = i
            .ActiveRecord = i
            'Exit if the field to be used for the filename is empty
            If Trim(.DataFields("Name")) = "" Then Exit For
            'StrFolder = .DataFields("Folder") & Application.PathSeparator
            StrName = .DataFields("Name")
          End With
        .Execute Pause:=False
        'Clean up the filename
        For j = 1 To Len(StrNoChr)
          StrName = Replace(StrName, Mid(StrNoChr, j, 1), "_")
        Next
        StrName = "Letter - " & Trim(StrName)
        'Save as a PDF
        wdApp.ActiveDocument.SaveAs Filename:=StrFolder & StrName & ".pdf", _
          FileFormat:=wdFormatPDF, AddToRecentFiles:=False
        wdApp.ActiveDocument.Close SaveChanges:=False
      Next i
      'Disconnect from the data source
      .MainDocumentType = wdNotAMergeDocument
    End With
    'Close the mailmerge main document
    .Close False
  End With
  'Restore the Word alerts
  .DisplayAlerts = wdAlertsAll
  'Exit Word
  .Quit
End With
Set wdDoc = Nothing: Set wdApp = Nothing
End Sub

As coded, files are saved to the same folder as the mailmerge main document, using what is assumed to be the 'Name' field in the data source for the filenames (change this to suit your actual field name).

Illegal filename characters (i.e. "*./:?|) are replaced with underscores.

It's not clear what you mean by "I would like to use the first line as controller, so I can decide which line is merged". That said, if you're referring to a column with the 'x' entries, you can use a mailmerge filter to include or exclude those records. The macro assumes the field you're filtering on is named 'Filter' and you want to process those records with a lower-case 'x' against them. Change the details in the SQLStatement line to suit.

Note the comments re adding a Word library reference and re visibility in the code.