Membuat PDF dari Mail Merge dengan VBA

 Bahan Mail Merge :

1. Dokumen Ms.Word
2. Dokumen Ms.Excel (database mail merge)

Cara :

1. Buka dokumen Ms.Word mail merge
2. Koneksikan dengan database Excel mail merge
3. Ketik alt + F11
4. Insert - Module
5. Copas skrip di bawah ini, sesuaikan Folder output dan Folder Excel nya
6. Klik Run
7. Baris 38-40 berfungsi untuk menghapus dokumen .docx di folder output

	Option Explicit
	Const FOLDER_SAVED As String = "D:\Mail merge\PDF\"
	Const SOURCE_FILE_PATH As String = "D:\Mail merge\database.xlsx"
	Sub MailMergeToIndPDF()
	Dim MainDoc As Document, TargetDoc As Document
	Dim dbPath As String
	Dim recordNumber As Long, totalRecord As Long
	Set MainDoc = ActiveDocument
	With MainDoc.MailMerge
	'// if you want to specify your data, insert a WHERE clause in the SQL statement
	.OpenDataSource Name:=SOURCE_FILE_PATH, sqlstatement:="SELECT * FROM [Sheet1$]"
	totalRecord = .DataSource.RecordCount
	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 & .DataSource.DataFields("Nomor").Value & ".docx", wdFormatDocumentDefault
	TargetDoc.ExportAsFixedFormat FOLDER_SAVED & .DataSource.DataFields("Nomor").Value & ".pdf", exportformat:=wdExportFormatPDF
	TargetDoc.Close 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 https://learndataanalysis.org/automate-mail-merge-to-save-each-record-individually-with-word-vba/
	'with additional delete docx file

view raw mail-merge-pdf-vba hosted with ❤ by GitHub

Sumber : dari sini