Stop Saving Mail Merge PDFs One by One – Here’s the VBA Trick That Does It Instantly

Stop Print Mail Merge Satu-Satu! Pakai Kode VBA Ini, Otomatis Jadi PDF per File

Assalamu'alaikum, apa kabar pembaca setia?

Pernah ngalamin momen di mana kamu harus membuat ratusan surat atau dokumen yang isinya hampir sama, tapi beda nama dan alamat? Nah, itu namanya mail merge. Fitur andalan Microsoft Word untuk bikin surat masal dalam sekejap.

Masalahnya, kadang kita nggak cuma butuh mencetak di kertas, tapi juga menyimpannya sebagai file PDF per orang. Bayangkan kalau ada 200 data karyawan, lalu kamu harus save PDF satu per satu. Capek, kan? Belum lagi kalau salah satu nama kepencet, bisa kacau.

Di artikel ini, saya bakal kasih bocoran trik otomatis pakai VBA (Visual Basic for Applications) di Word. Dengan satu tombol, semua PDF-mu akan tercetak rapi per file sesuai nama atau ID. Tenang, saya jelasin pelan-pelan, kok. Nggak perlu jago coding!

Kenapa Kita Perlu Print Mail Merge ke PDF per File?

Buat yang belum ngerasain, ini masalah klasik di dunia administrasi. Misalnya:

• HR mau kirim slip gaji ke 500 karyawan via email. Mereka butuh file PDF masing-masing.
• Marketing ngirim penawaran ke banyak klien. Harus personal, beda nama dan perusahaan.
• Akademik bikin sertifikat atau ijazah untuk banyak siswa.

Kalau cuma pakai mail merge biasa, hasilnya bisa langsung cetak atau jadi satu file PDF besar. Nah, untuk pisahin jadi per file, banyak yang pakai jasa split PDF online. Sayangnya, cara itu sering mengubah nama file jadi "dokumen (1), dokumen (2)" dan seterusnya. Bukan nama orang atau ID. Ribet banget kalau mau diarsip atau dikirim ulang, kan?

Makanya, solusi paling jitu adalah mengotomatiskan proses dari dalam Microsoft Word itu sendiri. Dengan kode VBA, kita bisa atur Word untuk nyimpan setiap hasil mail merge jadi PDF dengan nama yang kita inginkan (misalnya "NamaKaryawan.pdf" atau "ID_001.pdf").

Ini Kode VBA-nya (Jangan Takut, Saya Bantu Bedah!)

Berikut adalah kode yang bisa kamu salin (atau lebih baik, ketik ulang supaya paham) ke dalam modul VBA di Microsoft Word. Saya ambil dari berbagai sumber, lalu saya sesuaikan biar gampang dipakai.

Option Explicit Const FOLDER_SAVED As String = "C:\Users\Namamu\Documents\Hasil PDF\" ' GANTI dengan folder tujuan Const SOURCE_FILE_PATH As String = "C:\Users\Namamu\Documents\DataMailMerge.xlsx" ' GANTI dengan path file Excel Sub MailMergeToIndPDF() Dim MainDoc As Document, TargetDoc As Document Dim recordNumber As Long, totalRecord As Long Set MainDoc = ActiveDocument With MainDoc.MailMerge ' Buka sumber data (file Excel) .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 ' Simpan sebagai PDF dengan nama dari kolom "ID" atau "Nama" TargetDoc.SaveAs FOLDER_SAVED & .DataSource.DataFields("Nama").Value & ".docx", wdFormatDocumentDefault TargetDoc.ExportAsFixedFormat FOLDER_SAVED & .DataSource.DataFields("Nama").Value & ".pdf", exportformat:=wdExportFormatPDF TargetDoc.Close False Set TargetDoc = Nothing Next recordNumber End With ' Hapus file .docx sementara (agar tidak berantakan) On Error Resume Next Kill FOLDER_SAVED & "*.docx" On Error GoTo 0 Set MainDoc = Nothing MsgBox "Selesai! Semua PDF sudah tersimpan di folder: " & FOLDER_SAVED End Sub

Penjelasan Kode:

• FOLDER_SAVED: Ganti dengan alamat folder di komputer kamu tempat menyimpan PDF.
• SOURCE_FILE_PATH: Ganti dengan alamat file Excel yang berisi data mail merge.
• .DataFields("Nama"): Bagian ini penting. "Nama" adalah judul kolom di Excel yang berisi nama orang. Ganti dengan "ID" atau "Kode" sesuai kebutuhan.
• Kode akan mengulang dari record pertama sampai terakhir, membuat dokumen baru, lalu menyimpannya sebagai PDF.

Langkah-Langkah Menggunakan Kode VBA Ini di Word

Oke, kalau kamu udah siap, ikuti langkah-langkah praktis berikut:

• Buka file Word yang berisi template surat atau dokumen mail merge-mu.
• Tekan Alt + F11 untuk membuka jendela VBA (Visual Basic Editor).
• Di menu, klik Insert > Module. Ini akan membuat modul kosong.
• Copy-paste kode di atas ke dalam modul tersebut.
• Sesuaikan 3 bagian penting: folder tujuan, path file Excel, dan nama kolom (misalnya "Nama" atau "ID").
• Tekan F5 atau klik tombol Run untuk menjalankan kode.
• Lihat keajaiban! Semua file PDF akan muncul di folder yang kamu tentukan, dengan nama sesuai data di Excel.

Catatan: Pastikan file Excel dan Word-mu sudah terhubung dengan benar untuk mail merge. Jika belum, buka dulu tab Mailings > Select Recipients untuk memilih sumber data.

Kesalahan Umum dan Cara Mengatasinya

Kadang, hal teknis selalu ada tantangannya. Ini beberapa hal yang sering terjadi dan cara mengatasinya:

• Error "Subscript out of range": Biasanya karena nama kolom di kode tidak sesuai dengan yang di Excel. Cek kembali ejaan kolom "Nama" atau "ID" di kode dan di file Excel-mu.
• Folder tidak ditemukan: Pastikan path folder yang kamu tulis benar, misalnya "C:\Users\Namamu\Documents\Hasil PDF\" dan folder tersebut benar-benar ada.
• Kode tidak berjalan: Pastikan makro diizinkan di Word. Buka File > Options > Trust Center > Trust Center Settings > Macro Settings, lalu pilih "Enable all macros" (untuk sementara, kemudian matikan lagi setelah selesai).
• PDF yang dihasilkan kosong: Periksa kembali data di Excel, pastikan tidak ada baris kosong di awal atau akhir.

Selamat Tinggal Kerja Manual, Halo Otomatisasi!

Dengan trik VBA ini, pekerjaan mencetak mail merge ke PDF per file akan terasa sangat ringan. Kamu bisa sambil minum kopi atau ngobrol santai, sementara komputer bekerja keras untukmu. Hebat, kan?

Saya ingat dulu pertama kali belajar VBA, saya juga pusing. Tapi setelah berhasil, rasanya sangat memuaskan. Semoga artikel ini bisa membantu kamu yang sedang mencari solusi. Kalau ada yang belum jelas, silakan tanyakan di kolom komentar. Saya usahakan jawab, meskipun mungkin nggak secepat kilat, ya.

Sekarang, giliran kamu untuk mencoba! Jangan lupa backup data sebelum menjalankan kode. Salam produktif!

Pertanyaan Seputar Mail Merge ke PDF per File

1. Apakah kode VBA ini bisa digunakan di Mac?
VBA di Word untuk Mac memang ada, tetapi kadang sedikit berbeda. Sebagian besar kode di atas seharusnya berfungsi, namun path folder harus menggunakan format Mac (contoh: "Macintosh HD:Users:Namamu:Documents").

2. Bagaimana jika saya ingin menyimpan dalam format lain selain PDF, misalnya DOCX?
Ganti bagian .ExportAsFixedFormat dengan .SaveAs dan format wdFormatDocumentDefault untuk menyimpan sebagai DOCX.

3. Apakah file Excel harus dibuka saat menjalankan kode?
Tidak perlu. Kode akan membuka sumber data secara otomatis di belakang layar.

4. Bisa tidak menyimpan hasil PDF dengan nama yang terdiri dari gabungan kolom, misalnya "Nama_Departemen"?
Bisa. Ubah bagian .DataFields("Nama").Value menjadi .DataFields("Nama").Value & "_" & .DataFields("Departemen").Value.

5. Apakah kode ini aman digunakan?
Kode ini relatif aman. Namun, selalu disarankan untuk mencoba pada file copy (duplikat) terlebih dahulu, untuk menghindari hal-hal yang tidak diinginkan.

Baca juga: Cara Membuat Mail Merge di Word dengan Mudah (Panduan Pemula)

Stop Saving Mail Merge PDFs One by One – Here’s the VBA Trick That Does It Instantly

Hey, have you ever been stuck creating hundreds of letters that are almost identical, except for names and addresses? That's mail merge—a lifesaver in Microsoft Word for bulk documents.

But here's the catch: sometimes you don't just want to print them on paper. You need to save each one as an individual PDF. Imagine 200 employee records, and you have to click "Save as PDF" for each one manually. Tedious, right? And if your finger slips, you might mix up the files.

In this article, I'm sharing a hidden trick using VBA (Visual Basic for Applications) inside Word. With a single click, all your PDFs will be neatly saved, each with its own proper name. Don't worry—I'll walk you through it step by step. No coding genius required.

Why Print Mail Merge to Individual PDFs?

For those who haven't experienced it, this is a classic admin headache. For example:

• HR wants to email payslips to 500 employees. They need separate PDFs for each person.
• Marketing sends personalised offers to many clients. Each must have the correct name and company.
• Schools create certificates for dozens of students.

With standard mail merge, you can either print directly or create one giant PDF file. To split them, many people use online PDF splitters. The drawback? The files are often renamed to "document (1)", "document (2)", etc.—not the person's name or ID. A nightmare for archiving or sending back out.

That's why the smartest solution is to automate from inside Microsoft Word itself. With a little VBA code, you can tell Word to save each mail merge result as a PDF with the filename you choose (e.g., "EmployeeName.pdf" or "ID_001.pdf").

The VBA Code (Don't Panic, I'll Explain It)

Here is the code you can copy (or better, type out yourself to understand it) into a VBA module in Microsoft Word. I've adapted it from various sources to make it simple.

Option Explicit Const FOLDER_SAVED As String = "C:\Users\YourName\Documents\PDF Results\" ' CHANGE to your folder Const SOURCE_FILE_PATH As String = "C:\Users\YourName\Documents\MailMergeData.xlsx" ' CHANGE to your Excel file path Sub MailMergeToIndPDF() Dim MainDoc As Document, TargetDoc As Document Dim recordNumber As Long, totalRecord As Long Set MainDoc = ActiveDocument With MainDoc.MailMerge ' Open data source (Excel file) .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 ' Save as PDF with filename from column "Name" or "ID" TargetDoc.SaveAs FOLDER_SAVED & .DataSource.DataFields("Name").Value & ".docx", wdFormatDocumentDefault TargetDoc.ExportAsFixedFormat FOLDER_SAVED & .DataSource.DataFields("Name").Value & ".pdf", exportformat:=wdExportFormatPDF TargetDoc.Close False Set TargetDoc = Nothing Next recordNumber End With ' Delete temporary .docx files On Error Resume Next Kill FOLDER_SAVED & "*.docx" On Error GoTo 0 Set MainDoc = Nothing MsgBox "Done! All PDFs are saved in: " & FOLDER_SAVED End Sub

Explanation:

• FOLDER_SAVED: Replace with the folder path on your computer where you want to save the PDFs.
• SOURCE_FILE_PATH: Replace with the path to your Excel file that contains the mail merge data.
• .DataFields("Name"): This is crucial. "Name" is the column header in Excel containing the person's name. Change it to "ID" or "Code" as needed.
• The code loops through each record, creates a new document, and saves it as a PDF.

How to Use This VBA Code in Word

Ready? Follow these practical steps:

• Open your Word document that contains the mail merge template.
• Press Alt + F11 to open the VBA editor.
• In the menu, click Insert > Module. A blank module will appear.
• Copy and paste the code above into the module.
• Adjust the three key parts: the destination folder, the Excel file path, and the column name (e.g., "Name" or "ID").
• Press F5 or click the Run button to execute the code.
• Watch the magic happen! All PDFs will appear in your folder, named according to your Excel data.

Note: Make sure your Word document is already connected to the Excel data source for mail merge. If not, go to the Mailings > Select Recipients tab to set it up first.

Common Errors and How to Fix Them

Technology can be moody. Here are some common hiccups and their fixes:

• Error "Subscript out of range": This usually means the column name in the code doesn't match the one in your Excel file. Double-check the spelling.
• Folder not found: Ensure the folder path you typed actually exists on your computer.
• Code doesn't run: You might need to enable macros. Go to File > Options > Trust Center > Trust Center Settings > Macro Settings, then select "Enable all macros" (temporarily). Remember to disable it afterward for security.
• PDFs are blank: Check your Excel data for any empty rows at the top or bottom.

Goodbye Manual Work, Hello Automation!

With this VBA trick, the task of printing mail merge to individual PDFs becomes incredibly light. You can sip your coffee or have a chat while your computer does the heavy lifting. How satisfying is that?

I remember when I first learned VBA, it felt like reading a foreign language. But the first time it worked, oh, the joy! I hope this article helps you find the solution you were looking for. If something's unclear, drop a comment below. I'll do my best to help (though maybe not at lightning speed).

Now it's your turn to try! Don't forget to back up your data before running the code. Stay productive!

FAQs About Saving Mail Merge to Individual PDFs

1. Does this VBA code work on a Mac?
VBA for Mac exists but can be slightly different. Most of the code should work, but folder paths must use Mac format (e.g., "Macintosh HD:Users:YourName:Documents").

2. What if I want to save in another format, like DOCX?
Replace the .ExportAsFixedFormat line with .SaveAs and use wdFormatDocumentDefault to save as DOCX.

3. Does the Excel file need to be open while the code runs?
No, the code opens it automatically behind the scenes.

4. Can I save the PDF with a name combining two columns, like "Name_Department"?
Yes, change .DataFields("Name").Value to .DataFields("Name").Value & "_" & .DataFields("Department").Value.

5. Is this code safe to use?
Yes, it's relatively safe. However, I always recommend testing it on a copy of your file first to avoid any surprises.

Read also: How to Create a Mail Merge in Word (Beginner's Guide)