Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #5  
Old 01-04-2021, 02:57 PM
Angel21 Angel21 is offline Using Excel data to automatically make Word documents Windows Vista Using Excel data to automatically make Word documents Office 2010
Novice
 
Join Date: Jan 2021
Posts: 1
Angel21 is on a distinguished road
Default Mail Merge Docs Save as pdf - Code in VBA

Quote:
Originally Posted by macropod View Post
To create the individual PDFs, you could drive the merge with a macro like:
Code:
Sub Merge_To_Individual_Files()
'Merges one record at a time to the chosen output folder
Application.ScreenUpdating = False
Dim StrFolder As String, StrName As String, MainDoc As Document, i As Long, j as long
Set MainDoc = ActiveDocument
With MainDoc
  StrFolder = .Path & "\"
  For i = 1 To .MailMerge.DataSource.RecordCount
    With .MailMerge
      .Destination = wdSendToNewDocument
      .SuppressBlankLines = True
      With .DataSource
        .FirstRecord = i
        .LastRecord = i
        .ActiveRecord = i
        If Trim(.DataFields("Last_Name")) = "" Then Exit For
        StrName = .DataFields("Last_Name") & "_" & .DataFields("First_Name")
      End With
      .Execute Pause:=False
    End With
    For j = 1 To 255
      Select Case j
        Case 1 To 31, 33 To 45, 47, 58 To 64, 91 To 94, 96, 123 To 141, 143 To 149, 152 To 157, 160 To 180, 182 To 191
        StrName = Replace(StrName, Chr(j), "")
      End Select
    Next
    StrName = Trim(StrName)
    With ActiveDocument
      .SaveAs2 FileName:=StrPath & StrName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
      .Close SaveChanges:=False
    End With
  Next i
End With
Application.ScreenUpdating = True
End Sub
Note: the above macro is coded to get the filenames from two of the data fields, via the line:
StrName = .DataFields("Last_Name") & "_" & .DataFields("First_Name")
You will need to change that lines to reference the data field(s) you want to use.
The output PDFs will be saved in the same folder you keep your mailmerge main document in.
Hi

Please can you let me how to change the code to save the pdfs file in a location of my choice

Thanks
 



Similar Threads
Thread Thread Starter Forum Replies Last Post
Using Excel data to automatically make Word documents How to automatically sum in excel for filtered data msi_g Excel 2 08-05-2013 05:29 AM
Using Excel data to automatically make Word documents How to create an Excel add in that will automatically build tables from data selvamariappan Excel Programming 1 12-12-2011 03:11 AM
Using Excel data to automatically make Word documents How to copy automatically data from Excel file to Word file? fuchsd Word 6 10-25-2011 05:52 AM
Using Excel data to automatically make Word documents Old Excel Document folder automatically created in My Documents slickcondo Office 6 07-13-2011 02:31 AM
Automatically entering/fill data in cells in Excel 2003 dipdog Excel 0 08-17-2006 08:37 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:31 AM.


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