VBA & Macro Problems

How to Create Separate Mail Merge PDFs with Custom Names Using Word VBA

Maira Mehtab Sep 21, 2026 872 views

Question details

The user needs a Word VBA mail merge macro to save each merged record as individual DOCX and PDF files, utilizing specific spreadsheet fields for custom folder paths and filenames.

Product: Word
Device & OS: not provided
Scenario: Automating a document mail merge to output individual, uniquely named files based on data extracted from a connected spreadsheet.
Observed behavior: The goal is to properly configure the spreadsheet formats and macro logic to accurately parse fields like DocFolderPath and PdfFileName to save files in the correct directories.

Before you start

Ensure your data source spreadsheet contains exact header matches for your VBA script (e.g., DocFolderPath, PdfFileName) and that all specified target folders already exist on your local drive.

Solution 1Recommended

Format Spreadsheet Columns as General and Verify Macro Logic

Changing column formats prevents VBA from misreading text strings and ensures smooth extraction of custom filenames and paths.

VBA macros often fail to read data fields accurately during a mail merge if the source spreadsheet columns are formatted strictly as 'Text'. Reformatting these to 'General' resolves most read-errors.

Open Data Source

Open your data source spreadsheet in Excel or WPS Spreadsheet.

Select Target Columns

Highlight the columns containing your file paths and custom names (e.g., DocFolderPath, DocFileName, PdfFolderPath, PdfFileName).

Change Cell Format

Right-click the highlighted area, select 'Format Cells', navigate to the 'Number' tab, and choose 'General' instead of 'Text'.

Test with Sanitized Data

Save the spreadsheet. Run the Word VBA macro again with a small, sanitized test batch to verify that the field names, cell references, and output paths are functioning correctly.

Debugging Tip: If errors persist, create a dummy spreadsheet with only one or two records to isolate whether the issue lies in the VBA code logic or the spreadsheet data formatting.

Efficient Mail Merge Automation

Automate Mail Merges and PDFs with WPS Office

WPS Office provides robust support for VBA macros, allowing you to seamlessly run complex mail merges, generate individual PDFs, and automate bulk document creation workflows.

1. Prepare the Main Document: Open your template document in WPS Writer and navigate to the 'Mailings' tab to begin setting up your merge.
2. Connect Data Source: Click 'Open Data Source' to link your formatted spreadsheet containing the custom file paths and names.
3. Insert VBA Code: Press Alt + F11 to open the built-in WPS Macro Editor and paste your mail merge export script.
4. Run Macro: Execute the macro to automatically generate, name, and save your customized PDF and DOCX files to their designated folders.

Highly compatible with Microsoft Word VBA macros and Excel data sources.Built-in PDF conversion engine for fast, flawless document generation.Lightweight architecture handles large mail merge batches without freezing.Supports 100% compatibility with standard .docx and .xlsx formats.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VBA macro failing to save PDFs to the specified folder?

This commonly happens if the destination folder specified in the spreadsheet does not exist or the path is missing a trailing backslash (\). VBA cannot create nested folders automatically during a 'SaveAs' command unless specifically coded to do so.

Can I use WPS Office to run Microsoft Word VBA macros?

Yes, WPS Office (in its professional or business editions) includes robust VBA support. You can enable Developer Tools and run existing Microsoft Office VBA scripts with high compatibility.

Why are my mail merge fields showing up as empty strings in VBA?

This typically occurs when spreadsheet columns are formatted as 'Text' rather than 'General'. Changing the cell format to 'General', saving the file, and reconnecting the data source will usually resolve the missing data issue.

Excel & Data Problems