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.
Buy Me a Coffee? Your support is much appreciated!
PayPal Me: https://www.paypal.me/jiejenn/5
Venmo: @Jie-Jenn
For any question, please contact me at YouTube@LearnDataAnalysis.org (since I don’t check on my post reply very often).
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!
Hi Jie Jenn, thanks for the VBA code, but I kept on having issues with the Recordcount. It seems the mail merge is merging for the last empty row with no fields and when it hits the .TargetDoc.SaveAs, it forces the macro to stop. Anyway to resolve this?
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?
All sorted thanks anyway.
Typo.
Not sure if the code works for Publisher.
Sir, you are the best of the best.
Thank you so much!
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
What about using the following…
To turn off alerts:
.DisplayAlerts = wdAlertsNone
To turn back on:
.DisplayAlerts = wdAlertsAll
Hi, I’m having error on Saveas2, I cannot determine why,
TargetDoc.SaveAs2 FOLDER_SAVED & .DataSource.DataFields(“Short_name”).Value & “.docx”, wdFormatDocumentDefault
.DataSource and .DataField needs to have a space in between.
Hi I used the code and the first time it worked 100% fine.
I then wanted to do it again using a different excel database. I edited the code but am now getting “Runtime Error 5941 The Requested Member of the Collection Does Not Exist”
When i watch the code to see where the error is it seems it relates to :
“TargetDoc.ExportAsFixedFormat FOLDER_SAVED & .DataSource.DataFields(“Client_Name”).Value & “.pdf”, exportformat:=wdExportFormatPDF”
I suspect if might be something to do with your new file path if your code worked the first time. If you want, feel free to send me your data file and word template to YouTube@LearnDataAnalysis.org and I will take a look at it.
Hi, I seem to have reach an error here.. Is there anyway to insert the where clause?
‘// if you want to specify your data, insert a WHERE clause in the SQL statement
.OpenDataSource Name:=SOURCE_FILE_PATH, sqlstatement:=”SELECT * FROM [April 2020$]”
Is there any areas that I’ve input wrongly?
Thank you in advance
You want to insert your WHERE clause in your SQL statement. For example “SELECT * FROM [April 2020$] WHERE [your field] =“
Hi Jiejenn,
What your are sharing is really helpful. Do you have a tutorial on encrypting unique password to individual pdf after saving it. I want to use the individual password listed from the source file.
Thank you in advance.
Currently Word does not support PDF password encryption. Do you have knowledge in Python? I wrote a small program to embed password to PDF files. Otherwise you will have to wait till I release an open source program (Windows only) to encrypt PDF files.
Hi, is it possible to share the program to embed password to PDF files?
Hi jiejenn,
Your VBA script is totally awesome. I’m working with 206 data records, and it will take forever to make pdf one by one each record.
Some of my data fields contain a same record. And it will overwrite a target document with a same name. Is there any chance to name the target document by combining 2 data fields? I just want to make them unique.
Thank you so much
You will first pick one or two fields to be used to create your unique combination (just makes sure there are no special characters used in those fields). Next, you can join the fields using the “&” symbol. For example, .DataSource.DataFields(“Client_Name”).Value & “-” & .DataSource.DataFields(“City_Name”).Value.
Hi, this is a great script, and did exactly what I was hoping for. I’m trying to save each individually named file into a folder by manager, and then vp. Any ideas on how I can get this macro to create these folders then file each document?
filepath/svp/vp/manager
This was a huge help for me! Is there any way to make the input and save path variable based on a userid. Similar to:
Const FOLDER_SAVED As String = “C:\Users\” & sUserid &”\Desktop\Contracts”
Where Userid is based on sUserid = Environ$(“username”)
Thanks in advance!
Yeah it is possible. You just need to move FOLDER_Saved inside the macro. For example,
Const SOURCE_FILE_PATH As String = “”
Sub TestRun()
Dim MainDoc As Document, TargetDoc As Document
Dim dbPath As String
Dim recordNumber As Long, totalRecord As Long
Dim FOLDER_SAVED As String, sUserId As String
sUserId = Application.UserName
FOLDER_SAVED = “C:\Users\” & sUserId & “\Desktop\Contracts”
If sUserId is returning the username only and not their user ID, which is what my system is set to, then I found a solution:
Insert new Module with (below credit to RyanL):
Declare Function wu_GetUserName Lib “advapi32” Alias “GetUserNameA” _
(ByVal lpBuffer As String, nSize As Long) As Long
Function NetworkUser() As String
Dim lngStringLength As Long
Dim sString As String * 255
lngStringLength = Len(sString)
sString = String$(lngStringLength, 0)
If wu_GetUserName(sString, lngStringLength) Then
NetworkUser = Left$(sString, InStr(sString, Chr(0)) – 1)
Else
NetworkUser = “Unknown”
End If
End Function
Back in the main Sub, revise FOLDER_SAVED to:
set FOLDER_SAVED = “C:\Users\” & networkuser () & “\Desktop\”
Great JOb
Hi – thank you so much for the script, you are a total lifesaver! Works a dream, but have one problem… The documents when split out all save on my desktop, even when I have copied and pasted the file path (as you say to do in your video) to a new folder (called “mm test”) in my downloads. I tried moving this new folder to my desktop (and copying the new file path), but again it just goes directly to my desktop.
Relevant bit of my code:
Const FOLDER_SAVED As String = “C:\Users\ctaylorc\Downloads\mm test”
Const SOURCE_FILE_PATH As String = “C:\Users\ctaylorc\Downloads\mm test\mm test.xlsx”
[can send the rest if you need it!]
Not sure if I follow your question. If you want, send me a descriptive explanation along with your Word Document and Excel Data file to YouTube@LearnDataAnalysis.org and I can take a look.
Hii, You are very helpfull, but i need make password in individual pdf, how to make it ? i will very happy if you want to solved my problem.
best regards
Makinun
PDF password encryption is currently not available.
I ran the script, adapted the fields but it generates files with the proper names but all of them with the contents of the first page.
Is there something missing?
Hard to tell without looking at your file and your code. You can send your Word Document and Excel Data file to YouTube@LearnDataAnalysis.org and I can take a look.
hi, I’ve tried your code successfully.
the problem is when I use some 4 signature scanned images with png format in the docx content file. everything exported successfully in pdf format. all images show perfectly. when it save at docx format, only 1 image showing and the rest image not showing.
could you help me fix my problem?
Hi, This is exactly what I was looking for so thank you for publishing. I hope I can get it to work.
I have no experience with VBA and I’m having an issue with the line:
.OpenDataSource Name:=SOURCE_FILE_PATH, sqlstatement:=”SELECT * FROM [$]”
I added the worksheet name but get an error
Your select statement is missing the table name.
SELECT * FROM [Your_Table_Name$]
I have been looking for a solution for a few days now. Tried various things but was running into issues like extra pages, broken formatting, missing copying of header/footer etc.
Then I came across your script; and was exactly what I needed!
It worked like a Charm! Thank you so much!
I did make some minor tweak for myself, and was curious if there is a way to keep the two constant paths relative to the document path?
Thanks
Sushant
Can you give me an example？
Dear JieJenn, you are the hero!
But I have two questions:
1. when I want to save just actual document to .pdf and .docx, how should I change the code? Now it is repeating until all rows from excel are done.
2. I would like to use condition – to create document ONLY when in excel sheet in column “X” is an value (or bigger than 0). If not, then skip creating of this document, and continue to next row.
Many thanks in advance.
Can you give me an example of your question #1. Not sure if I get it. For #2, in line 15, you can insert what we called WHERE clause to specify your filter criteria. For example$] WHERE [Column Name X] = “”X”””
.OpenDataSource Name:=SOURCE_FILE_PATH, sqlstatement:=”SELECT * FROM [
Sure, sorry 🙂
1 – now it works like I have 30 rows in excel filled with data. I start macro and 30 .pdf and 30 .docx documents is created. But sometimes, I just need to pick just one client from mail merge and create just this actually opened page to .pdf and .docx
2 – thank you, I am going to try, but I am beginner. Maybe I will kindly ask you for fixing “my” code 🙂
Thank you very much.
I have tried to google it, but I am lost. I need to add condition, I want to generate pdf and docx if value in all rows in column BP is higher than 300 € (for example)
http://prntscr.com/suiqiz
😀
I mean it value in specific cell of column BP. It will check condition – if yes, then create documents, if no, skip to cell below 🙂
Hey there – this code is excellent and it’s saving me loads of time already after I implemented it at the weekend!! Thank you very much for sharing it.
Could you please tell me what code I should add to have each of my newly created word docs automatically attach to an email, with the email address pre-populated from the Email column in my data source?
So I run the macro, it creates the individual word doc and attaches it to a new email with the correct email address. It doesn’t automatically send it, I’ll add my own text. But it means that the correct file is always attached to an email with the correct email address immediately following the merge process.
If you could please advise I’d be immensely grateful.
Many thanks
Hey Davey, have you written the VBA already? In your comments you say you ran a macro and it creates an email and send an email + attachment.
Jiejenn, this is terrific! I have a small question and I’m wondering if you could help. Right now, my worksheet has about 1000 fields, and they will keep adding on as days goes by as I get more applications. I get about 70 new fields to the same worksheet everyday, and run this (wonderful) code you made everyday with those 70 new rows of data.
How can I code the VBA in such a way that I can (manually) input the rows that I want them to be saved automatically? For example, today I would like to save row 1001 to 1100, and tomorrow, I will like to save 1101 to 1200.
If you want to specify the row range, in this line For recordNumber = 1 To totalRecord, you can change the row range. For example, if I want to only print row 100, 200, then I would change the line to
For recordNumber = 100 to 200
Hi, first of all, thanks for sharing this knowledge.
I followed all the steps, but when I running the macro, error 4198 appears.
.OpenDataSource Name:=SOURCE_FILE_PATH, SQLStatement:=”SELECT * FROM [dados$]”
Could you help me?
Error 4198 usually refer to file name is invalid. Make sure no special characters are included when you try to save your file.
Thank you for your help. With your help, I managed to solve this problem. However, when individual files are saved, they are saved in white. There is no information in the document. Could you help me with this problem?
Good day, Jie-Jenn!
Thanks a lot for your code, it is help us for print result of testing our patients for COVID-19.
But i have same problem, like Jonah does:
If i insert WHERE clause and filter my data by Date (only today result) – I have wrong count of rows and code goes to error.
For example:
My table have over 2000 rows, but only 123 row for Date = 6/12/2020, and i send next sqlstatement:
.OpenDataSource Name:=SOURCE_FILE_PATH, sqlstatement:=”SELECT * FROM [Sheet1$] WHERE [Date_Field] = #6/12/2020#”
But Msgbox totalRecord print “2000” instead 123
Can you help?
Can you send me your data file and your Word document to YouTube@LearnDataAnalysis.org and I will take a look at it.
Interested in the input pop up window for where SQL function. Totally new to VBA and wondering if the script for how to include the pop up window could be provided.
Hi Jiejenn,
thanks u so much for the script, its save me a lot of time.
i have an another question, after i convert this to pdf and i want to email the pdf file automaticly what code i must insert in this script. For The email address already in the source file.
ps: sorry for my bad english, cos it’s not my native language… 🙂
No worries. Send me an email with your data file with just some dummy data to YouTube@LearnDataAnalysis.org. It is difficult to share a chunk of VBA code in a reply.