RUMUS VBA WORD SIMPAN OTOMATIS MAIL MERGE DALAM BENTUK PDF SECARA OTOMATIS DAN MENGGUNAKAN PASSWORD

 Assalamu'alaikum warahmatullahi wabarakatuh

Kali ini saya menolak tuk lupa tentang rumus yang sangat bermanfaat jika kita ingin menyimpan secara otomatis file mail merge ke dalam format pdf, kemudian file pdf tersebut kita beri password secara otomatis juga. Namun sebelum menjalankan VBA silahkan download dulu PDF Toolkit untuk memaksimalkan penggunaan PDF. 

Download PDF Toolkit

Setelah install Toolkit, berikut langkah yang diperlukan :

1. siapkan file database mailing dan file mailing yang kalian perlukan

2. tekan alt+Fn+F11 untuk membuka VBA

3. Klik kanan pada judul file, klik insert, module, seperti gambar di bawah ini

4. ketik atau copas tulisan di bawah ini 

Option Explicit

Const FOLDER_SAVED As String = "D:\TUTORIAL\PART3\SuratTugas_"  'sesuaikan direktorinya
Const FOLDER_SAVED_TEMP As String = "D:\TUTORIAL\PART3\"
Const SOURCE_FILE_PATH As String = "D:\TUTORIAL\PART3\database.xlsx" 'sesuaikan direktorinya
 
Sub MailMergeToIndPDF()
Dim MainDoc As Document, TargetDoc As Document
Dim dbPath As String
Dim recordNumber As Long, totalRecord As Long
Dim fTemp As String
Dim oPdf As String
Dim Pwd As String
Dim cmdStr As String

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

                     
            Pwd = .DataSource.DataFields("Password").Value
            oPdf = FOLDER_SAVED & .DataSource.DataFields("Password").Value & "_" & .DataSource.DataFields("Nama").Value & ".pdf"
            fTemp = FOLDER_SAVED_TEMP & "Temp.Pdf"
            TargetDoc.ExportAsFixedFormat fTemp, exportformat:=wdExportFormatPDF
            TargetDoc.Close False
            
            fTemp = """" & fTemp & """"                                                                 'Putting extra "" around for command Parameter.
            oPdf = """" & oPdf & """"									'Download dulu PDFtoolkit di https://www.pdflabs.com/tools/pdftk-the-pdf-toolkit/
            Pwd = """" & Pwd & """"
                                                                    
            cmdStr = "pdftk " & fTemp & " Output " & oPdf & " User_pw " & Pwd & " Allow AllFeatures"    'Making Command String for making protected PDFs Using PDFtk tool.
    
            Shell cmdStr, vbHide                                                                        'Executing PDFtk Command.


            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
'with PDF Tool Kit to save PDF easch record individually Password
'adopted from https://www.excelforum.com/excel-programming-vba-macros/1069367-making-password-protected-pdfs-using-excel-vba-and-pdftk-tool.html
'thanks for Vikas_Gautam and  arusrikanth

5. tulisan yang berwarna dan pakai background silahkan di ganti sesuai dengan file yang dibuat

6. setelah semua sudah disesuaikan klik icon run pada toolbar VBA,

7. TARAAAA...... saksikan keajaiban yang terjadi 😉

SELAMAT MENCOBA, SEMOGA SUKSES