Mail Merge to Separate PDFs with Custom File Names and Folder Locations. No Plugin Needed!

today we're going to convert your mail merge into separate pdf documents now if you started down this rabbit hole you'll know that mail merge produces one massive document and whether you convert to pdf first or later you've somehow got to split this up and the processes for doing this at least those on google are either really complicated or require a third-party plug-in so we're going to avoid either of those today but that's not the only problem if you somehow managed to get through the process and create separate pdf documents you'll find that they're called something completely useless like document 1 document 2 document 752 and you're going to spend the next two or three hours renaming all these files before you can send them to the recipient or file them away it's a colossal waste of time and that's the second thing we're going to avoid today so how we're going to do this we're going to give you a word macro that you can copy and paste into your word system to automate the process of saving out your mail merge into individual documents and exporting as pds better than that the macro is set up to read a folder path and a file name from the mail merge data itself to specify the name of the file and where you want to save this i cannot stress how powerful this is when you set up your word mail merge data you'll typically do it as a table in excel and that means you've got all the excel formulas available to you to define the name and the save to location so if you want to have a name or a date or a reference number in there you can if you want to have some kind of group identifier you can the choice is entirely yours and more importantly you're not limited just the file name you can do it with the folder path as well so if you've got say one folder for every recipient you can save the pdf directly into that folder you don't have to later on filing it saving you again a colossal amount of time so how we're going to do this well the first thing we need to do is to get the macro into your word system we provide the macro on our website in plain text format so you need to just copy it from there and paste it into word but don't worry we will go through all of this in this video so you can see every single step second step we'll set up the data in excel and the word mail merge in word and then run it so you can see how it works and then finally as a bonus we will go through every single line of the code line by line showing you exactly what it does in word how it automates word and with that in mind we've written the code not in a purist fashion but rather to be as understandable as readable as possible to the layman and finally to answer the last question you probably have what's the point in creating a pdf if i can't then send it to people we've got another video coming out where we'll use the word mail merge email feature and a macro to send emails with attachments and actually in fact custom subject matters and a cc box as well neither of which are available in the standard version of mail merge to email but that's another video we'll deal with that later so now let's get to it so here we are in windows explorer where we have a folder containing our data as an excel spreadsheet and our letter as a word document now for anyone using some kind of cloud or sharing service such as sharepoint or dropbox you'll need to make sure that your service is synced with your computer so that you have a local folder which appears the process of word saving a pdf does not allow direct saving to for example sharepoint so for us here we have the demo sharepoint site and then we've just got the folder called end of year inside that so this will be saving effectively directly to sharepoint but doing so virasync folder so without the way let's actually get on with the job so first things first let's open up word and we will add the macro in now if you're joining us here the macro is on our website in the article linked to this video so if you're watching the video on the website it's below and if you are watching this on say youtube please look in the description below where you'll find a link to the article on the website where the text of the macro is so let's open up word and here is our letter so first things first as i promised we'll do the macro so the way you can add a macro to your system is using this developer tab up here now if you don't have this appearing that's perfectly normal for word they hide it by default and the way you make it appear is you just click in the ribbon anywhere and you go to customize ribbon here and you click the checkbox next to developer in this right hand column once done press ok and the developer tab will appear and this brings you the visual basic and macro button on the left hand side here so to bring up visual basic where we're going to add our macro in we click the visual basic button and this brings up the visual basic editor if your visual basic editor doesn't look exactly like this don't worry all we need is this project panel to appear and if yours doesn't you can find it by clicking view here and project explorer or pressing control r here so once done you should have this panel appearing if not on the left and at least somewhere on your screen the project explorer looks a lot like windows explorer you've got folders and they contain files and the files contain text which define your macros and all we're going to do is create a new little text file and paste our macro into it and that makes it available now you can see here we've got two top level entities here we've got one called normal and one called project brackets letter now this normal one is so-called normal template and this is a default template in word and it's used to store all your defaults you can define styles in it but you can also define macros in it and normally is opened every time you open word so that's really the best place to save any macro that you want to be able to access repeatedly project letter refers to the letter template which we've just opened and while we could save our macro in there it will only therefore be available to that one letter and once you've closed it it won't be available anywhere else so i would recommend that we insert it into normal and the way we insert it is we simply right click go to insert and module and having done that we find here that this modules folder will be created if it hasn't been created already and we've got module 1 appearing and all we need to do is to go to the website select the text of the macro copy it and paste it in and i'm just going to right click i've already copied it and paste and there we go so the text is now in there i will go through the text in detail later on but we need to know is this sub and this n sub that defines our macro that defines the macro name and this green text down the side tells you what each line is doing we'll run through this in detail in the video later on but right now that's what we've got and having copied and pasted our macro in we can close this down so our system is ready to go with that macro in fact the way we run the macro later on is we click macros and there it is it's ready to go but we're not ready to run it yet because we haven't set up our data so quickly let's just look at our templates this is a very simple letter template i'm sure you'll want to do something far more detailed for your own work but for us all we've got is we've got a full name field here we've got a known as field here for the salutation and finally we're going to give them an end of year complement because we're nice like that and so everybody's got an individualized compliment which goes in this one i've already set this up as a mail merge so the recipients are already selected and the fields have already been added so this is done and we are ready to look at the data so let's now open up the excel spreadsheet the first three columns on the left are as you might expect we've got one called full name one called known as and one called complement and these correspond to the three fields that you saw in the mail merge in addition to this we have created four additional columns doc folder path doc file name pdf folder path and pdf file name now the names of these columns are important and need to be exactly as you see here the spaces have been removed and the first letter of each word has been capitalized this enables the macro to know where to save and with what name each of the doc and pdf files so what we need to do is to put in the relevant data so first of all doc folder path this is where we want to save all of the word documents now the process requires you to save a word document before converting to pdf so because we have to save it we might as well save it somewhere useful and even if you just delete it all afterwards it doesn't really matter and the way we go about finding the doc folder path is we look at windows explorer and we simply need to find the folder we want to save the documents into like here i've got this ready-made one called docs and we click in the address bar here make sure it's all selected and we copy the text once done we can simply click on the cell and paste the data and there it is that is the folder path already set up for the documents now in the beginning of the video i did promise that we could have much more complicated folder paths and indeed you could do this but we're not going to do that here however if you wanted to you could always use a formula and for example have a subfolder of docs whatever you do please be advised that you do need the folders to already exist in the system before you run this macro the macro won't create the folders for you so we'll keep it simple we're going to save all the word documents into docs so i'm just going to double click on the corner here and that fills it all the way down into each of the five rows so the doc file name we need to make sure each of them are unique because they're all going into the same folder and to do this i am going to simply use the full name and some kind of description of the content so i'm going to make a formula open equals a quote and we'll just call this end of year letter to and then we'll add in using the ampsand to combine text the name so there it is we have a formula and i'm just going to double click here and paste it down and so here we have each file will be called end of year letter two and it's each of our names now you'll notice i haven't included the extension the docx extension that will be automatically added by the macro if you do inadvertently add it you'll just find that your file is called docx docx it will still work so next up the pdf folder path coming back to windows explorer we've already created a pdfs folder as well again you could go much more complex but we'll keep ours simple and save all the pdfs into one folder so i've copied the text and i'll paste it in there and once again double click to fill down and there we go we've set up our pdf folder path so finally the pdf file name and we could we could make it exactly the same as the word document file name that is not a problem so i've copied the formula there and pasted it down so this is the same as the doc file name so that is our data setup we know now from this data that we'll save all the word documents into the folder called docs we will call them end of year letter plus the name and the pds will go into a folder called pdfs and each of the letters will have it their own name so this is all set up we are ready to go okay so here we are back in windows explorer i'm going to reopen the letter and we will put this onto the left hand side so that we can see the letter and we'll zoom out quickly so we can see the whole letter so here's our letter and on the right hand side what we're going to open up is the two folders this is the docs folder and this is the pdf folder so we can see the files as they're created within these two folders so to run it we click in the developer tab here press macros and here's our macro ready to go so press run and so as this runs you can see here that in each of the folders the word documents are created followed by the pdfs and so there we go the process has been completed and we have five docx letters and five pdfs so let's just check one of them we'll check bethany's open this up and here we are bethany klein dear beth and beth is indeed superb so our process has worked we've got named pdf they have the contents of the mail merge we have succeeded so that's it please go off have a play use the code and finally it's time for the running through of the vba macro in its entirety so here we are back in vba where we're going to run through the macro and see how it performs the mail merge so on the right hand side we have all this green text these are called comments and they're defined by this single quote and everything after the single quote is green and green text is ignored by the computer so we can write whatever we want there and typically you use these to give a description in real world terms of what the line does now i'm going to tell you what these lines do which will roughly correspond to this green text over here so the first line here sub mail merged pdf this defines a subroutine which is the same as a macro and gives a name mail merged pdf that's all this line does the brackets you can ignore the first line of our subroutine here dim master dock record number and single dock what this does this sets up what are called variables and the best thing to do is think of the variables as post-it notes so what we've done is we've collected three post-it notes together we've put them on our desk and we've scribbled onto them a name master doc record number and single document and that's really all we need to think about at this time so the next line here is we set master doc remember this is just a post-it note to the active document and if we bring back word when we click go on our macro we have a document that's front and center this is the active document and this being our mail merge document is the one we want to identify so we identify this as master document and now we know which one this is every single time we want to refer to it we don't have to worry about other documents getting in the way or anything getting confused here so once that's done what we're going to do is we're going to run what's called a for loop and that starts with a 4 here and runs through to this next line here and what this does is this runs the code in the middle from here to here a fixed number of times each time it runs it this value here record number another post-it note will have a different value so conceptually you might say this is a bit like putting a number one you want a bit of paper handing it to an assistant saying going off and getting that file or doing something with it they come back you put a different one on and you send them off again and you do it each and every time so this loop will run from record number one through to this thing here which don't worry about it just yet but this defines the total number of records and to give you a feeling of how this would work in the mail merge if i preview the results here so we can actually see the data this is a bit like going to one and then we'll do something and then we'll click onto two do something click onto three and in fact we'll show you all these steps as we do them so let's go back to our code this bit as i said previously this is the total number of records within your mail merge and here we're just going to have a quick glimpse of how a macro can identify different things as it runs so we've already identified that the master document is the well active document the document with the mail merge template in it and that contains the mail merge data as well so what this does this is like an address so we start with the top level thing the master document we say i want to drill down into that and give me the mail merge that's associated with that document inside that mail merge we drill down again we identify the data source that is the excel spreadsheet we provided it and we go into that and we say give me the record count i.e the number of rows in that excel spreadsheet so this will return in our case the number five we only had five records set up for it but in your case it could be any number of records so all this does is this defines total number of records in our mail merge and puts an end condition for this for loop so it only runs up to that total number again coming back to word this is a little bit like clicking this skip to the end last record button and there it is the last record's five so that would be the record count five for the next steps i'm going to close down the project explorer over here and i'm going to push this to the left hand side so we can see the mailbridge data on the right and we have here our code on the left and the reason i'm doing this is to show you exactly how this works with the mail merge so the first line master doc dot mail merge dot data source dot active record equals record number now remember record number is set to 1 for the first iteration loop then two then three then four then five and it ends and this is the same as going to mailings and going under preview results to here and just simply entering the correct record number so in our case we start with one so i put in a one press enter and there we go we've gone now to the first record adam griffith so that's what this does in the background this is really important because later on every time we refer to the data of the mail merge to find out the save locations and the file names we want to make sure that we're pulling it from the right row so this says we are looking at row one or two or three depending on what record number is so next up masterdoc.malmo.destination equals wd sentinel document this is the same as clicking finish and merge and going here to edit individual documents so we click on that and we get this up so that's what this line does this is done in the background so you wouldn't actually ever see this this window here appear but in the background that's effectively what it's doing so next up we set the master the my stock mail merge data source first record to record number and the last record is the record number and that's the same as going here from and two and putting a one in both boxes because we start at one finally master dot dot mail motion to execute false and that's the same as clicking ok so literally all that code has done is exactly what you saw me do there and it has done this mail merge for the first record and here it is only one letter it's one page long this is quite simply the first document that's been extracted and has been created as a new document so the thing we now need to do is to slap a post-it note onto that new document so we know what it is so we set the single doc to this and it's while it says active document and yes active document was used up here as well this is now the active document being the front document and this one the letter document is no longer the active document it will still have the master doc post-it note on it but here at the front active document is this one and this now has the single doc post-it note on it so having identified this new active document which is the result of our mail merge just one record remember we now need to save it and we need to export it as pdf and this first set of four lines here this is in fact one line of code but it's been split over four lines this saves document as a word document and that's the same as clicking here going to save as choosing a folder and naming your file and saving it next up we have another four lines here and this is the same as going up to file and going to save as adobe pdf again choosing a folder giving it a file name and saving it so finally having done all that we can close the document now i haven't saved this one but if you had you would uh not get that window to ask you to save it because it already saved and that's what this line does here does it closes the document and now we're ready to do the next result so this line here next record number this tells us we're going to increment record number by one and then we're going to go up here to this four and repeat the process again so that would be equivalent of going here putting in a two going here add individual documents set that to two set that to two open it up save it close it so that's the process that we've automated and once it's done it for all of our records in our data sets we get to end sub and that has finished the macro so before we go let's have a quick look at the detail of the save as step and the export as pdf step so while these are over four lines they are in fact one single line this little underscore here says go on to a new line but don't start a new code line so this is one single command and at the top we tell it we're saving and then it's just got two options here which have the the name of the option here file name and then file format followed by colon and an equals and to the file name is not just the file name but is actually the file path in its entirety so that's the folder path followed by slash followed by the file name and the way we create the file name is we take this thing which is the folder path and this is identified by going to the master dot mail merge data source then looking at a item called data fields for the one that's named doc folderpath and returning its value so this will return the value of the doc folder path for the row that is the active record so the row we're exporting this will return the doc folder path value we then easy amazon is just like in excel it adds two bits of strings together so we add in the backslash and then we do the same for the uh doc file name and so this is retrieved and finally we add on the docx at the end so this builds up one single string which is the folder path and file name combined and this is provided as the file name so we know where to save the document finally we have this file format here which is simply saying save as a docx document that's the new version rather than the 97 document of the last century uh so the exporters fix file format is very very similar uh exporter fix file format is a generic name for the pdf export and we have an output file name rather than just simply file name but it is assembled in pretty much the same way we have the pdf folder path backslash and then the the name of the file followed by pdf and finally we tell it we want to export as pdf as opposed to another format so these lines although they spread over four lines and look very complicated they're relatively simple and are built up of these monstrosities here which as long as they are are simply return the field from the current row which has the name given in these quotes here and in the brackets so that's it that covers the entire macro all i've said here is presented to you in this green text on the right so please feel free to read that but that is it we have done our mail merge to pdf the results are here we are ready to go and send these out via email so thanks for watching i hope you found this useful as mentioned before we've got another video where we use word mail merge and a macro to create a better email system where we can actually send attachments to people so please do check that out on top of that please obviously do check out our website we've got a lot of tips and tricks and everything on there which will help you systematize your work and reclaim your life so thanks for watching see you next time

Channel: Imnoss

Views: 243,350

Keywords: Separate PDF, PDF, Word, VBA, Macro, Individual, File Name, Mail Merge, Microsoft, Email, Custom, Folder Path, Mail Merge PDF, Office, 365, Automatically Name PDF, Tutorial, How To, No Plugin, Without Plugin

Id: sFX0UfwAUmM

Length: 23min 1sec (1381 seconds)

Published: Tue Jan 19 2021