View Single Post
Old 12-13-2019, 03:15 PM
Trang Nguyen Trang Nguyen is offline Windows 10 Office 2016
Join Date: Dec 2019
Posts: 4
Trang Nguyen is on a distinguished road
Default Mail Merge Macro NOT Creating PDF Files

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:

'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), "_")
    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
Reply With Quote