How to Export Mail Merge PDF Individually Effortlessly

This tutorial guides you through automating the Mail Merge process in Microsoft Word using VBA (Visual Basic for Applications). Mail Merge is a powerful feature in Word that allows you to create personalized documents for multiple recipients using a single template. By using VBA, you can further enhance this process to automatically save each merged document as both a DOCX and a PDF file, all while keeping your files organized in a specific directory.

Here’s what you will achieve by the end of this tutorial:

• Set up your Word and Excel files for Mail Merge.
• Write and customize a VBA script to automate the Mail Merge process.
• Save each merged document individually in both DOCX and PDF formats.
• Optionally clean up temporary files to keep your directory tidy.

Let’s get started!

Step-by-Step Tutorial for Mail Merge with VBA in Word

Step 1: Ensure Files are in the Same Directory

1. Place the Master Document and Data Source File in the Same Directory:
   • Ensure that both your Word document (the master document) and your Excel file (the data source) are saved in the same folder. For example, both files could be located in A:\01-kuliah\2024-PELATIHAN-EDTECH\.

Step 2: Open the Master Document in Word

1. Open Microsoft Word:
   • Open the Word document that will serve as the master document for your mail merge.

Step 3: Open the VBA Editor

1. Open the VBA Editor:
   • Press ALT + F11 to open the Visual Basic for Applications (VBA) editor.

Step 4: Insert a New Module

1. Insert a Module:
   • In the VBA editor, go to Insert > Module. This will create a new module where you can write and store your VBA code.

Step 5: Copy and Paste the VBA Code

1. Copy the VBA Code:
   • Copy the following VBA code and paste it into the newly created module in the VBA editor:

Step 6: Customize the VBA Code

1. Customize the Folder and File Paths:
   • Modify FOLDER_SAVED and SOURCE_FILE_PATH constants to match your specific directory and file names.
2. Customize the SQL Statement:
   • If needed, adjust the SQL statement in .OpenDataSource to filter or modify the data being merged.
3. Customize the File Naming Convention:
   • Change how the files are named in the TargetDoc.SaveAs2 and TargetDoc.ExportAsFixedFormat lines based on your specific needs.

Step 7: Run the VBA Macro

1. Run the Macro:
   • In the VBA editor, press F5 or go to Run > Run Sub/UserForm to execute the macro.
2. Check the Output:
   • After running the macro, check the specified folder (e.g., A:\) to find the generated DOCX and PDF files.
3. Optional Clean-Up:
   • If you do not want to delete the DOCX files after creating the PDFs, you can comment out or remove the following lines in the code:

On Error Resume Next
Kill FOLDER_SAVED & "*.docx"
On Error GoTo 0

Notes

• Ensure that macros are enabled in Word. You might need to adjust your security settings to allow macros to run.
• Make sure that the Excel file is not open in another program when running the macro.
• This tutorial assumes that the data source Excel file has a sheet named sertifikat. Adjust the sheet name in the SQL statement if your data is in a different sheet.

By following these steps, you can automate the mail merge process in Word using VBA, saving each record individually as DOCX and PDF files.

Environments

• Windows 11 Pro
• Microsoft Excel 365 Enterprise
• Microsoft® Word for Microsoft 365 MSO (Version 2406 Build 16.0.17726.20078) 64-bit