Everything in this video is great: the macro, the clear explanation and the details of the code, the possibility to download the files. One of the best videos ever. Thank you so much for this pearl" 👍👏👏👏👏
This is, BY FAR, the best thing I've ever found on UA-cam in the last fifteen years. You are amazing! I had to mail merge over 1200 documents, each with a unique file name based on Student ID. I don't know how I can thank you enough. Mate, seriously! Cheers :)
Your comment is thanks enough. I am really glad this helped you, and most importantly that it saved you time. Now use that time saved to spend time with the family or go out for a drink on me :). Thanks for your comment.
THANK YOU so much. I knew this was possible but most suggestions online regarding splitting mail merges are to download a plug-in which I can’t always do as a contractor !!! You rock.
You're officially my new hero! I was trying to merge a document into individual files and all of the other video tutorials caused my images to disappear and the document spacing to get thrown way out of wack. This was absolutely PERFECT for my needs. And THANK YOU for making the code available for us to download.
That is great to hear. I am really glad this tutorial helped. Thanks for your comment. Just to let you know, i also have a tutorial on merging images. Particularly great for certificates.
THANK YOU A THOUSAND TIMES I had a 500 pages file to split into each page for each employee and our IT block sites like "I Love PDF" your macro worked like a charm I just renamed my sheet database to "MergeData" and added the three columns
I was searching for this process for a long time. When preparing service documents for equipment in the same models, even though mail merged and split into individual documents, I had to rename the PDF files one by one according to their serial numbers, which took a long time to open and check individually (had to repeat every month for different equipment lists). Thanks to your video, I could save my time from hours of hard work into minutes of clicking. Thank you soo much for making the life easier by bringing up this valuable video. Highly appriciate your effort and will not miss to share this with my friends as well. Thanks again.
Thank you so much for posting this! I had a file with 50 letters and it merged into one big one. I did not want to spend hours separating. You're awesome for sharing your knowledge.
Thank you so much. I had a little difficulty until I realized that I hade to keep the first 4 fields in the spreadsheet as you had them, then add my stuff after. Worked perfectly and loved the screen tutorials
I found this after a few other attempts to accomplish this functionality which didn't work, however yours worked immediately and without issue, absolute life saver! Thanks mate.
This is so helpful and is saving me HOURS of work. Thank you for the hard work you put in and the easy-to-follow instructions! If you ever have the time, I would love to see how to add to the macro the ability to add a sensitivity label to each document.
Thank you! I'm new to Mail Merge and I needed to create separate PDFs with the recipient's name in the file name. This video is perfect for someone who has no coding background. Your instruction is excellent! In case this is helpful for others: It took me perhaps two hours to go through the video, take notes, and prepare my own Word doc and Excel sheet to practice. When I cut and pasted the code from the .txt file, the process would not Run. Rather, I received a Syntax error. Again, I don't code, so I didn't know how to fix that. Next I deleted that Module (as he does in the video) and imported the the .bas file. When I selected Run, it worked! Holy cannoli! It worked! I'll be using this often. One million thank yous for creating this excellent video and sharing the files.
Thankyou for taking the time to leave your feedback. I am happy you found this usefull. I will look into the text ersion of the code to see what may have cause the syntax error. I have another mothid for breaking up a pdf in to separate files which is not as good as this one but works and is great if you have been given a merged pdf document. ua-cam.com/video/MsUpz6docZA/v-deo.html
This is amazing... I used mailmerge so many times in the years past, and it never occurred to me to look for a solution to save it in separate files... and for a big project I'm starting, this will save SO MUCH TIME, thanks! Very well explained, on top of everything!
@@JonnieDard sorry i missed your comment. Header and footers are not an issue. If you add the header and footer to your master document it will be included in all merged documents.
I can't thank you enough for this video. I'm glad I watched until the end because I only need .docx files and you made it very easy to understand how to edit the code. I have 3623 files to save, so THANK YOU THANK YOU THANK YOU!
I have been searching for this automation for ages! Thank you very much for explaining things in such detail that is so easy to understand and implement! Now I need to figure out how to email each file to the email addresses on the same data source! 😅
Thank you so much for this amazing and helpful video! Your explanation of the mail merge process and converting files into personalized PDFs for each record was clear and detailed. Your presentation style and organization were excellent . I truly appreciate your effort in preparing and sharing this professional content with us. I will definitely recommend this video to others who need to learn this skill. Thanks again!
So easy to follow. Would you be able to do a video or let me know how the documents could be saved in their own personal folder? So using your example have the merged paper saved in the students individual file you’ve created
Never thought there would be a need to generate folders for each file but yes it can be done and i will probably make a video soon. However if you want to have a crack a it here is how i would do it. In a separate column in the Excel file use text manipulation to ad the letters md followed by a space and then the unique file name, so it would look something like this without the quotes "md Nick_Paphitis" Duplicate this for all the entries. Copy this data and paste in a notepad. Rename the extension from .txt to .bat. Place the .bat file in the folder where you will export and double click on it. This will now generate all the folders. Now go back to the Excel file. You can delete the column you made. For the columns WordDocs and PdfDocs delete the location paths and create the path with the unique folder paths. Now each entry has a unique path instead of the same one and those folders already exist as you already generated them. hope that make sense.
You are my new hero too! Thank you so much! It perfectly resolved our technical issue how to generate individual files with ID in the file name. YOU ARE THE BEST!
Thanks for your comment. I am happy you found this useful. This is definately a process which is becoming a more popular task for schools and small businesses.
Please check the folder names and locations as this will be your issue. Field names must be correct. If you d check everything it will work. See pin ed and peevious posts.
Struggling again with this - by some miracle it worked last year after hours and hours of trying everything... cannot for the life of me figure out why it's happening again this year. Can you comment on what would cause a "run-time error 5941"? I have followed instructions to the T. Watched your video many times, went to another computer (in case Sharepoint or using macros were a problem), all to no avail. What could possibly be happening? Reworked all my documents, shortened paths to folder locations, etc. used your exact verbiage, used your exact templates. but cannot make it work...
Do not you synced cloud folders or prtected system folders like c: Use desktop or documents to be safe. Check file paths and spelling. Let me know if it works or if you still have issues.
Your tutorial is excellent! I need your advice and guidance: If the Excel source file includes an additional heading titled "Sports Interest" (e.g., Chess, Tennis, etc.), is it possible tp modify the code so that it saves a Word document/PDF containing all the pages of records for members with the same sports interest? Thank you.
Yes. If you have the fields in the spreadsheet then form the Mail merge in word you can filter the records and export only the records you want. Have a look at this and it will explain the process: ua-cam.com/video/taeWuL9VH18/v-deo.htmlsi=_Qo0lHwj9xJ5e1c5
Excellent work Nicos. This process created an incredible productivity improvement for me at work. Thank you so much. Is it possible to do this through excel only?
I am glad this helped you. Thanks for your comment. Excel provides only the data source, it does not do mail merge. You can take Excel out of the process and use a different data source like a database or csv file but not merge through Excel.
Good job! Really helpfull! I modified it a little bit though. The only thing that I changed that I added is a window that asks me to enter starting and ending record numbers cause I use list that I update every day and I don't want it to start from the first line every time
Good job. Interesting add. I normally have a column with and incrementing number and use this column to filter the recipient list (from word mailings edit recipient list). It adds a bit more flexibility than a starting point. You could have a date field and filter on date after. Thanks for you comment.
If you put an index infront of the name so that when sorted they are in the right order then yes. Instead of the name Nick use A1_Nick then the next name A2_ and so on. This way if you sort the files by file name they will be in the same order.
Make sure the folders you are saving to are not in protected windows locations, use documents or desktop. Also do not use cloud synced folders, they cause issues. Next check and make sure that the folders and their paths are correctly addressed in the data source file.
THANK YOU SO MUCH!! This VB File helps me a lot! I have created donation receipts for an NGO, now I have to send them individually by e-mail to 400 addresses. Do you have a recommendation for an Outlook or Gmail add-in? I'm thinking about buying the Merge Toolkit from Mapi lab. Or could this even be done with VB and macros?
Inam glad this helped you. Thanks for your comment. It can be done with VB code but i personally use the Merge Toolkit as it offers alot of other features you will end up using.
This is totally awesome I have been searching for the right video for this, and Yours is the BEST so far. It really helps me a lot in my reports as every month I have to generate a 5000 training certificate and have to rename them .... what a mess.
is this applicable to Office 365? Apps for Enterprise? the problem i encounter is that the original document close upon execution so only the first record is processed. It stops after processing the first record. How can i work around it?
Definately applicable. Dows it generate both the word and pdf of the first record? If yes check the file folder path and file names of the second record. To check, delete the first record so the second becomes the first. Run the macro, does it generate? If it does then you have probably made a change to the macro. Use the original macro.
@@paphitisn thank you for your reply and for your video. another subscriber here. Yes it always create the first record only. It generates both doc and pdf. I did not change anything. i just copy paste it, i also watch your video, checked them all lcharacter per character and renamed all my files to your files. My excel file however does have other columns, could it have affected the merge? how can i send a a screenshot of the error?
The best method i have found to be able to send the personalized pdfs to individual recipients is with Merge Tools Adin mergetoolsaddin.com/ it is free but you can donate. i have also used Merge Toolkit which has an annual cost www.mapilab.com/outlook/mail_merge/ Both will allow you to attach an individualized email to your recipients.
Thank you very much! This is very useful, and I am able to add new field named OUTPUT and only export docx when I put “Y” under the field. However the generation is a bit slow and I am wondering is there any trick to improve the speed? I am export around 300 documents, around 8 pages long.
Well done for modifying it to improve work efficiency. As for the speed, it depends on your device, amount of RAM and the length of the document as the documents are kept open until the process finishes. You may find it gets slower as it progresses if you have a lot of documents. I would suggest add another field so that you can add a number like 1, 2, 3 etc. add the number 1 for the first 50 records, 2 for the next 50 and so on. This will allow you to filter on this field and only merge 50 documents at a time. This should improve the speed as you will only be merging 50 records at a time ana you will avoid the long delays when you are on the 100th or 20th record. Let me know if this works for you and improves the speed for you.
Nicos this is really useful, thanks very much. I am using this along with someone elses VBA code which then sends emails with the custom PDFs created by yours. I am wondering if yours would also be able to send the custom PDFs to custom addresses (stored in an extra column in your spreadsheet)? To have both processes in one vba would be amazing. I am not enough of a coder to splice the two bits of code together. Thanks!
Definately possible. I however use a Word addin for sending the personalised pdf docs as attachments. It allows me to send personalised emails, cc bcc and a few other options also. To recreate this from the start would be alot of hassle. If you want, send me the vba code you are using and i will check to see if it can e merged with the mail merge code.
This is not working for me. I keep getting an Error 5941... I read somewhere it is because i am on a shared network, I am doing this on my work laptop so i have moved all files to my system c drive and it still will not work........
I suggest using your desktop or documents folder as the C: may be prtected. If it does not work in these lications then i suggest you double check the paths of folders and names of columns in the data source.
Thx from Germany. Big ups. This code goes so hard. I am trying to make it more general so that basically anyone can use it without having to make folders and maybe a gui so you can type in how files can have individual names :) Anyways big help. I am super happy about this.
Thank you your feedback. Your project sounds great. Please let me know how you get on and please share your work with us if you get it working. It sounds awesome. I would be happy to make a tutorial on it (and of course reference you ) if you get it working. It will help a lot of people.
@@paphitisn Right now I just got it to save more data fields. I am having an issue with spaces and parenthesis. ... DataFields("Nam new").Value... If I replace the space with underscore it works but I also have ... DataFields("Nam (new)").Value... Even if I replace the underscore it still throws a runtime error. Also amazing documentation. I am used to look at completly wild and uncomented code so this is a blessing. I am not new to VBA but it is has been a while since I used it :)
Hi there! Thank you for your video. I'm running into an issue with the module and I've checked and rechecked the file names and paths on each of my documents and there are no issues. I even tried deleting the Word exportation section of the code to see if it would just run the PDFs, but unfortunately it ran with the same error. Any thoughts on how to fix this? Thanks!
Without knowing what issue you are running into i will assume you have tripplechecked evreything. If this is the case then the issue maybe the location where your folder is. Do not use a synced cloud folder like dropbox or onedrive. Also dont use c:\ as you may need elecated privileges to write here. Use a folder on the desktop. Make sure to add the new path in the data source. Let me know how it goes.
Our desktop is on the c:\ folder, so I tried to move it over to my personal MacBook (and adjusted the path names), and I'm getting the error around the "ExportAsFixedFormat" command. Any thoughts? @@paphitisn
Great Stuff, especially the VB code. My imported code stopped - and I worked out I had duplicate entries in the FileName field. In my large file, I ran the conditional format duplicate feature in the MS Excel and found the problems. Thanks for the work and clues.
Thank you Nicos, This is such a useful Tutorial. Thank you also for the downloads. I am getting an error message run time error 5852 requested object is not available. Something to do with marking the last page - do I have to edit the document on the last page with a zero?
Makesure you are not saving the files in a synced cloud folder like onedrive or in a protected location. Use documents or desktop location. Make sure you have acurately entered the folder locations in the spreadsheet. Make sure the headings of the columns in the spreadsheet are exactly correct unless you have changed the code. Let me know how it goes. Your error is related to it not finding the folder locations so double check everything.
Hey, Thanks you so much for the helpful video! Can you please help me with one issue. I have one page word document, but when i run the code the document got one blank page and i do not want this blank page. I have tried to find solution but nothing have worked. Please help!
Is the a Word setting i need to change before i put and run the macro? Because once i "Run" the macro, it still pops up the "Save as" dialog box and i will have to save it manually.
No, the only thing you need is to enable the Developer ribbon so you can access Macros. Which you have done as you can see the Macros section in the developer ribbon. Have you imparted the .BAS file. If not do that instead of creating a new macro and pasting the code.
The macro should be working fine. Check filder paths, check the names of the columns so that the are the same as in the video. Make sure you thebdestination folders are not in a cloud synced drive ot protected folder. Use a folder on the desktop or in documents. Follow the instructions in the video exactly and it will work. Thousands have used the macro and it works for them.
hi this video was very helpful. but am getting a runtime error 5941 for the below code: SingleMergeDoc.SaveAs2 _ FileName:=MasterDoc.MailMerge.DataSource.DataFields("DocFolder").Value & Application.PathSeparator & _ MasterDoc.MailMerge.DataSource.DataFields("FileName").Value & ".docx", _ FileFormat:=wdFormatXMLDocument
Heck the folder names and the path you have for those folders in the data source. Also make sure the folder are not in syned cloud folders or protected folders like C:\ or windows folders. Use Desktop or documents. Let us know how it goes.
Hi, Thank you for the video. It is exactly what I have been looking for. Unfortunately I am receiving an error when it is running the macro. It creates the first word doc but fails to create the first pdf document. I receive a run-time error message '5941': The requested member of the collection does not exist. I have followed all of the steps and rewatched three times, but i'm baffled as to what I am missing. Do you have any suggestions what could be going wrong? Many thanks, Matt
Yes, check the field names you have specified in the macro and make sure they are exactly the same (small and capital letters) in the spreadsheet. Check that the file paths specified in the word and doc column are correct. Make sure to let me know if this has helped and you have resolved your issue. If the first word document is saving then it is a likely one of the 2 suggestions above. You could always remove the save to PDF commands and see if it exports all the word documents to make sure everything in the macro works.
I have a question, I'm using this to create certificates but when the automation starts the first page is the word document template but the second one is an empty page, is there a way to remove said blank page?
This means that your .erged fields are pushing the text down to the point where the insertion point is going to the next page. Make your top and document margins a bit smaller so that when you .erge the feilds there is mor page length and hopefully the insertion point will not move to the next page. Also make sure you do not have any blank lines at the end of your master document. Let me know how this goes for you.
You can download all the files i used including the code and the .bas VB module file from the link i provided in the description of the video (at the end of the description). It is easier to use the .bas file as this will import the macro for you automatically as explained in the video.
THANK YOU. This is exactly what I was looking for, and your teaching is PHENOMENAL. However, I followed your instructions but I get "Run Time Error 5". It only saves 1 word doc and cannot proceed any further. can you please help? I keep your excel column names exactly and have not changed anything. I give permission for the macro to access the folder where files are being saved and everything. Any ideas what can be wrong?
If it is saving a file and stopping it means that you must have an error in your folder names or location. Is it generating both the work and pdf for the 1 file? if no then look at the pdf info. If yes check your data source as you may have an error in the file path for the second one. Be careful of leading or trailing spaces in your data source for the paths.
Hi Nicos - how do I modify the export block (SingleMergeDoc.ExportAsFixedFormat) to export it as html (is this possible)? I tried a couple of things but they didn't work. Thanks!
if you look through the comments, everyone that faced issues the problem was with the names of the paths, columns. Also make sure you are not saving the merged documents in a cloud synced folder or protected folder. Use the desktop or documents.
@@paphitisn Thanks - it turned out to be that when I copied and pasted it - it added some additional words between lines and I just had to go in and delete them. Thanks for coming back to this though, your help is much appreciated!
How could I tweak the macro so it only generates the documents i have checked in the Mail Merge Recipients. I.e. lets say i have those same 12 lines but i only check record number 1 and 3 (so those are the ones id like to generate). I'd like to update certain records only.
Chances are you are filtering the data instead of Edit Recipients. Go to Mailings ribbon in word, click Edit Recipients and then apply your filter or simply select the records you want to merge. When you run the macro inly those records will e merged. Here is a link to a tutorial on Mail Merge that explains everything you need to know on mail merge. ua-cam.com/video/taeWuL9VH18/v-deo.html
Thankyou so much for this, do you have a video that explains how I can now send the PDFs to the respective candidates/recipients? i feel like this should be easy but I cant get my head around it.
there is no native method from Word. However you can purchase the Mail Merge Toolkit which is a word add-in and will allow you to send personalized attachments via mail merge, which is what you want to do.
Is there any way to download the VB Code without having a dropbox account. It does not permit me to download from my official laptop and I need this for my team.
Sure, here is a download link. Let me know if it works for you: thejuniorschool-my.sharepoint.com/:u:/g/personal/nick_theseniorschool_com/ETex9B3wX4tCoAAL633oa2YBCZU9EcOBH2ue0me2lDjoOg?e=PzvYeF
make sure the folder you are saving the documents in is not a cloud synced folder like dropbox or onedrive. Also make sure the folder is not in a protected location like directly in C:\ Use a folder on the desktop to make sure.
@@paphitisnIn my case, on a daily basis i use your method and keep on adding the data in the same file order wise, so how to comment off the previous data so that only new loaded data should merg.
@shivagunti add another column in your data. It could be a date or a simple incrementing number. When you add the next data add the next numbe so if the previous number was 15 then all the new records will have the number 16. Before you merge filter the data on the records which equal 16 in that column. Then merge. Or use a date field and filter on date added. This will allow you to also track when records were added
This one is very helpful to understand how to merge files from excel to word. However, I have done the procedure and INSERT MODULE1.bas and still got error as "runtime error 5941 word ". Can you help me?
Please read previous posts where others had issues as they were all solved making sure the paths on your local drive exist and are correctly named in your spread sheet and make sure the file names are the same in the macro.
HI! THANK you for sharing this. Your video was very helpful for me who is not a techy person. I have successfully download MS WORD files. Unfortunately, not working with PDF.
Check the column names in your Excwl file. They must be exactly the same as they are written in the code. Also make sure the folders are named correctly. Check these and it will work.
![Split a Mail Merged document into SEPARATE documents with custom file names [No Coding]](http://i.ytimg.com/vi/MsUpz6docZA/mqdefault.jpg)
![Split a Mail Merged document into SEPARATE documents with custom file names [No Coding]](/img/tr.png)
![Beginner Power BI DAX Functions Tutorial [Full Course]](http://i.ytimg.com/vi/QJw4HkagVWc/mqdefault.jpg)
![Mail Merge PICTURES to documents [step by step]](/img/n.gif)
Everything in this video is great: the macro, the clear explanation and the details of the code, the possibility to download the files.
One of the best videos ever.
Thank you so much for this pearl" 👍👏👏👏👏
Thanks for your comment. I am glad you found this usefull.
This is, BY FAR, the best thing I've ever found on UA-cam in the last fifteen years. You are amazing! I had to mail merge over 1200 documents, each with a unique file name based on Student ID. I don't know how I can thank you enough. Mate, seriously! Cheers :)
Your comment is thanks enough. I am really glad this helped you, and most importantly that it saved you time. Now use that time saved to spend time with the family or go out for a drink on me :).
Thanks for your comment.
@@paphitisn thanks 😁👍
THANK YOU so much. I knew this was possible but most suggestions online regarding splitting mail merges are to download a plug-in which I can’t always do as a contractor !!! You rock.
Really glad this has helped you. thanks for your comment.
Sir you just saved my life!! I have 700 students to personalize a pdf file for and this just was perfect !!!!
Awesome, i am so glad you found this and saved yourself a heap of time. Thanks for the comment.
Amazing video, but even more amazing instructions, in detail, and with the possibility to skip to the part I need. Keep up this extraordinary work!
Really glad you found this usefull. Thanks for your comment.
You're officially my new hero! I was trying to merge a document into individual files and all of the other video tutorials caused my images to disappear and the document spacing to get thrown way out of wack. This was absolutely PERFECT for my needs. And THANK YOU for making the code available for us to download.
That is great to hear. I am really glad this tutorial helped. Thanks for your comment. Just to let you know, i also have a tutorial on merging images. Particularly great for certificates.
THANK YOU A THOUSAND TIMES
I had a 500 pages file to split into each page for each employee and our IT block sites like "I Love PDF"
your macro worked like a charm
I just renamed my sheet database to "MergeData" and added the three columns
That is really fantastic to hear. I am glad this has saved you time and effort. Thank you for your comment.
I was searching for this process for a long time. When preparing service documents for equipment in the same models, even though mail merged and split into individual documents, I had to rename the PDF files one by one according to their serial numbers, which took a long time to open and check individually (had to repeat every month for different equipment lists).
Thanks to your video, I could save my time from hours of hard work into minutes of clicking. Thank you soo much for making the life easier by bringing up this valuable video. Highly appriciate your effort and will not miss to share this with my friends as well.
Thanks again.
Really glad this tutorial has helped save you work hours. Thanks for your comment and for sharing it with others.
@@paphitisn ❤️😇
Thank you so much for posting this! I had a file with 50 letters and it merged into one big one. I did not want to spend hours separating. You're awesome for sharing your knowledge.
So glad this helped you. Thanks for your comment
Amazing! Saved me hours and hours of editing files one by one! Thank you!
That's great to hear. Thanks for your comment
Thank you so much. I had a little difficulty until I realized that I hade to keep the first 4 fields in the spreadsheet as you had them, then add my stuff after.
Worked perfectly and loved the screen tutorials
That's fantastic. I am glad it worked for you. Thanks for coming back to leave your feedback. I am sure it will help others
Thank you. That's what I needed.
Omg your comments saved my life!!! Thank you both!!
Thanks, Nicos, what a time saver and really simplified explanation of the whole merging / save file process
Hi Peter. I am glad you found this useful a d easy to follow. Thanks for your comment.
I found this after a few other attempts to accomplish this functionality which didn't work, however yours worked immediately and without issue, absolute life saver! Thanks mate.
That's reallybgreat to hear. I am glad it worked for you. Thanks for your comment
You have saved me hours of typing and saving. THANK YOU for this video. Very easy to follow and worked like a charm
i am so happy this helped you. it really is a timesaver. Thanks for you comment.
Genius, exactly what I was looking for and now I actually know how it works
I am glad this heled you. Thanks for your comment.
This is so helpful and is saving me HOURS of work. Thank you for the hard work you put in and the easy-to-follow instructions! If you ever have the time, I would love to see how to add to the macro the ability to add a sensitivity label to each document.
From what i know, that requires an A5/E5 license which i do not have.
I am glad the this has saved you hours of work. Thanks for your comment.
Hats off guru ji........from India
Really i experienced my schooling days by your explanation. Its crystal clear.
Thank you for your kind words. I am glad my tutorials were useful to you.
Thank you! I'm new to Mail Merge and I needed to create separate PDFs with the recipient's name in the file name. This video is perfect for someone who has no coding background. Your instruction is excellent! In case this is helpful for others: It took me perhaps two hours to go through the video, take notes, and prepare my own Word doc and Excel sheet to practice. When I cut and pasted the code from the .txt file, the process would not Run. Rather, I received a Syntax error. Again, I don't code, so I didn't know how to fix that. Next I deleted that Module (as he does in the video) and imported the the .bas file. When I selected Run, it worked! Holy cannoli! It worked! I'll be using this often. One million thank yous for creating this excellent video and sharing the files.
Thankyou for taking the time to leave your feedback. I am happy you found this usefull. I will look into the text ersion of the code to see what may have cause the syntax error. I have another mothid for breaking up a pdf in to separate files which is not as good as this one but works and is great if you have been given a merged pdf document.
ua-cam.com/video/MsUpz6docZA/v-deo.html
THANK YOU for taking the time to put this together. This helped me tackle a project, saving countless hours. Cheers!
Thanks for you comment. I am glad this was useful for you and that it saved you some work hours. Hope you used those hours saved wisely :)
BRILLIANT!!! And no blank page at the end of the word document!! Thank you so much!!!!
I knew you would like this tutorial. Glad it worked out for you.
This is amazing... I used mailmerge so many times in the years past, and it never occurred to me to look for a solution to save it in separate files... and for a big project I'm starting, this will save SO MUCH TIME, thanks! Very well explained, on top of everything!
Really glad you found it useful. Thanks for coming back and leaving your feedback
Fantastic Nicos. This is really helpful. But I’m wondering how I can get this done when there a header with a logo and a footer ?
@@JonnieDard sorry i missed your comment. Header and footers are not an issue. If you add the header and footer to your master document it will be included in all merged documents.
Thanks for this! It's outrageous that this hasn't been built into Office yet
Totally agree with you. This should be built into the merge feature of Word.
Thank you so much for providing this video! This saved me so many hours of creating individual PDF documents.
I am glad this helped you. Thanks for your comment.
I can't thank you enough for this video. I'm glad I watched until the end because I only need .docx files and you made it very easy to understand how to edit the code. I have 3623 files to save, so THANK YOU THANK YOU THANK YOU!
I have been searching for this automation for ages! Thank you very much for explaining things in such detail that is so easy to understand and implement!
Now I need to figure out how to email each file to the email addresses on the same data source! 😅
You an use Mail Merge Toolkit addin. It is free and easy to use.
Good evening you know where i found the free Mail Merge Toolkit addin, because all that i found is not free@@paphitisn
Thank you so much for this amazing and helpful video! Your explanation of the mail merge process and converting files into personalized PDFs for each record was clear and detailed. Your presentation style and organization were excellent . I truly appreciate your effort in preparing and sharing this professional content with us. I will definitely recommend this video to others who need to learn this skill. Thanks again!
I am really glad you found this usefull. Thank you for your comment.
Wow! Thank you so much for this tutorial! Well organized and perfectly explained, great job!
hands down neatest solution
thanks for your comment. glad you found this useful
Thank you very much for the excellent presentation, informative explanations, and valuable module. Your efforts are greatly appreciated!🙏
you are welcome. Thanks for your comment
This was awesome, thank you so much for the step by step guide and the macro. Saved me so much time!
Glad to hear it. Thanks for your comment.
Dear Nicos thank you so much for the the code and the tutorial, this has saved me so many hours
I am very happy to hear that. Thanks for your comment
Amazinglys detailed presentation!!! So kind of you to share your work with us!! thanks is not enough!!!
Really glad this helped you. Thanks for your comment.
Amazing video, thank you for showing every detail!
You are more than welcome. Thanks for your comment.
So easy to follow. Would you be able to do a video or let me know how the documents could be saved in their own personal folder? So using your example have the merged paper saved in the students individual file you’ve created
Never thought there would be a need to generate folders for each file but yes it can be done and i will probably make a video soon. However if you want to have a crack a it here is how i would do it.
In a separate column in the Excel file use text manipulation to ad the letters md followed by a space and then the unique file name, so it would look something like this without the quotes "md Nick_Paphitis"
Duplicate this for all the entries. Copy this data and paste in a notepad. Rename the extension from .txt to .bat. Place the .bat file in the folder where you will export and double click on it. This will now generate all the folders.
Now go back to the Excel file. You can delete the column you made. For the columns WordDocs and PdfDocs delete the location paths and create the path with the unique folder paths. Now each entry has a unique path instead of the same one and those folders already exist as you already generated them.
hope that make sense.
WOW!!! You are my new hero. Thank you SO much for creating and posting this video. I don't actually care HOW it works, just that it DOES work!!!!! :)
Haha, i am glad you found this usefull. Thanks for your comment.
You are my new hero too! Thank you so much! It perfectly resolved our technical issue how to generate individual files with ID in the file name. YOU ARE THE BEST!
I am so glad it helped. Thanks for your comment.
This is unbelievable Nicos is a star
Thanks for your comment. I am happy you found this useful. This is definately a process which is becoming a more popular task for schools and small businesses.
My man you are awesome . You have no idea how much time you have saved me .
I am happy it helped. Thanks for your feedback
Much appreciated Nicos, very useful info. Worked on my first try!
That is fantastic. Well done. Glad you found it useful. Thanks for the comment.
I tried this code on another word document. But after running the code it show - Run time error 5852. Requested object is not available.
Please check the folder names and locations as this will be your issue. Field names must be correct. If you d check everything it will work. See pin ed and peevious posts.
Struggling again with this - by some miracle it worked last year after hours and hours of trying everything... cannot for the life of me figure out why it's happening again this year. Can you comment on what would cause a "run-time error 5941"? I have followed instructions to the T. Watched your video many times, went to another computer (in case Sharepoint or using macros were a problem), all to no avail. What could possibly be happening? Reworked all my documents, shortened paths to folder locations, etc. used your exact verbiage, used your exact templates. but cannot make it work...
Do not you synced cloud folders or prtected system folders like c:
Use desktop or documents to be safe. Check file paths and spelling. Let me know if it works or if you still have issues.
Your tutorial is excellent! I need your advice and guidance: If the Excel source file includes an additional heading titled "Sports Interest" (e.g., Chess, Tennis, etc.), is it possible tp modify the code so that it saves a Word document/PDF containing all the pages of records for members with the same sports interest? Thank you.
Yes. If you have the fields in the spreadsheet then form the Mail merge in word you can filter the records and export only the records you want. Have a look at this and it will explain the process:
ua-cam.com/video/taeWuL9VH18/v-deo.htmlsi=_Qo0lHwj9xJ5e1c5
very much appreciated! Excellent work and super complete! Love it and it helped me out so much. Thank you very much. Greetings from Belgium.
That is great to hear. I am glad it helped you thanks for your comment.
Thanks For your help - I already was ready to start doing it by myself!
I am glad it helped
Excellent work Nicos. This process created an incredible productivity improvement for me at work. Thank you so much. Is it possible to do this through excel only?
I am glad this helped you. Thanks for your comment. Excel provides only the data source, it does not do mail merge. You can take Excel out of the process and use a different data source like a database or csv file but not merge through Excel.
Thank you so much for this tutorial & the macro file. You saved me so much time & headache - and I learned a lot!
I am glad this was useful for you. Most of all i am glad you learnt a lot :) Thanks for your comment.
Good job! Really helpfull! I modified it a little bit though. The only thing that I changed that I added is a window that asks me to enter starting and ending record numbers cause I use list that I update every day and I don't want it to start from the first line every time
Good job. Interesting add. I normally have a column with and incrementing number and use this column to filter the recipient list (from word mailings edit recipient list). It adds a bit more flexibility than a starting point. You could have a date field and filter on date after.
Thanks for you comment.
Well done Nicos!
This is a very helpful trick and will save my a lot of time!
Awesome. I am glad you found this tutorial usefull.
Thank you so much for this amazing tutorial.
Thank you for your comment. I am glad this helped you.
Amazing guideline. Is there a way when the documents are saved on the pdf folder they save in the exact same order as the excel names?
If you put an index infront of the name so that when sorted they are in the right order then yes. Instead of the name Nick use A1_Nick then the next name A2_ and so on. This way if you sort the files by file name they will be in the same order.
Thank a lot for the detailed explanation and tutorial! Much appreciated! This is really helpful!
You are welcome. Thanks for your comment
Hi, why im getting a Run Time error 5941 and the requested member of the collection does not exist ?
Make sure the folders you are saving to are not in protected windows locations, use documents or desktop. Also do not use cloud synced folders, they cause issues. Next check and make sure that the folders and their paths are correctly addressed in the data source file.
THANK YOU SO MUCH!! This VB File helps me a lot! I have created donation receipts for an NGO, now I have to send them individually by e-mail to 400 addresses. Do you have a recommendation for an Outlook or Gmail add-in? I'm thinking about buying the Merge Toolkit from Mapi lab. Or could this even be done with VB and macros?
Inam glad this helped you. Thanks for your comment. It can be done with VB code but i personally use the Merge Toolkit as it offers alot of other features you will end up using.
Thank You! AND thanks for commenting your code so it was easy to modify just to export pdf's or word docs.
I am glad you found this usefull. You are welcome for the comments 😀
Thanks for your comment.
This is totally awesome I have been searching for the right video for this, and Yours is the BEST so far. It really helps me a lot in my reports as every month I have to generate a 5000 training certificate and have to rename them .... what a mess.
That is awesome. I am glad it helped. Thanks for the feedback
Thank you very much for your sharing. It is very very useful, save so much time.
I am glad it helped. Thanks for your comment
This is an incredible tool. Really appreciate you explaining the code as well to customize it as needed. Saved me hours of work, thanks a lot!
Thanks for the feedback. Really glad it helped you out
Teacher with Capital T! Excellent lesson
Thank you. I am glad you found this usefull. From the name intake it you are feom Greece, Crete?
So helpful, this video has saved me hours!
I am glad. thanks for your comment
This is great! work like a charm. Thank you for sharing!
Glad it was useful for you. Thanks for the comment
is this applicable to Office 365? Apps for Enterprise? the problem i encounter is that the original document close upon execution so only the first record is processed. It stops after processing the first record. How can i work around it?
Definately applicable. Dows it generate both the word and pdf of the first record? If yes check the file folder path and file names of the second record. To check, delete the first record so the second becomes the first. Run the macro, does it generate? If it does then you have probably made a change to the macro. Use the original macro.
@@paphitisn thank you for your reply and for your video. another subscriber here. Yes it always create the first record only. It generates both doc and pdf. I did not change anything. i just copy paste it, i also watch your video, checked them all lcharacter per character and renamed all my files to your files. My excel file however does have other columns, could it have affected the merge? how can i send a a screenshot of the error?
Awesome.... just what I was looking for
glad to hear it. Thanks for the comment
Hi, is there any way we can automatically send the individual files to respective recipients after generating them
The best method i have found to be able to send the personalized pdfs to individual recipients is with Merge Tools Adin
mergetoolsaddin.com/
it is free but you can donate.
i have also used Merge Toolkit which has an annual cost
www.mapilab.com/outlook/mail_merge/
Both will allow you to attach an individualized email to your recipients.
Great tutorial. I was wondering how I can copy the macro?
You can find a link to download the macro and the code in the description.
I receive an error that says "Run-time error '5852': Requested object is not available." Do you know how to troubleshoot this issue?
Check all folder names, field names in the data source and the folder paths in the data source
Thank you for this and the explanation of the macro! This is exactly what I was looking for!
That's great. Glad you found it usefull. Thanks for the feedback
Thank you very much! This is very useful, and I am able to add new field named OUTPUT and only export docx when I put “Y” under the field. However the generation is a bit slow and I am wondering is there any trick to improve the speed? I am export around 300 documents, around 8 pages long.
Well done for modifying it to improve work efficiency. As for the speed, it depends on your device, amount of RAM and the length of the document as the documents are kept open until the process finishes. You may find it gets slower as it progresses if you have a lot of documents. I would suggest add another field so that you can add a number like 1, 2, 3 etc. add the number 1 for the first 50 records, 2 for the next 50 and so on. This will allow you to filter on this field and only merge 50 documents at a time. This should improve the speed as you will only be merging 50 records at a time ana you will avoid the long delays when you are on the 100th or 20th record. Let me know if this works for you and improves the speed for you.
You are a life saver!!!!!!
I am glad this helped you. Sounds like you got it all working. Thanks for your comment
Nicos this is really useful, thanks very much. I am using this along with someone elses VBA code which then sends emails with the custom PDFs created by yours. I am wondering if yours would also be able to send the custom PDFs to custom addresses (stored in an extra column in your spreadsheet)? To have both processes in one vba would be amazing. I am not enough of a coder to splice the two bits of code together. Thanks!
Definately possible. I however use a Word addin for sending the personalised pdf docs as attachments. It allows me to send personalised emails, cc bcc and a few other options also. To recreate this from the start would be alot of hassle. If you want, send me the vba code you are using and i will check to see if it can e merged with the mail merge code.
This is not working for me. I keep getting an Error 5941... I read somewhere it is because i am on a shared network, I am doing this on my work laptop so i have moved all files to my system c drive and it still will not work........
I suggest using your desktop or documents folder as the C: may be prtected. If it does not work in these lications then i suggest you double check the paths of folders and names of columns in the data source.
Thx from Germany. Big ups. This code goes so hard. I am trying to make it more general so that basically anyone can use it without having to make folders and maybe a gui so you can type in how files can have individual names :) Anyways big help. I am super happy about this.
Thank you your feedback. Your project sounds great. Please let me know how you get on and please share your work with us if you get it working. It sounds awesome. I would be happy to make a tutorial on it (and of course reference you ) if you get it working. It will help a lot of people.
@@paphitisn Right now I just got it to save more data fields. I am having an issue with spaces and parenthesis.
... DataFields("Nam new").Value... If I replace the space with underscore it works but I also have
... DataFields("Nam (new)").Value...
Even if I replace the underscore it still throws a runtime error.
Also amazing documentation. I am used to look at completly wild and uncomented code so this is a blessing. I am not new to VBA but it is has been a while since I used it :)
Oh, figured it out. It just escapes it automatically. I just removed the parenthesis and it worked. lol :)
@@loiiblank4699 Great :)
Be blessed so helpful, Nice instructions
@@GideonMacks thank you for your comment. I am glad this was helpful.
Hi there! Thank you for your video. I'm running into an issue with the module and I've checked and rechecked the file names and paths on each of my documents and there are no issues. I even tried deleting the Word exportation section of the code to see if it would just run the PDFs, but unfortunately it ran with the same error. Any thoughts on how to fix this? Thanks!
Without knowing what issue you are running into i will assume you have tripplechecked evreything. If this is the case then the issue maybe the location where your folder is. Do not use a synced cloud folder like dropbox or onedrive. Also dont use c:\ as you may need elecated privileges to write here. Use a folder on the desktop. Make sure to add the new path in the data source. Let me know how it goes.
Our desktop is on the c:\ folder, so I tried to move it over to my personal MacBook (and adjusted the path names), and I'm getting the error around the "ExportAsFixedFormat" command. Any thoughts? @@paphitisn
Great Stuff, especially the VB code. My imported code stopped - and I worked out I had duplicate entries in the FileName field. In my large file, I ran the conditional format duplicate feature in the MS Excel and found the problems. Thanks for the work and clues.
Awesome. Nice work with the use of conditional format to find the duplicates.
I cannot get this to work for me. I'm using a letter head type word template document. Could that be what is messing up the "heading 1" feature.
Can't say for sure but why don't you try with a plain document to see if it works and then you can with your document.
Thank you Nicos, This is such a useful Tutorial. Thank you also for the downloads. I am getting an error message run time error 5852 requested object is not available. Something to do with marking the last page - do I have to edit the document on the last page with a zero?
Makesure you are not saving the files in a synced cloud folder like onedrive or in a protected location. Use documents or desktop location. Make sure you have acurately entered the folder locations in the spreadsheet. Make sure the headings of the columns in the spreadsheet are exactly correct unless you have changed the code. Let me know how it goes. Your error is related to it not finding the folder locations so double check everything.
Hey, Thanks you so much for the helpful video! Can you please help me with one issue. I have one page word document, but when i run the code the document got one blank page and i do not want this blank page. I have tried to find solution but nothing have worked.
Please help!
decrease your margin at the bottom or increase the left and right margin. this will shorten your document and make it one page
Thank you!@@paphitisn
Is the a Word setting i need to change before i put and run the macro? Because once i "Run" the macro, it still pops up the "Save as" dialog box and i will have to save it manually.
No, the only thing you need is to enable the Developer ribbon so you can access Macros. Which you have done as you can see the Macros section in the developer ribbon. Have you imparted the .BAS file. If not do that instead of creating a new macro and pasting the code.
Hi Nicos, I'm trying to separate some documents but the macro isn't working. Please can I have some help/advice?
The macro should be working fine. Check filder paths, check the names of the columns so that the are the same as in the video. Make sure you thebdestination folders are not in a cloud synced drive ot protected folder. Use a folder on the desktop or in documents. Follow the instructions in the video exactly and it will work. Thousands have used the macro and it works for them.
hi this video was very helpful. but am getting a runtime error 5941 for the below code:
SingleMergeDoc.SaveAs2 _
FileName:=MasterDoc.MailMerge.DataSource.DataFields("DocFolder").Value & Application.PathSeparator & _
MasterDoc.MailMerge.DataSource.DataFields("FileName").Value & ".docx", _
FileFormat:=wdFormatXMLDocument
Heck the folder names and the path you have for those folders in the data source. Also make sure the folder are not in syned cloud folders or protected folders like C:\ or windows folders. Use Desktop or documents. Let us know how it goes.
Hi,
Thank you for the video. It is exactly what I have been looking for.
Unfortunately I am receiving an error when it is running the macro.
It creates the first word doc but fails to create the first pdf document.
I receive a run-time error message '5941': The requested member of the collection does not exist.
I have followed all of the steps and rewatched three times, but i'm baffled as to what I am missing.
Do you have any suggestions what could be going wrong?
Many thanks, Matt
Yes, check the field names you have specified in the macro and make sure they are exactly the same (small and capital letters) in the spreadsheet. Check that the file paths specified in the word and doc column are correct. Make sure to let me know if this has helped and you have resolved your issue. If the first word document is saving then it is a likely one of the 2 suggestions above. You could always remove the save to PDF commands and see if it exports all the word documents to make sure everything in the macro works.
Brilliant , love this video. Hats off !!!
Thanks, glad you liked it. Lersonally i used it today to generate around 100 student certificates. Even merged their photo in the document.
I have a question, I'm using this to create certificates but when the automation starts the first page is the word document template but the second one is an empty page, is there a way to remove said blank page?
This means that your .erged fields are pushing the text down to the point where the insertion point is going to the next page. Make your top and document margins a bit smaller so that when you .erge the feilds there is mor page length and hopefully the insertion point will not move to the next page. Also make sure you do not have any blank lines at the end of your master document. Let me know how this goes for you.
@@paphitisn Perfect, that fixed it, thank you very much! :D
Thank you for the Video.
I tried to write the code, but something is getting stuck. How can I get the import file from you?
Thanks Marinus
You can download all the files i used including the code and the .bas VB module file from the link i provided in the description of the video (at the end of the description). It is easier to use the .bas file as this will import the macro for you automatically as explained in the video.
THANK YOU. This is exactly what I was looking for, and your teaching is PHENOMENAL.
However, I followed your instructions but I get "Run Time Error 5". It only saves 1 word doc and cannot proceed any further. can you please help? I keep your excel column names exactly and have not changed anything. I give permission for the macro to access the folder where files are being saved and everything. Any ideas what can be wrong?
If it is saving a file and stopping it means that you must have an error in your folder names or location. Is it generating both the work and pdf for the 1 file? if no then look at the pdf info. If yes check your data source as you may have an error in the file path for the second one. Be careful of leading or trailing spaces in your data source for the paths.
Thank YOU! This was amazing and so helpful!
Thanks for your comment. I am glad this helped
Hi Nicos - how do I modify the export block (SingleMergeDoc.ExportAsFixedFormat) to export it as html (is this possible)? I tried a couple of things but they didn't work. Thanks!
Hi. I have never looked in to that to be honest. I will have a look at the save command for html when i have some time.
This is perfect!! Thank you very much for sharing your knowledge!
You are welcome. I am glad you found it useful
This is extremely useful!!! THANK YOU!!!
I am glad to hear it. Thanks for your comment.
Overall very helpful but I keep getting a Compile Error and when I look up how to fix it, there does not seem to be a problem, any thoughts?
if you look through the comments, everyone that faced issues the problem was with the names of the paths, columns. Also make sure you are not saving the merged documents in a cloud synced folder or protected folder. Use the desktop or documents.
@@paphitisn Thanks - it turned out to be that when I copied and pasted it - it added some additional words between lines and I just had to go in and delete them. Thanks for coming back to this though, your help is much appreciated!
How could I tweak the macro so it only generates the documents i have checked in the Mail Merge Recipients. I.e. lets say i have those same 12 lines but i only check record number 1 and 3 (so those are the ones id like to generate). I'd like to update certain records only.
Chances are you are filtering the data instead of Edit Recipients. Go to Mailings ribbon in word, click Edit Recipients and then apply your filter or simply select the records you want to merge. When you run the macro inly those records will e merged. Here is a link to a tutorial on Mail Merge that explains everything you need to know on mail merge.
ua-cam.com/video/taeWuL9VH18/v-deo.html
Thankyou so much for this, do you have a video that explains how I can now send the PDFs to the respective candidates/recipients? i feel like this should be easy but I cant get my head around it.
there is no native method from Word. However you can purchase the Mail Merge Toolkit which is a word add-in and will allow you to send personalized attachments via mail merge, which is what you want to do.
Thank you so much Sir for this excellent presentation, it has really benefited me a lot.
my dude, this is awesome. thanks!
Glad you found it useful. Thanks for your comment
Thank you so much for this! Your instructions were super clear and easy to follow, and it worked brilliantly. Much appreciated!
That's great. Glad i helped and that it worked out for you.
Is there any free macro or add on to email these files as attachment using outlook ?
Merge tools addin is free for the first 20 uses. After that you can pqy 30 dollars for a life time license
Is there any way to download the VB Code without having a dropbox account. It does not permit me to download from my official laptop and I need this for my team.
Sure, here is a download link. Let me know if it works for you:
thejuniorschool-my.sharepoint.com/:u:/g/personal/nick_theseniorschool_com/ETex9B3wX4tCoAAL633oa2YBCZU9EcOBH2ue0me2lDjoOg?e=PzvYeF
Went through all steps and at this end an error Run-time error 5852 shows up. Any suggestions here?
make sure the folder you are saving the documents in is not a cloud synced folder like dropbox or onedrive. Also make sure the folder is not in a protected location like directly in C:\
Use a folder on the desktop to make sure.
Wow! Astounding. Thanks a ton for making this video for me in advance.
I am glad you found this useful. Thanks for your comment.
@@paphitisnIn my case, on a daily basis i use your method and keep on adding the data in the same file order wise, so how to comment off the previous data so that only new loaded data should merg.
@shivagunti add another column in your data. It could be a date or a simple incrementing number. When you add the next data add the next numbe so if the previous number was 15 then all the new records will have the number 16. Before you merge filter the data on the records which equal 16 in that column. Then merge. Or use a date field and filter on date added. This will allow you to also track when records were added
This one is very helpful to understand how to merge files from excel to word. However, I have done the procedure and INSERT MODULE1.bas and still got error as "runtime error 5941 word ". Can you help me?
Please read previous posts where others had issues as they were all solved making sure the paths on your local drive exist and are correctly named in your spread sheet and make sure the file names are the same in the macro.
@@paphitisn Thank you. Noted on this.
HI! THANK you for sharing this. Your video was very helpful for me who is not a techy person. I have successfully download MS WORD files. Unfortunately, not working with PDF.
Check the column names in your Excwl file. They must be exactly the same as they are written in the code. Also make sure the folders are named correctly. Check these and it will work.