Scribd
Upload
54 views2 pages

Mail Merge Automation with VBA

This document contains VBA code to perform a mail merge from an Excel database file into individual PDF documents. The code opens the Excel data source, loops through each record, performs the mail merge to a new document, saves that as a PDF file named after a field in the record, closes the document, and deletes the temporary DOC files. It provides constants to define the data source path, folder path, and file naming convention used in the mail merge and document export process.

Uploaded by

BK ID
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
54 views2 pages

Mail Merge Automation with VBA

This document contains VBA code to perform a mail merge from an Excel database file into individual PDF documents. The code opens the Excel data source, loops through each record, performs the mail merge to a new document, saves that as a PDF file named after a field in the record, closes the document, and deletes the temporary DOC files. It provides constants to define the data source path, folder path, and file naming convention used in the mail merge and document export process.

Uploaded by

BK ID
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Option Explicit

Const FOLDER_SAVED As String = "D:\Test\" 'sesuaikan direktorinya

Const SOURCE_FILE_PATH As String = "D:\Test\[Link]" 'sesuaikan direktorinya

Sub MailMergeToIndPDF()

Dim MainDoc As Document, TargetDoc As Document

Dim dbPath As String

Dim recordNumber As Long, totalRecord As Long

Set MainDoc = ActiveDocument

With [Link]

'// if you want to specify your data, insert a WHERE clause in the SQL statement

.OpenDataSource Name:=SOURCE_FILE_PATH, sqlstatement:="SELECT * FROM [Olah$]"

totalRecord = .[Link]

For recordNumber = 1 To totalRecord

With .DataSource

.ActiveRecord = recordNumber

.FirstRecord = recordNumber

.LastRecord = recordNumber

End With

.Destination = wdSendToNewDocument

.Execute False
Set TargetDoc = ActiveDocument

TargetDoc.SaveAs2 FOLDER_SAVED & .[Link]("Nama").Value & ".docx",


wdFormatDocumentDefault 'sesuaikan dengan field yang akan dijadikan format penamaan

[Link] FOLDER_SAVED & .[Link]("Nama").Value &


".pdf", exportformat:=wdExportFormatPDF 'sesuaikan dengan field yang akan dijadikan format
penamaan

[Link] False

Set TargetDoc = Nothing

Next recordNumber

End With

On Error Resume Next

Kill FOLDER_SAVED & "*.docx"

On Error GoTo 0

Set MainDoc = Nothing

End Sub

'adopted from [Link]


with-word-vba/

'with additional delete docx file

You might also like