Want to add couple things here: 1) When your table header name contains a space, for example Account Name, because a data source field cannot have a space, VBA will automatically replace space with an underscore. So when you type your DataFields' name, instead of DataFields("Account Name").value, you want to use DataFields("Account_Name").value. 2) I will be working on a Mail Merge Utility with a UI with to eliminate the need to write VBA code. Also looking adding few useful features to enhance the functionality. 3) and try not use special characters in your file name. And lastly, thank you for all of your support and views. I appreciate it.
thanks for your video. Unfortunately I have a problem when I run the code; a table will appear to confirm data source, I click on Show all and retreive my data source file choosing "Excel files ODBC (*xlsx), then I have the SelectTable window, I click on Options, Show, and I tick everything. I select the sheet where the specified column is, the window closes and then nothing happens. Do I maybe do something wrong? Thanks
For me it kept crashing on the lines below, but by declaring the parameters for Filename/FileFormat (save as in Word - I used the filename from concatenating 2 fields within the Excel data file) and outputFileName/exportformat (to export to pdf) meant that it worked: TargetDoc.SaveAs2 FileName:=FOLDER_SAVED & .DataSource.DataFields("filename").Value & ".docx", FileFormat:=wdFormatDocumentDefault TargetDoc.ExportAsFixedFormat outputfilename:=FOLDER_SAVED & .DataSource.DataFields("filename").Value & ".pdf", exportformat:=wdExportFormatPDF Anybody else gets stuck with that, you could also try that. Brilliant work Jie Jenn, saved me so much time - it's very much appreciated!
Good tutorial, although not sure what I have done wrong. Managed to take a 49 page (49 individual letters) mail merged document and end up with 49 versions of the same each with the 49 pages each, instead of 49 documents with a letter each.
I cannot thank enough for this tutorial, very useful and extremely helpful for someone like me. hours of work in minutes. thanks once again for sharing your knowledge :)
Well done! but I have question with the sql statement if want to retrieve data from row 2 to row 10 how can I do with it? .OpenDataSource Name:=SOURCE_FILE_PATH, sqlstatement:="SELECT * FROM [databases$]" Thanks you.
Great video Jie Jenn, I have been struggling with the SaveAs2 comment. My Datafield has a '" | " character in the value, I want to save the file name as the 10 characters before the " | " character, can you help me?
Hi Jie, Thank you for this video, very helpful. I have a question about formatting. The data that appears in the newly created word document does not have the proper formatting, i.e. Currency/Accounting/Percentage formatting in the excel file shows up as a general number in word. 6% would show up as 6.0 etc. Any ideas on how to fix this?
hello i cant seem to pass the open data source i continue getting an error .OpenDataSource Name:=SOURCE_FILE_PATH, SQLStatement:="SELECT*FROM[database$]" is there a way that i can fix this?
Hello, Thank you very much for the video. When i try to run the code, a tab saying "select table" show ups and when i click okay it say that "Run-time error '5922' unable to find data source". Do you know what i am doing wrong? Thanks
After the OpenDataSource instruction, I get a pop-up which allows me to select the whole worksheet, but then .DataSource.RecordCount = -1. Any ideas why it is not finding my records? Mail merge works normally so I'm sure the source data is ok.
Thank you this is great, but I'm having a problem I hope you can help me with, when I try to run it it gives me a runtime error 4198 and points to the .opendatasource line. And suggestions?
Hi. I’ve tried this and when I press F5 it runs like it’s working but it’s not opening a new document? Can you help? Thank you. The code I used is exactly the same as yours.
Hello sir this is super amazing, when I run the macro I’m getting run time error 5922. The error is showing up in line 10 where we have the open data source. Would you mind helping me out
Is it possible to have this make individual folders for each database reference and then have the files save in them locations ? Just out of interest as used this for batch documents in work and it’s been great however it requires moving said file into a folder for each individual letter any advice?
Hello mate thank you so much for the toturtial! everything works however the documents crated seems to be all empty, can you help me with this problem?
Hi. How do I get the client name to appear at the beginning of the Word document name once it is saved? The code right now always makes the client name appear at the end of the document name. Thanks.
Hello. I have used this macro in the past with good success...a timesaver. Acrobat PDF Maker plug in with Mail merge now does create separate pdf files but still can't name them uniquely so this macro still remains very useful. I however now have an issue that the .pdf files being generated are all blank contents..the name of the file matches what I have in my excel mailmerge database so that source file appears to be set up correctly but the mail merge functionality itself doesn't seem to be running properly. any insight? I am using Office 365... is there perhaps a macro update to use on Office 365?
@@jiejenn thanks for the reply Jie. Can you clarity how to trouble shoot a margin issue? what should I be looking for? I am unable to send you the file as it contains confidential info. I did however need to really reduce both side, top and bottom margins to maximize the space for my document. thanks in advance
Hi Jie. This really helped me a lot while I used for some letters last year and I’m really thankful for that. However, while I’m trying the same code on a new file: 1. I’m getting separate files opened along with my letter format without any file name. 2. Both word and pdf files saved in my folder read as “word found licked fields during update. Word can’t update locked fields “ Kindly help. Thanks in advance
Thanks. Jin. It is a very useful and timesaving clerical job which is widely used in corporates and other sectors. One my relative is in the HR department and he used to print and save the Increment / Bonus Letters individually by each employee and send by email to them. You can imagine how painful and time-consuming job for more than 500 employees. I shared this example and he is very happy to save his time by such Automation. Thanks Jin Again for your efforts.
Thank you so much. Initially had blank pages, had to import and run the module from within the project/document section, not the normal section in the VBA editor tree window on the left screen (don't know proper name right now).
@@johnvanco2959 in the vba window tree on the left. It will list a generic version and a line for the document you have open that you want to run it in. Click on the actual doc and then run/press play
Hi Jie, how do I send the individual file to fax numbers? I need to create letters that will go to a list of about 1,000 providers, to let them know their new id number. But it has to go to their individual fax number which is in a column in the excel sheet. I saw there is an option: .Destination = wdSendToFax how do I use that? Thank you for your video. It is so illustrative.
@@jiejenn i have the spread sheet with providers, provider id and their fax number. So each letter has to go to the provider fax machine. Is that possible?
Hello, When I run the VBA and it gets to the .OpenDataSource line of code, another window opens up, the says “select table”, I then click my workbook, click options, then click “system tables” so that the table I am referencing shows up in the window. I then click that table, click ok then nothing happens. No error messages, nothing. Can you help? Thanks
Not sure if I can help you given such little information you provided. If you want, you can send me your data file + Word document (must contain the VBA code) to TH-cam@LearnDataAnalysis.org and I can take a look when I have time.
Hi, Thanks for the tutorial. It doesn't work when I followed it on a Mac, quite possibly something is different between the two platforms. The macro ran, no error, but no files have been created. I'm not an expert in VB, though
Hi Zarina, I think the object references are a little bit different on Mac. Unfortunately I am not a Mac user nor do I own a Mac PC. Your best bet is look for someone who has a Windows PC and try the mail merge and see if it works.
I have had the same difficulties. I was able to get the script to MOSTLY work though. Line 19: The 1 to TotalRecord doesn't seem to work, but if you replace TotalRecord with the actual total number of records then it works. Line 33: I cannot get it to save PDFs. If I comment out this line, the script will create unique word files for every merge record, but my Mac chokes on wdExportFormatPDF
Is it possible to make pdf file output encrypted. Usually i do manual save as page 1, input password xxx, next save as page 2 input password yyy, and so on....
hi, thank you for this, i have a question - i keep getting a debugger error on the open source data line. i see your folder is in C drive, i have to use a W drive - is that the issue? also i get a pop up asking me to select a workbook. any suggestions would be really helpful. thank you!
I don't think drive location should matter, but your error to me seems like Word cannot find the file. The pop up is caused by Word doesn't have the source file linked. If you want, you can send me your VBA code to youtube@learndataanalysis.org and I can take a look at it.
Hello, I just used this and it was very helpful and it did work, but when I open the certificates in my PDF or Word file, the document comes blank. How do I fix this?
Hi JJ, I getting a 'Microsoft Data Link Error' which reads as 'Test connection failed because of an error in initializing provider. Cannot start your application. The workgroup information file is missing or opened exclusively by another user'. while I have not opened the excel file nor it is being used/accessed by anyother program. Please help.
I have a question, If I just need the pdf File, Can I write a code to delete the files? or can I make a pdf file without creating a word document? Thank you.
The code gets to the "TargetDoc.Close False" line and then stops to ask me if I would like to save the document. I have to press yes, otherwise it does not continue. Do you know how to automate the "yes" clicking? Thank you in advance.
I tried this VBA. It works, but not same as what he says " Automate Mail Merge to Save Each Record Individually in MS Word". The VBA Mail Merge produces many records and saved in one file. I expected to have one record saved in one file.
Most of the people seemed to have success with the script. I suspect there may be a typo or two somewhere. Have you try to step through each line and to see what does each line does? It is easier to figure out where the issue is that way.
Thanks a lot for sharing this wonderful tool. As a public server I need to send legal sheets with same info to different people and it works great! I dont have great knowledge of VBA but you made it so easy. At first I had troubles but with patiente I made it work. Greetings fro México.
Thank you for the tutorial. Anw, I wanna ask you one thing if you don't mind. How to save each record as PDF if my data source is .CSV not .XLSX? (background: I have some field that exceed 255 limit in mail merge, so I have to save it in CSV in order to make it work). Thank you.
@@jiejenn Thank you for your response. When I change my data source to csv, the formula can't work. Let say, if I type: Const SOURCE_FILE_PATH As String = "D/MyName/MyFile.csv", nothing will happen after this. It only could run if I use xlsx format (ex: MyFile.xlsx). How to make the formula work? (I use windows 10 and office 365)
This was a lifesaver thank you so much for this workaround!!! I had to watch a few times, but very easy to follow. Had to remove the SQLStatement though.
@@YippeeKanYe i have a problem with that line as well, did you delete the sql statement after the comma or the whole line? (. Opendatasource name=....)
It was great help... i have one more ask i have in the corresponding column password to be input for that file. Note password is different for each file. May be you can think like a SSN Number or Driving licence number for each person as password for them. CAn you help please.
If you want to include password to Word Document, the SaveAs2 method has a parameter called password, and you can supply your password to that parameter. TargetDoc.SaveAs2 FOLDER_SAVED & .DataSource.DataFields("Client_Name").Value & ".docx", FileFormat:= wdFormatDocumentDefault, Password:=""
Thank you Sir! Appreciate your quick reply. however its like this for File 1 the password is 1234 File 2 password is 4567 and so on and its different for each file like mail merge we have different name and id etc..so can we something to input password while saving only with the mail merge from the Excel sheet it can pickup the password
Thanks a lot for sharing this knowledge with us it was very useful. Please advise if we can separate individual documents from a mail merged consolidated file as well? can we use with the same method by making the source as that word file?
It is possible to generate a word template from every excel row individually. In excel I need a button to appear on every new row I populate with data and the button must open the word template that was fill with the data that I populate the row. And also i need the autofill word template to be save in a specified location. Thank you!
Yeah it is doable, but because you want to tie each row per button, you will have to manually re-link the button to each row. It is a pretty time consuming process if you have many records though.
Thank you for posting this video. The .Destination = WdSendtoNewDocument seems to make a long word file with every generated document from the Mail Merge put together. Any way how to change it so it just makes the one custom document for each line of source data?
Greatly appreciate your sharing this. Struggled to find an effective solution elsewhere. Been awhile since I coded last. Your delivery was excellent and easy to replicate. Only real hiccup I had was the SQL code. SOURCE_FILE_PATH was correct (I set a Watch) yet stepping through the line, I'd consistently get the full filename path truncated (e.g. c:\1\2\3\file.xlsx, came back as c:\1\2\file.xlsx). Solution: Replace database$ with the name of the table containing the data in the xlsx. Worked beautifully. Thanks!
I have used this to help me with my work and have colleagues asking to use it - when I have shared it to them they can’t get it to work is their a way in which this can be used by multiple users ?
Unfortunately, VBA support on IOS is very limited, and some of the Word objects are missing comparing to Windows. If you are on a Mac, my recommendation is to either borrow a Window's PC or look for an alternate.
Hello. Great work. I tried the macro but I get the following error 5941, element not found for the line TargetDoc.SaveAs2 FOLDER_SAVED & .DataSource.DataFields("Client_Name").Value & ".docx", wdFormatDocumentDefault Do you have any idea why? Thank you very much
Without looking at your code, your file, and the error you get, it is nearly impossible to help. I would suggest you post your question on Microsoft Word Forum.
Thanks. This is very useful & I can successfully try even I don't have any IT knowledge. Thanks so much!! And would like to ask if it is possible to set a password in the PDF??
Hey Jie Jenn, thanks for this tutorial. I am trying to do the same with Publisher, but I can't figure out what to do. Could you please somehow look at my code and give me an advice?
@@jiejenn I am very glad you answered. Yes, Microsoft Publisher. I have Mail Merge, it is connected to excel sheet. And I want to convert each record to separate PDF. I wrote a simple script, it generates PDFs with correct names, but for some reason each PDF contains only the second record from excel.
You are a lifesaver! There were some moments where I hit a roadblock, but meddled here and there and it saved my team's time, a LOT of it! Thank you so much sir.
Hi, Many thanks for sharing this tutorial. It's amazing and so helpful. It's saving both in word and pdf format; is there any way to avoid saving in word format- please advise.
Thank you Jie Genn, the video explains all I'm looking for. I'm practically new with VBA Codes and I'm having a hard time understanding how you went about the "destination" which bears the mail merge automation. Could you do a little explanation on that? Thanks.
I meant the first line " folder_saved as string" the folder named "destination" I would like to know it's content and how to go about it? I know the second file is the Excel sheet containing the data. Can I work with an "xslm" file in this case
What a great tutorial!! I have one question. I need to put a repeated name in the file name. For example "Approval - Project" . The word Approval has to be in all my file names. How do I include the in the macros?
Within the TargetDoc.SaveAs2 you can concat any addition text you want to include. For example: TargetDoc.SaveAs2 FOLDER_SAVED & .DataSource.DataFields("Client_Name").value & " Approval - Project" & ".docx"
After you have connected to your database file on the Word Ribon choose edit recipient list. This is the Microsoft article on the subject. support.office.com/en-us/article/mail-merge-using-an-excel-spreadsheet-858c7d7f-5cc0-4ba1-9a7b-0a948fa3d7d3
Hello, Many thanks for the video. This was great! My question is In case I want this to be done only for a group of the rows, for example lets say on a column I have category 1,2 and 3, and I want to do this for category 3 only, how should I modify the VBA to apply the filter?
You can actually do that directly in a SQL statement. From the OpenDataSource statement, you can include what we called WHERE clause to include criteria. For example, assuming your column name is "Product Category" and you want to filter the dataset by label "category 1", you would write (noticing the extract double quotation): .OpenDataSource Name:=SOURCE_FILE_PATH, sqlstatement:="SELECT * FROM [$] WHERE [Product Category] IN (""Category 1"")"
Nice tutorial! I have some questions though. What if you already have a conditional statement (like skip record if mergefield Field_name is ""). This means the loop will still try to run the total number of records. What is the workaround? Can be filename also be dynamic? i.e. a function of text Manipulation?
@@jiejenn Let's say from Excel you have 2 fields; Name and DOB. Now when doing mailmerge in Word, you put a conditional saying skip record if DOB = "". Also In Excel, the Name field is a combination of Title, Fname, Mname, and Lname from an original database. So your name field in excel will come as "Miss Jane A. Doe" or "Mr. John Doe" if there's no Mname. This means when you do mailmerge you have just two fields name and DOB in word. From your tutorial, the code will go through the total number of records and save as .docx or export to .pdf regardless of what is in the field. This means it will even do a merge for empty fields as far as you have set up your mergefields in word as you would like them to be outputted. So this is what I would like to do, use pagecount to know the number of pages it needs to loop through OR use and IF statement in the VBA code to know what records to actually save indiviudual documents for. It will also extract just the first and last name from each record.
Hi Jie, thank you so much for posting the tutorial. It is definitely what I need recently. I am new to VBA, and I tried to follow your video to make my mail merge works. There is an error "438" in the line of ".OpenDataSource Name:=source_file_path, SQLStatement:="SELECT * FROM [XXX$]", as i only change the sheet name you created to mine. Can you help me on this? Thanks!
This is great and it works perfectly. I am wanting to expand on it a bit by selecting the records from the Excel file that have not been "processed" yet. I have that figured out, but I am trying to figure out how to update the excel field to show it as processed so the next time I run the macro, it doesn't pick that record. For example, I want to change the Processed field from No to Yes. Hopefully you understand what I am trying to do.
If you want to process rows based on a condition(s), after the loop, you can insert an IF statement to express your criteria. For example, if your field is called "Processed", the code would look something like IF .fieldname("Processed").value = "Yes" THEN [mail merge code block] END
@@jiejenn I figured that part out after some trial and error. But what I want it to do is after it finds the records and creates the new documents, update the row in Excel so it does not have a duplicate made. For example, say I have 10 rows and I have a "Processed" column. 4 of the 10 rows have No in this column and 6 have Yes. The query finds the 4 rows that were not processed, your script creates the PDF, but I want those rows to change the No to Yes so next time I run the script, these rows will not be created again. Hope that makes sense.
@@ScottAlexander68 I think I understood your question fully now. If you want to update cells in Excel, it involves of adding Excel reference in Word VBA, which is a bit more advanced if you do not have experience in writing VBA. I will take this as an idea and to make a part 2 video in the future.
Jie Jenn thanks for your reply. I figured out what happened; I forgot to change the name of the worksheet. Your tutorial is extremely helpful. I’m not kidding...what used to take me 4 hours to do now takes me less than 5 minutes. I’m a total noob but getting the hang of it thanks to you. I appreciate your time putting together these tutorials. Very easy to follow and understand.
Is it possible to add another field to the file name? Let’s say I want the company name to pull from one field but I would also like to include the cost center from another field. The file name should look like this: COMPANY_CC9210. Thanks!!
Want to add couple things here:
1) When your table header name contains a space, for example Account Name, because a data source field cannot have a space, VBA will automatically replace space with an underscore. So when you type your DataFields' name, instead of DataFields("Account Name").value, you want to use DataFields("Account_Name").value.
2) I will be working on a Mail Merge Utility with a UI with to eliminate the need to write VBA code. Also looking adding few useful features to enhance the functionality.
3) and try not use special characters in your file name.
And lastly, thank you for all of your support and views. I appreciate it.
Jie Jenn, this was very useful. Can you tell how to few lines of code to combine 2 or more pdf files together in this script of yours?
thanks for your video. Unfortunately I have a problem when I run the code; a table will appear to confirm data source, I click on Show all and retreive my data source file choosing "Excel files ODBC (*xlsx), then I have the SelectTable window, I click on Options, Show, and I tick everything. I select the sheet where the specified column is, the window closes and then nothing happens. Do I maybe do something wrong?
Thanks
For me it kept crashing on the lines below, but by declaring the parameters for Filename/FileFormat (save as in Word - I used the filename from concatenating 2 fields within the Excel data file) and outputFileName/exportformat (to export to pdf) meant that it worked:
TargetDoc.SaveAs2 FileName:=FOLDER_SAVED & .DataSource.DataFields("filename").Value & ".docx", FileFormat:=wdFormatDocumentDefault
TargetDoc.ExportAsFixedFormat outputfilename:=FOLDER_SAVED & .DataSource.DataFields("filename").Value & ".pdf", exportformat:=wdExportFormatPDF
Anybody else gets stuck with that, you could also try that.
Brilliant work Jie Jenn, saved me so much time - it's very much appreciated!
Good tutorial, although not sure what I have done wrong. Managed to take a 49 page (49 individual letters) mail merged document and end up with 49 versions of the same each with the 49 pages each, instead of 49 documents with a letter each.
Happened the same to me. Any solution?
This is amazing, It is a generic tool which can save each record from Excel in a customized pdf or word template.
I cannot thank enough for this tutorial, very useful and extremely helpful for someone like me. hours of work in minutes. thanks once again for sharing your knowledge :)
Well done!
but I have question with the sql statement
if want to retrieve data from row 2 to row 10
how can I do with it?
.OpenDataSource Name:=SOURCE_FILE_PATH, sqlstatement:="SELECT * FROM [databases$]"
Thanks you.
Great video Jie Jenn, I have been struggling with the SaveAs2 comment. My Datafield has a '" | " character in the value, I want to save the file name as the 10 characters before the " | " character, can you help me?
| is one of the characters cannot be used to create a file or folder name. You will have to use other characters that is valid.
Hi, I am able to produce word and PDF but both file are empty ) no words insite. Can someone help.
Hi Jie, Thank you for this video, very helpful. I have a question about formatting. The data that appears in the newly created word document does not have the proper formatting, i.e. Currency/Accounting/Percentage formatting in the excel file shows up as a general number in word. 6% would show up as 6.0 etc. Any ideas on how to fix this?
i have run this code , error has occurred, line .OpenDataSource Name:=SOURCE_FILE_PATH, SQLstatement:="select * from [database$]" pls help any one
hello i cant seem to pass the open data source i continue getting an error
.OpenDataSource Name:=SOURCE_FILE_PATH, SQLStatement:="SELECT*FROM[database$]"
is there a way that i can fix this?
By look at your syntax, I guess your query should be SELECT * FROM [database$]
Hello, Thank you very much for the video. When i try to run the code, a tab saying "select table" show ups and when i click okay it say that "Run-time error '5922' unable to find data source". Do you know what i am doing wrong? Thanks
Check your code to see if there are any typos.
@@jiejenn I also got same error. Its something to do with SQL Statement.
After the OpenDataSource instruction, I get a pop-up which allows me to select the whole worksheet, but then .DataSource.RecordCount = -1. Any ideas why it is not finding my records? Mail merge works normally so I'm sure the source data is ok.
Thanks for the information, what if I want to save only 1 or 2 letters from the table and not the rest. How can this be done.
Thank you this is great, but I'm having a problem I hope you can help me with, when I try to run it it gives me a runtime error 4198 and points to the .opendatasource line. And suggestions?
The error is most likely due to invalid file path is provided.
Hi. I’ve tried this and when I press F5 it runs like it’s working but it’s not opening a new document? Can you help? Thank you. The code I used is exactly the same as yours.
Hi Samantha, I charge a fee of $10 to troubleshoot. If you are interested please let me know.
Getting error as method or data member found for .active record
Anyone please help
Will this work on a Mac (Office 16). So far, no love.
Omg..... Omg... Finaly i got vba code for mail merge for docx ans save file in docx... Thnk lost.... It very help full me.... 🙏🙏🙏
Hi, Awesome. Quick question, if my word document has a table of contents and I wish to update it is there VBA code to do so for each file?
Hello sir this is super amazing, when I run the macro I’m getting run time error 5922. The error is showing up in line 10 where we have the open data source. Would you mind helping me out
Is it possible to have this make individual folders for each database reference and then have the files save in them locations ? Just out of interest as used this for batch documents in work and it’s been great however it requires moving said file into a folder for each individual letter any advice?
Yeah, it is possible.
Hello mate thank you so much for the toturtial! everything works however the documents crated seems to be all empty, can you help me with this problem?
Replied your email.
Hi. How do I get the client name to appear at the beginning of the Word document name once it is saved? The code right now always makes the client name appear at the end of the document name. Thanks.
Once solution is to have a separate column containing the file name, and use that column as the file name instead.
Facing a problem that once merge and the doc / PDF was empty but the PFD file name was correct, any idea?
Makes sure your PDF content is extractable. If it is an image or encrypted, then it may not work.
Hello. I have used this macro in the past with good success...a timesaver. Acrobat PDF Maker plug in with Mail merge now does create separate pdf files but still can't name them uniquely so this macro still remains very useful.
I however now have an issue that the .pdf files being generated are all blank contents..the name of the file matches what I have in my excel mailmerge database so that source file appears to be set up correctly but the mail merge functionality itself doesn't seem to be running properly. any insight?
I am using Office 365... is there perhaps a macro update to use on Office 365?
Hard to tell without looking at your files. One common cause is due to margin is not set correctly.
@@jiejenn thanks for the reply Jie. Can you clarity how to trouble shoot a margin issue? what should I be looking for?
I am unable to send you the file as it contains confidential info. I did however need to really reduce both side, top and bottom margins to maximize the space for my document. thanks in advance
Hi Jie. This really helped me a lot while I used for some letters last year and I’m really thankful for that. However, while I’m trying the same code on a new file:
1. I’m getting separate files opened along with my letter format without any file name.
2. Both word and pdf files saved in my folder read as “word found licked fields during update. Word can’t update locked fields “
Kindly help. Thanks in advance
Thanks. Jin. It is a very useful and timesaving clerical job which is widely used in corporates and other sectors.
One my relative is in the HR department and he used to print and save the Increment / Bonus Letters individually by each employee and send by email to them. You can imagine how painful and time-consuming job for more than 500 employees.
I shared this example and he is very happy to save his time by such Automation.
Thanks Jin Again for your efforts.
Thank You for a clean crisp code! It has provided me the solution in the first go!!!
Thank you so much. Initially had blank pages, had to import and run the module from within the project/document section, not the normal section in the VBA editor tree window on the left screen (don't know proper name right now).
I have the same issue, what do you mean by running the module from within the project/document section ?
Thanks for your help !
@@johnvanco2959 in the vba window tree on the left. It will list a generic version and a line for the document you have open that you want to run it in. Click on the actual doc and then run/press play
Thanks for posting your solution!
Hi Jie, how do I send the individual file to fax numbers? I need to create letters that will go to a list of about 1,000 providers, to let them know their new id number. But it has to go to their individual fax number which is in a column in the excel sheet. I saw there is an option: .Destination = wdSendToFax
how do I use that?
Thank you for your video. It is so illustrative.
Do you mind send to a fax machine?
@@jiejenn i have the spread sheet with providers, provider id and their fax number. So each letter has to go to the provider fax machine. Is that possible?
To be honest, I am not sure. I haven't fax anything since 2010, so don't know what to tell you.
Hello,
When I run the VBA and it gets to the .OpenDataSource line of code, another window opens up, the says “select table”, I then click my workbook, click options, then click “system tables” so that the table I am referencing shows up in the window. I then click that table, click ok then nothing happens. No error messages, nothing. Can you help? Thanks
Not sure if I can help you given such little information you provided. If you want, you can send me your data file + Word document (must contain the VBA code) to TH-cam@LearnDataAnalysis.org and I can take a look when I have time.
Hi, Thanks for the tutorial. It doesn't work when I followed it on a Mac, quite possibly something is different between the two platforms. The macro ran, no error, but no files have been created. I'm not an expert in VB, though
Hi Zarina, I think the object references are a little bit different on Mac. Unfortunately I am not a Mac user nor do I own a Mac PC. Your best bet is look for someone who has a Windows PC and try the mail merge and see if it works.
@@jiejenn i have the same problem with Zarina, and I'm running Windows. help?
i.m using word 2013 .. is there a different?
I have had the same difficulties. I was able to get the script to MOSTLY work though.
Line 19: The 1 to TotalRecord doesn't seem to work, but if you replace TotalRecord with the actual total number of records then it works.
Line 33: I cannot get it to save PDFs. If I comment out this line, the script will create unique word files for every merge record, but my Mac chokes on wdExportFormatPDF
Works great form me!!! Thanks a lot for your script. ;-)
Is it possible to make pdf file output encrypted. Usually i do manual save as page 1, input password xxx, next save as page 2 input password yyy, and so on....
hi, thank you for this, i have a question - i keep getting a debugger error on the open source data line. i see your folder is in C drive, i have to use a W drive - is that the issue? also i get a pop up asking me to select a workbook. any suggestions would be really helpful. thank you!
I don't think drive location should matter, but your error to me seems like Word cannot find the file. The pop up is caused by Word doesn't have the source file linked.
If you want, you can send me your VBA code to youtube@learndataanalysis.org and I can take a look at it.
same issue here
I fixed this by unselecting the 'preview results' option in word. It then worked fine!
Hello, I just used this and it was very helpful and it did work, but when I open the certificates in my PDF or Word file, the document comes blank. How do I fix this?
There's probably a typo somewhere in your code. This happened to a viewer recently, and he spotted a typo that was causing the issue.
What if you have multiple letters for the same person? Could a file contain multiple letters for the same person?
Hi JJ, I getting a 'Microsoft Data Link Error' which reads as 'Test connection failed because of an error in initializing provider. Cannot start your application. The workgroup information file is missing or opened exclusively by another user'.
while I have not opened the excel file nor it is being used/accessed by anyother program. Please help.
I have a question,
If I just need the pdf File, Can I write a code to delete the files?
or can I make a pdf file without creating a word document?
Thank you.
You can directly create the PDF without creating the word doc.
This is very helpful. It helped me accomplish my task! Thank you so much!
I get a "Run-time error '5491': The requested member of the collection does not exist.
It creates one copy and crashes there. Any thing I can do?
You have a typo somewhere.
You have saved me a lot of time! Thank you!
dear sir thank you for your tutorial
The code gets to the "TargetDoc.Close False" line and then stops to ask me if I would like to save the document. I have to press yes, otherwise it does not continue. Do you know how to automate the "yes" clicking? Thank you in advance.
I solved this problem by adding "If ActiveDocument.Saved = False Then ActiveDocument.Save" directly above "TargetDoc.Close False"
THANK YOU! This video, and especially the link to the VBA code, saved me hours and hours of work!
I tried this VBA. It works, but not same as what he says " Automate Mail Merge to Save Each Record Individually in MS Word". The VBA Mail Merge produces many records and saved in one file. I expected to have one record saved in one file.
Most of the people seemed to have success with the script. I suspect there may be a typo or two somewhere. Have you try to step through each line and to see what does each line does? It is easier to figure out where the issue is that way.
Thanks a lot for sharing this wonderful tool. As a public server I need to send legal sheets with same info to different people and it works great! I dont have great knowledge of VBA but you made it so easy. At first I had troubles but with patiente I made it work. Greetings fro México.
thanks, very usefull, but how if want to save with two DataFields, example, name and date
My documents are all being created, but they are all coming back blank. Anyone have any idea why that my be happening?
Change MainDoc=ActiveDocument.
This is great!! Thank you brother!
Thanks so much for the tutorial. Very clear. Can you provide the macro text pls.?
Thank you for the tutorial. Anw, I wanna ask you one thing if you don't mind. How to save each record as PDF if my data source is .CSV not .XLSX? (background: I have some field that exceed 255 limit in mail merge, so I have to save it in CSV in order to make it work). Thank you.
You should be able to change your data source from Excel to CSV.
@@jiejenn Thank you for your response. When I change my data source to csv, the formula can't work. Let say, if I type:
Const SOURCE_FILE_PATH As String = "D/MyName/MyFile.csv",
nothing will happen after this. It only could run if I use xlsx format (ex: MyFile.xlsx).
How to make the formula work? (I use windows 10 and office 365)
This is awesome. Thank you very much. Working perfectly fine
You are a legend!!!Thanks for this.
Thanks its very helpful kindly share the another vdo for all records save in one pdf format
This was a lifesaver thank you so much for this workaround!!! I had to watch a few times, but very easy to follow. Had to remove the SQLStatement though.
Same here, thank you Sharrod
@@YippeeKanYe i have a problem with that line as well, did you delete the sql statement after the comma or the whole line? (. Opendatasource name=....)
@@f.p.186 I deleted the whole line i.e. .Opendata...
Woah!! This is awesome Jie. Thank you very much. Working perfectly fine.
It was great help... i have one more ask i have in the corresponding column password to be input for that file. Note password is different for each file. May be you can think like a SSN Number or Driving licence number for each person as password for them. CAn you help please.
If you want to include password to Word Document, the SaveAs2 method has a parameter called password, and you can supply your password to that parameter.
TargetDoc.SaveAs2 FOLDER_SAVED & .DataSource.DataFields("Client_Name").Value & ".docx", FileFormat:= wdFormatDocumentDefault, Password:=""
Thank you Sir! Appreciate your quick reply. however its like this for File 1 the password is 1234
File 2 password is 4567 and so on and its different for each file like mail merge we have different name and id etc..so can we something to input password while saving only with the mail merge from the Excel sheet it can pickup the password
Dude!!! This worked so well!! You saved me hours of work, thank you so so much!
how to create with button of source_file & file save_as (dinamic path)?
folder & filename picker
Thanks so much for all the effort! This saved my day
Thanks a lot for sharing this knowledge with us it was very useful. Please advise if we can separate individual documents from a mail merged consolidated file as well? can we use with the same method by making the source as that word file?
Well you can, but depends on the document content organization. Are the pages number consistent for each file? or the page number varies.
It is possible to generate a word template from every excel row individually. In excel I need a button to appear on every new row I populate with data and the button must open the word template that was fill with the data that I populate the row. And also i need the autofill word template to be save in a specified location. Thank you!
Yeah it is doable, but because you want to tie each row per button, you will have to manually re-link the button to each row. It is a pretty time consuming process if you have many records though.
@@jiejenn Thank you!
Thank you for posting this video. The .Destination = WdSendtoNewDocument seems to make a long word file with every generated document from the Mail Merge put together. Any way how to change it so it just makes the one custom document for each line of source data?
Not sure if it follow. Can you be more specific.
mind blowing, superb knowledge you holding and speed of writing macros
Greatly appreciate your sharing this. Struggled to find an effective solution elsewhere. Been awhile since I coded last. Your delivery was excellent and easy to replicate. Only real hiccup I had was the SQL code. SOURCE_FILE_PATH was correct (I set a Watch) yet stepping through the line, I'd consistently get the full filename path truncated (e.g. c:\1\2\3\file.xlsx, came back as c:\1\2\file.xlsx). Solution: Replace database$ with the name of the table containing the data in the xlsx. Worked beautifully.
Thanks!
Glad my video helped.
I have used this to help me with my work and have colleagues asking to use it - when I have shared it to them they can’t get it to work is their a way in which this can be used by multiple users ?
There should be an error message inducting the issue.
Managed to resolve thanks
Thank you so much, it's so easy to follow your step
Did anyone solve for Word for Mac!?!?! This has been something I have been working on for FAR too long!
Unfortunately, VBA support on IOS is very limited, and some of the Word objects are missing comparing to Windows. If you are on a Mac, my recommendation is to either borrow a Window's PC or look for an alternate.
how to custom add starting and ending record?
Hello. Great work. I tried the macro but I get the following error 5941, element not found for the line TargetDoc.SaveAs2 FOLDER_SAVED & .DataSource.DataFields("Client_Name").Value & ".docx", wdFormatDocumentDefault Do you have any idea why? Thank you very much
you have to replace "Client_Name" with the header (field) of your input table
@@giorgiolupato6553 how to create with button of source_file & file save_as (dinamic path)?
Perfect, thank you so much..
I do follow the step that you give. But why the result is blank document and pdf? any idea about this issue?
Without looking at your code, your file, and the error you get, it is nearly impossible to help.
I would suggest you post your question on Microsoft Word Forum.
Thanks this is very helpful and simplified compared to all the complex threads out there.
I love you.
Thanks man!
Thanks. This is very useful & I can successfully try even I don't have any IT knowledge. Thanks so much!! And would like to ask if it is possible to set a password in the PDF??
VBA Save function doesn't have set password option. You might have to use 3rd party software to automatically set PDF files' password.
@@jiejenn Thanks a lot^^
Thank you so much please how to add a picture to the file
I'm not following your question.
@@jiejenn Let's say we have a logo folder for each client and we want to put this image on the customer's message
Very nice...great way to explain and fully functional...thank you
Hey Jie Jenn, thanks for this tutorial. I am trying to do the same with Publisher, but I can't figure out what to do. Could you please somehow look at my code and give me an advice?
Do you mean Windows publisher?
@@jiejenn I am very glad you answered. Yes, Microsoft Publisher. I have Mail Merge, it is connected to excel sheet. And I want to convert each record to separate PDF. I wrote a simple script, it generates PDFs with correct names, but for some reason each PDF contains only the second record from excel.
Amazing will save me a lot of time. Thank you. Subscribed for life :-)
You are a lifesaver! There were some moments where I hit a roadblock, but meddled here and there and it saved my team's time, a LOT of it! Thank you so much sir.
Glad my video helped. Cheers.
Hi, Many thanks for sharing this tutorial. It's amazing and so helpful. It's saving both in word and pdf format; is there any way to avoid saving in word format- please advise.
To avoid saving the word file, I think you can just comment out the line that saves the word file.
Thank you Jie Genn, the video explains all I'm looking for.
I'm practically new with VBA Codes and I'm having a hard time understanding how you went about the "destination" which bears the mail merge automation. Could you do a little explanation on that?
Thanks.
Not following your question. What do you mean by "destination"? Do you mean save your files in a different folder?
I meant the first line " folder_saved as string" the folder named "destination" I would like to know it's content and how to go about it?
I know the second file is the Excel sheet containing the data. Can I work with an "xslm" file in this case
Hi, i want to get output file as .jpg format tried changing extension, but it doesnt work.
I don't think it is possible to save as a jpg file.
@@jiejenn hey jie thanks for reply.. or may be .png ( it is actually a certificate)
You are savior! Thank you so much! This was smooth like anything!
This is fantastic thanks for sharing
Thank you very much, you save a lot of my time !!!
Glad my video helped.
What a great tutorial!! I have one question. I need to put a repeated name in the file name. For example "Approval - Project" . The word Approval has to be in all my file names. How do I include the in the macros?
Within the TargetDoc.SaveAs2 you can concat any addition text you want to include. For example: TargetDoc.SaveAs2 FOLDER_SAVED & .DataSource.DataFields("Client_Name").value & " Approval - Project" & ".docx"
@@jiejenn can you also do any additional text infront of the name? what would be the code? I've been trying but no idea...
Hi Jenn,
Pls advise, how to select rows to merge. I do not want to delete previous data.
After you have connected to your database file on the Word Ribon choose edit recipient list. This is the Microsoft article on the subject.
support.office.com/en-us/article/mail-merge-using-an-excel-spreadsheet-858c7d7f-5cc0-4ba1-9a7b-0a948fa3d7d3
Hello, Many thanks for the video. This was great!
My question is In case I want this to be done only for a group of the rows, for example lets say on a column I have category 1,2 and 3, and I want to do this for category 3 only, how should I modify the VBA to apply the filter?
You can actually do that directly in a SQL statement. From the OpenDataSource statement, you can include what we called WHERE clause to include criteria.
For example, assuming your column name is "Product Category" and you want to filter the dataset by label "category 1", you would write (noticing the extract double quotation):
.OpenDataSource Name:=SOURCE_FILE_PATH, sqlstatement:="SELECT * FROM [$] WHERE [Product Category] IN (""Category 1"")"
@@jiejenn
Many Thanks
But actually when I add this where clause, nothing happens... I sent you a video on your email, would you please kindly check?
Nice tutorial! I have some questions though. What if you already have a conditional statement (like skip record if mergefield Field_name is ""). This means the loop will still try to run the total number of records. What is the workaround? Can be filename also be dynamic? i.e. a function of text Manipulation?
Give me an example.
@@jiejenn Let's say from Excel you have 2 fields; Name and DOB. Now when doing mailmerge in Word, you put a conditional saying skip record if DOB = "". Also In Excel, the Name field is a combination of Title, Fname, Mname, and Lname from an original database. So your name field in excel will come as "Miss Jane A. Doe" or "Mr. John Doe" if there's no Mname.
This means when you do mailmerge you have just two fields name and DOB in word. From your tutorial, the code will go through the total number of records and save as .docx or export to .pdf regardless of what is in the field. This means it will even do a merge for empty fields as far as you have set up your mergefields in word as you would like them to be outputted.
So this is what I would like to do, use pagecount to know the number of pages it needs to loop through OR use and IF statement in the VBA code to know what records to actually save indiviudual documents for. It will also extract just the first and last name from each record.
Hi Jie, thank you so much for posting the tutorial. It is definitely what I need recently. I am new to VBA, and I tried to follow your video to make my mail merge works. There is an error "438" in the line of ".OpenDataSource Name:=source_file_path, SQLStatement:="SELECT * FROM [XXX$]", as i only change the sheet name you created to mine. Can you help me on this? Thanks!
I have the same issue. Please help us!
I had the same issue. Remove SQLStatement:="Select * From [Database$] and it should work
instead of -database- statement use the sheet name of your data
This is great and it works perfectly. I am wanting to expand on it a bit by selecting the records from the Excel file that have not been "processed" yet. I have that figured out, but I am trying to figure out how to update the excel field to show it as processed so the next time I run the macro, it doesn't pick that record. For example, I want to change the Processed field from No to Yes. Hopefully you understand what I am trying to do.
If you want to process rows based on a condition(s), after the loop, you can insert an IF statement to express your criteria. For example, if your field is called "Processed", the code would look something like
IF .fieldname("Processed").value = "Yes" THEN
[mail merge code block]
END
@@jiejenn I figured that part out after some trial and error. But what I want it to do is after it finds the records and creates the new documents, update the row in Excel so it does not have a duplicate made. For example, say I have 10 rows and I have a "Processed" column. 4 of the 10 rows have No in this column and 6 have Yes. The query finds the 4 rows that were not processed, your script creates the PDF, but I want those rows to change the No to Yes so next time I run the script, these rows will not be created again. Hope that makes sense.
@@ScottAlexander68 I think I understood your question fully now. If you want to update cells in Excel, it involves of adding Excel reference in Word VBA, which is a bit more advanced if you do not have experience in writing VBA. I will take this as an idea and to make a part 2 video in the future.
@@jiejenn Don't have much experience at all. That would be great and I am going to do some reading too lol.
I was able to run this but the word docs and PDF files that were created are blank. any ideas?
This seems to be a common problem. Can you send me your data file and your Word doc that contains the VBA code to TH-cam@LearnDataAnalysis.org.
Jie Jenn thanks for your reply. I figured out what happened; I forgot to change the name of the worksheet. Your tutorial is extremely helpful. I’m not kidding...what used to take me 4 hours to do now takes me less than 5 minutes. I’m a total noob but getting the hang of it thanks to you. I appreciate your time putting together these tutorials. Very easy to follow and understand.
Is it possible to add another field to the file name? Let’s say I want the company name to pull from one field but I would also like to include the cost center from another field. The file name should look like this: COMPANY_CC9210. Thanks!!
Jie, it was great, it worked first time. now the second document i'm getting all the copies saved by each name instead of individual page. any help?
Can you be more specific? Not sure if I understand your question clearly.
@@jiejenn thank you for your reply. i managed to work it out. thank you again. you are great!
Cool. Glad that you managed to worked it out.
Thank You. Lots of blessings for you... This is so powerful.
Awesome! Thank you!