Mail Merge Macro NOT Creating PDF Files

[Trang Nguyen]

Hello! I've been using a macro that does a mail merge to PDF files that I got from this column for years and I love it! However, I just tried to use it today and while the macro goes through all of the actions like it is creating the files, no PDF file is actually used. Below is the script that I used. I'm on Windows 10 running Office 2016. This macro has worked as recently as a few weeks ago. Has anyone run into this issue?



****Macro that I use************

Sub Merge_To_Individual_Files()
'Merges one record at a time to the folder containing the mailmerge main document.
' Sourced from: http://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html

'Application.ScreenUpdating = False 'turn of the screen refreshing
Dim StrFolder As String, StrName As String, MainDoc As Document, i As Long, j As Long
Const StrNoChr As String = """*./\:?|"
'
' use the active document with this code
'
Set MainDoc = ActiveDocument
With MainDoc
  StrFolder = .Path & Application.PathSeparator
'
' top of a loop to process the rows in the spreadsheet into documents
'
  For i = 1 To 75
    With .MailMerge
      .Destination = wdSendToNewDocument
      .SuppressBlankLines = True
      With .DataSource
        .FirstRecord = i
        .LastRecord = i
        .ActiveRecord = i
        If Trim(.DataFields("Last_Name")) = "" Then Exit For
        'StrFolder = .DataFields("Folder") & Application.PathSeparator
'
' Build the filename from the spreadsheet merge fields
'
        StrName = .DataFields("Manager_Name") & " " & .DataFields("Last_Name") & "_" & .DataFields("First_Name") & " - 2020 Bonus Adjustment"
      End With
      .Execute Pause:=False
    End With
'
' Remove non-filename characters from the built name
'
      For j = 1 To Len(StrNoChr)
        StrTxt = Replace(StrName, Mid(StrNoChr, j, 1), "_")
      Next
    StrName = Trim(StrName)

'
' save the file to the created name
'
    With ActiveDocument
      .SaveAs FileName:=StrFolder & StrName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
      .Close SaveChanges:=False
    End With

'bottom of a loop to process the rows in the spreadsheet into documents

  Next i

End With


Application.ScreenUpdating = True
End Sub

[macropod]

Your modifications to the code restrict it to just the first 75 records. If you've filtered those out, there'll be no output.

[Trang Nguyen]

Hi Macropod, they were not filtered out. There are only 75 records in the data merge file.

[macropod]

Is any of your "Last_Name" fields in the data source empty? If so, the code will exit at that point.

PS: The following is a more efficient version of your code:

Sub Merge_To_Individual_Files()
'Merges one record at a time to the folder containing the mailmerge main document.
' Sourced from: http://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html
Application.ScreenUpdating = False 'turn off the screen refreshing
Dim StrFolder As String, StrName As String, MainDoc As Document, i As Long, j As Long
Const StrNoChr As String = """*./\:?|"
'
' use the active document with this code
Set MainDoc = ActiveDocument
With MainDoc
  StrFolder = .Path & Application.PathSeparator
  With .MailMerge
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True '
    ' top of a loop to process the rows in the spreadsheet into documents
    For i = 1 To 75
      With .DataSource
        .FirstRecord = i
        .LastRecord = i
        .ActiveRecord = i
        If Trim(.DataFields("Last_Name")) = "" Then Exit For
        'StrFolder = .DataFields("Folder") & Application.PathSeparator
        '
        ' Build the filename from the spreadsheet merge fields
        '
        StrName = .DataFields("Manager_Name") & " " & .DataFields("Last_Name") & "_" & .DataFields("First_Name")
      End With
      .Execute Pause:=False
      '
      ' Remove non-filename characters from the built name
      For j = 1 To Len(StrNoChr)
        StrTxt = Replace(StrName, Mid(StrNoChr, j, 1), "_")
      Next
      StrName = Trim(StrName)
      '
      ' save the file to the created name
      With ActiveDocument
        .SaveAs FileName:=StrFolder & StrName & " - 2020 Bonus Adjustment.pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
        .Close SaveChanges:=False
      End With
    'bottom of a loop to process the rows in the spreadsheet into documents
    Next i
  End With
End With
Application.ScreenUpdating = True ' restore the screen refreshing
End Sub

[Trang Nguyen]

No, none of the Last_Name fields were blank. Thanks for the more efficient code!

UPDATE: I turns out that Adobe Acrobat Pro DC does not play nicely with this macro. I uninstalled it and reinstalled Acrobat Standard and it works perfectly. If there is a macro that works with Acrobat Pro DC or if there is a way to automate mail merges using Acrobat Pro DC, let me know!

[macropod]

I have Adobe Acrobat Pro (not DC) installed on my system and it doesn't interfere with the code. Perhaps your installation was configured to replace Word's built-in PDF save functionality?

[Trang Nguyen]

I'm not sure. I will look into that.