Uncategorized

Mail Merge to PDF

Ever Wondered how Fantastic it would be if Mail Merge Functionality Provided option to create separate Pdf Files.So this Idea Popped to Me When i was Delivering a lecture and a Candidate asked me if it is possible to make a pdf directly and save it on local disk with appropriate name based on a criteria in List of mail merge.

I had Earlier Worked for Creating pdf file on a vast range or excel templates and also on many commercial projects. But Looping this Whole System to create a whole set of Pdf wherein data can be as heavy as 25000+ lines of mail merge.

So I Started off with finding a solution withing excel and ms word as it would have been a very common task for any person using mail merge facility but best bet what i could find was send it via email to multiple receipient hence i thought of modifying old VBA code and creating a Fresh Code For this Purpose

VBA Code

 

 

Sub MailMergeToPdfBasic()
' Last Updated 2021-12-09
' Author Vinit Furia
outputfolder = InputBox("Path To Save Files", "Vinit Furia")
Dim masterDoc As Document, singleDoc As Document, lastRecordNum As Integer

Set masterDoc = ActiveDocument

masterDoc.MailMerge.DataSource.ActiveRecord = wdLastRecord
lastRecordNum = masterDoc.MailMerge.DataSource.ActiveRecord

masterDoc.MailMerge.DataSource.ActiveRecord = wdFirstRecord

Do While lastRecordNum > 0

masterDoc.MailMerge.Destination = wdSendToNewDocument
masterDoc.MailMerge.DataSource.FirstRecord = masterDoc.MailMerge.DataSource.ActiveRecord
masterDoc.MailMerge.DataSource.LastRecord = masterDoc.MailMerge.DataSource.ActiveRecord
masterDoc.MailMerge.Execute False

Set singleDoc = ActiveDocument




singleDoc.ExportAsFixedFormat _
OutputFileName:=outputfolder & Application.PathSeparator & _
masterDoc.MailMerge.DataSource.DataFields("GSTIN").Value & ".pdf", _
ExportFormat:=wdExportFormatPDF

singleDoc.Close False

If masterDoc.MailMerge.DataSource.ActiveRecord >= lastRecordNum Then
lastRecordNum = 0
Else
masterDoc.MailMerge.DataSource.ActiveRecord = wdNextRecord
End If

Loop
End Sub

Here in The Following Code GSTIN is Field Which will Determine the Name of the pdf File.