Mail Merge is a really useful feature built-in in Microsoft Word to create letters, labels, envelopes, or even drafting email in bulk. But not everything is perfect. One of the drawbacks with mail merge is that when you try to print each record as a single PDF file, unfortunately, Word combined all the records in one single PDF. Also, you cannot perform mail merge to save each record as their own individual Word document.
In this tutorial I am going to show with the help of VBA how we can 1) Automate Mail Merge task and 2) save each record as individual PDF or Word document.
VBA Script
Change “Client_Name” to the correct fieldheader of your xls(x).
Secondly, I had to change — TargetDoc.ExportAsFixedFormat FOLDER_SAVED … —
to
— TargetDoc.ExportAsFixedFormat OutputFileName:= FOLDER_SAVED …– to get i to work.
for the rest: many, many, many thanks. It solved my issue I’ve been working on for a long time.
This is amazing. Saved me so much time as I had a mail merge of over 150 items that was going to take for ever to save individually manually. Worked on Word 2016. Thank you so much for sharing this!!
Glad the VBA scripts helped!
This is great, exactly what I needed. Is there a way to use the SQL WHERE clause to ask for data, for example a specific month?
I can make the WHERE clause give me the information I want by editing the script each time but it would be perfect if there was a way to make the script ask the question.
In VBA there is an input function which will prompt a pop allowing users to enter a value. You can pass the put to a variable and concat the variable in your WHERE clause SQL statement.
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/inputbox-function
Thanks.
Hi! Question from a novice in the field: what do I have to write instead of [$] in the SQL statement? Is it my Excel sheet name?
.OpenDataSource Name:=SOURCE_FILE_PATH, sqlstatement:=”SELECT * FROM [$]”
Thanks a lot
You need to write your worksheet name followed by the dollar symbol.
Hi.
I get an error on this line.
.OpenDataSource Name:=SOURCE_FILE_PATH, sqlstatement:=”SELECT * FROM [Customer_Data_for_Mail_Merge$]”
Confirming that is the file name.
Any ideas?
Thanks in advance.
Regards
Brad
Sorry about the late reply, but what’s the exact error message?
Hi JieJenn,
Thanks for the guide! It saves so much time! It worked for me but when I opened the word documents, it will pop out:-
“Opening this document will run the following SQL command:
Select * FROM [SHEET1$] ” Data from your database will be placed in the document. Do you want to continue?”
Is there any way to not let this pop up whenever I open the documents? Once again, thank you!
Best Regards,
Nova
Hey Nova, looks like this is an issue exists for many users, perhaps this link might help. https://support.microsoft.com/en-us/help/825765/you-receive-the-opening-this-will-run-the-following-sql-command-messag