Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-13-2019, 03:15 PM
Trang Nguyen Trang Nguyen is offline Mail Merge Macro NOT Creating PDF Files Windows 10 Mail Merge Macro NOT Creating PDF Files Office 2016
Novice
Mail Merge Macro NOT Creating PDF Files
 
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************

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 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
Reply With Quote
  #2  
Old 12-13-2019, 07:34 PM
macropod's Avatar
macropod macropod is offline Mail Merge Macro NOT Creating PDF Files Windows 7 64bit Mail Merge Macro NOT Creating PDF Files Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,497
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

Your modifications to the code restrict it to just the first 75 records. If you've filtered those out, there'll be no output.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 12-16-2019, 07:50 AM
Trang Nguyen Trang Nguyen is offline Mail Merge Macro NOT Creating PDF Files Windows 10 Mail Merge Macro NOT Creating PDF Files Office 2016
Novice
Mail Merge Macro NOT Creating PDF Files
 
Join Date: Dec 2019
Posts: 4
Trang Nguyen is on a distinguished road
Default

Hi Macropod, they were not filtered out. There are only 75 records in the data merge file.
Reply With Quote
  #4  
Old 12-16-2019, 02:01 PM
macropod's Avatar
macropod macropod is offline Mail Merge Macro NOT Creating PDF Files Windows 7 64bit Mail Merge Macro NOT Creating PDF Files Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,497
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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:
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
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 12-16-2019, 02:07 PM
Trang Nguyen Trang Nguyen is offline Mail Merge Macro NOT Creating PDF Files Windows 10 Mail Merge Macro NOT Creating PDF Files Office 2016
Novice
Mail Merge Macro NOT Creating PDF Files
 
Join Date: Dec 2019
Posts: 4
Trang Nguyen is on a distinguished road
Default

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!
Reply With Quote
  #6  
Old 12-16-2019, 02:18 PM
macropod's Avatar
macropod macropod is offline Mail Merge Macro NOT Creating PDF Files Windows 7 64bit Mail Merge Macro NOT Creating PDF Files Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,497
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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?
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 12-16-2019, 02:20 PM
Trang Nguyen Trang Nguyen is offline Mail Merge Macro NOT Creating PDF Files Windows 10 Mail Merge Macro NOT Creating PDF Files Office 2016
Novice
Mail Merge Macro NOT Creating PDF Files
 
Join Date: Dec 2019
Posts: 4
Trang Nguyen is on a distinguished road
Default

I'm not sure. I will look into that.
Reply With Quote
Reply

Tags
macro, mail merge help

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating Template - Macro/Mail Merge/VBA? mwilburn01 Word 3 09-13-2018 05:24 AM
Mail Merge Macro NOT Creating PDF Files Mail Merge - Creating customer account Georgina Owen Mail Merge 5 05-18-2018 03:02 PM
Mail Merge Macro NOT Creating PDF Files Creating Hyperlinks after a Mail Merge jeffreybrown Mail Merge 4 09-19-2017 06:35 PM
Creating new documents from a mail merge template pnjcarter Mail Merge 1 02-03-2014 06:42 PM
Need help creating a data base for mail merge. acedking90 Mail Merge 0 07-27-2009 11:04 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 01:29 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft