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.
Thanks sir,
i am created successfully but i need all data in one pdf what i will do with code plz help me
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 Angga here,
I appreciate this post so much, it might help my works a lot, but im so confused why the script keep showing error (yellow highlight)
TargetDoc.SaveAs2 FOLDER_SAVED & .DataSource.DataFields(“Client_Name”).Value & “.docx”, wdFormatDocumentDefault
Is there any typo?
I’ve been googling, scrolled through the comments, watched your tutorial, but still havent found the solution yet
Please help me fix this error, thank you
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.
For some reason it will not allow me to add a space between .DataSource and .DataField
See if replace space with underscore (_) will fix the issue.
Hello, I tried to replace it with _, but it will not allow me to. Indicate invalid
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.
Hi, I am interested in the same question – I would like to save the PDF in a location and at the same time email the PDF document to an email address in the source file.
Hi Jiejenn,
Firstly I’d like to thank you for this, it’s saved me countless hours!
I have a MailMerge/excel doc that I’m adding data to week by week, I would be useful to add a feature that allows it to save from a point that I have left off from, is that possible?
Do you have any flag that indicating which row is the last printed item? If not, then you will need to find a way to mark the items that are printed first then it is easier to go from there.
Hi.
Nice and easy to follow. Very Clear. Thanks so much.
How could this be adapted to open a table or query from an Access Database instead of an Excel spreadheet?
Thanks
Don
Hi Jiejenn,
Your code will save my life. Thank you so much for it.
I followed your entire tutorial and the individual word / pdf documents were created. However, when I open the documents, there are no words in it. I created a new document and the error still continues. I have no idea what’s going on. Could you give me any tips? Thank you so much for your attention.
Check your source file path, most likely it is due to your VBA script couldn’t find the file.
Hi Jiejenn,
Thank you very much for your tip. I changed the
Set MainDoc = ThisDocument
For
Set MainDoc = activedocument
Now it worked. =)
Thanks!
Hey,
Thank you so much for the script, this is what i need
But unfortunately, my script never works and i can’t find the solution through the comments or google
Please help me fix this line (yellow color)
TargetDoc.SaveAs2 FOLDER_SAVED & .DataSource.DataFields(“Nomor”).Value & “.docx”, wdFormatDocumentDefault
is there anything wrong or typo? Im using Word 2010
Thank you, I appreciate it.
This is an amazing macro, thank you for sharing it. I spent quite a few hours in trying to get this working on Office 365 and for 2 days in a row I am getting and “ODBC Excel driver login failed” error saying that the “External table is not in the expected format”
I searched and searched, tried with CVS or XLS – an article saying that the .XLSX files are opened exclusively by Word, but I could not get it running.
Does anybody have any idea how can I fix this?
This code has saved me so much time in my work and I am very thankful for it! One quick question, I only need to save as a WORD document and when I remove the line to save as a PDF I get a “Run-timer error ‘4198’ Command failed”
When I click on debug the following field is highlighted:
TargetDoc.SaveAs2 FOLDER_SAVED & .DataSource.DataFields(“Customer_Name”).Value & “.docx”, wdFormatDocumentDefault
I’m assuming that the command doesn’t know where to go next and just stops but it does give me the WORD documents.
Thank you for any help you can provide!
If you hover your mouse to customer name field, does it show any value?
No, no value shows up when I hover my mouse over Customer_Name in the VBA code.
Hi,
Thank you so much for your script, but I’m having some trouble.
I kept getting the “Word found locked fields during the update. Word can’t update locked fields.”, but I tried to remove everything and type them in pure text, also remove all the fields from word template, nothing worked.
Also, how can I let the Word automatically close after the vba is finished?
Hi,
Thank you so much for your tutorial.
However, when I saved the files, there is always an empty page at the bottom of a separate document.
How may I resolve this?
Most likely your word doc has extra space at the end causing an extra empty page. One way to fix that is adjust your document margin.
This works really well. Is there a way do also insert metadata to the file header too via VBA on file save?
Hi jiejenn, thank you for sharing!
This would save a lot of time but I am encountering the following problems which I’m hoping you might be able to help with (I’ve looked at comments on your website but couldn’t find similar problem)
1) the document the VBA creates and saves uses the correct naming however the document contains the following text “A field calculation error occurred in record ‘x’
2) Form Letters are created – these are the MailMerge docs separated into individual documents
Any assistance would be greatly appreciated.
Can you send me your word file (must containing VBA) and Excel data file to YouTube@LearnDataAnalysis.org
Thanks for your offer to assist,we’ve worked out that the formatting of the footer was an issue. So now works perfectly. Thanks again so much!
Thank you so much for the help
Hi Jiejenn,
Just wanted to say thank you for posting this script. It’s incredibly useful and has saved our team a ton of time with a particular task that this was tailor made for.
Ernesto
Hi Jie-Jenn
Thank you so much for sharing. I can see this will be incredibly useful.
I originally started with code for automating mail merge but what I had would only produce a single word file with all my letters and did not save the file.
Private Sub Document_Open()
DoMailMerge
End Sub
Public Sub DoMailMerge()
Dim DocName$
DocName = ActiveDocument.Name
‘Do mailmerge
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.Execute
End With
‘Close mailmerge document
Windows(DocName).Close wdDoNotSaveChanges
End Sub
I have since replaced this with your code which works like a dream but I wondered if you could tell me how I can combine the two so that when my user double clicks on the mailmerge document, it opens, runs your code, saves the output to pdf then closes the original document?
I am not familiar with VBA at all!!
Many, many thanks in advance.
I’m trying to mail merge using a .CSV file. I’ve gotten it to work with XLSX but I can’t seem to get it to work for CSV. Is there anything different that needs to be done?