How to Generate PDF from Mail Merge | Mail Merge Save Separate Files | Mail Merge to PDF and Doc

hello welcome back to the another video of email automation using Excel macros so we have already seen one macro where we have generated the PDFs from the Excel data using the template in Excel okay so if you can see here and then we had this invoice format in Excel and then we have generated multiple PDF for each record in our last video you can find the link if you want to go through that video but in this video what we are going to do that we are going to generate PDF output and the MS Word documents output for each record in the Excel and then we are going to save a different document for each record all right so video is going to be very interesting please hit the like button if you like the content of this video subscribe to the channel for more automation videos using Excel macros RPO automation power automate and python as the first thing we have to set a mail merge template if you already know how to set up the mail merge template you can skip the video seven minutes from here and then you can directly jump on the VBA part of it okay so once we have set the mail merge template in the word document after that we have to write few lines of code to generate these PDFs and rocks file into a separate folder so here on your screen you can see the data that we are going to use and first of all we will follow the simple mail merge steps that usually most of you must have done or if you are doing first time you can understand here once the template and the data source is connected and we are able to generate we are able to update the data in the mail merge template then only this macro or the code that we are going to see for generating the PDF will work so this data and this is my template sample template I have taken here you can take any template and in this template I would also like to insert one table so you can keep any header and then let's copy the format keep this formatting and I will border all the cells like this all borders and then you can just copy it from here use in your template hold on this corner go like this if you want you can put the logo also here now we have to do the field mapping from the data source so if you have already done mail merge then it will be very easy and you can just Fast Track the video from here I will use the wizard you will be able to see same options on your screen so now first part is that we have to select our recipients or you can say like we have to select our data source for the mail merge to generate PDF so you can if you have you could not select then you can select from here also and then you go to your folder where you have kept the data source so here is the data source I will open it it will ask me to choose this sheet because there might be multiple sheets you can select that sheet where you have kept your data to generate PDFs from mailbox click ok and then you can see which all columns are selected and how many rows are there click on OK simple step now once you have this you can edit the recipients list if you want otherwise you can go to next and you can write your letters write your letter now you have to start field mapping so you can click on more items first of all for field mapping and all you can use the address field also if you are using some inbuilt templates but we want to use everything from our data file only for the easy reference so what we can do is just next to Dear what should come customer first name okay I will insert this field here close and in the address you can again go to more items you can select the address insert and if you want to select all and then you want to map later address City insert and we have the ZIP code also insert we have to use the order number also that we can do insert and we have to use the delivery order date as well that also we can do insert now we'll I'll just close it for click changes and then I will copy the City from here I will paste City here mapping will come here only I will take zip from here all right so field mapping is completed you can just check if you will mapping right now so we have to change the state delivery date all right hope you can see the template on your screen now the next step is so these are the fields which are with ugly items from the data source now we will go and in the table we have this also now we will go and we will review so you can see the first customer details are here and you can see the details here also okay so if you want you can just save this file from here and you can save as PDF also if you want to save so you can select the folder whatever you want and then you can save as PDF or you can save as the docs it is the PDF option but we have to do it automatically for multiple records so let's so this was just the option that I was telling you now complete the mail merge if you are satisfied with this format you can complete the mail merge or if you want to do more formatting in your document you can do that also I will go and I will look complete the mailbox you can have a testing from here the details are changing okay we have only four rows so now this is done now let's see how to use the code to generate generate everything automatically so now to write the macro code for mail merge 200 PDF what we have to do is we have to write a Visual Basic code so to write user basic code if you don't have developers Tab In Your Word file you can go from more go to options in the options you will have customizable from there you can select this developer option all right now whenever we click on Visual Basic we will have this empty document and one of the normal tab will be there if you are not getting this left hand side panel you can use project Explorer from here all right after that what you have to do you have to click on normal you have to insert a module I already I already have inside so I have already inserted the macro module I will start writing my code here and now first of all we have to create a folder so I want to create a folder into the same path so I have given the complete path here you can see my path and then I am adding the today's date as a format so if you can see here I have taken this this part where the date so when I will run this you will be able to see that it is this code is creating the folder inside this directory all right so the folder is created for 27th now let's maximize this right now next thing is that we have to initialize a Word document we have to create a Word document that we are going to use and few variables that are required like we required first line we require current line and we required last line so where is the code I have taken the active document I have taken all of those lines which I was just telling you so we are reading the data till lasts and then we are setting the and then we are getting the count of the last and now next is that we have to loop on each row in the data source all right so here is the code for looping on each report and while we are looping I will close this side panel so that you can see entire comment on your screen so anything that is in green on your screen and starting with the upper talking comma that is the comment that is not the code and as you can see in the comments we are reading first record as the active record and we are reading last record at the as the active record so that when we are generating the output one record only one record is coming in one document so each document should have only one result so that is why we are taking the first record as the active record and we are taking the last report also as the active record I hope this is clear to you now we are taking this very we are reading the value from the order number active field so how to take that value you don't have to refer to the source you can refer to your merge fields from here you have to take that value not from the data source because the name should be same as here because we are referring the document all right so now we have the order number what we have to do is we have to see that this Loop should run so now when we have changed the values in the format or in the form that we are generating through macro like this we have to save this in both the formats so from this line here the first line is to save the top type of the file and the second line is to save the PDF if you don't want to save the dog file you can disable this line now here we have closed the documents now we have to see that if this was the last record in the data source or not if this was the last report we will end the loop and if it was not then we will set the next record as the currency called all right so from here you can see we are checking the if this was the last record or not and here also we have to add a condition as 0 and now this is the time to test this code if it is working fine or not I will just run this I will keep this open and I am running this folder and I will run the code now so you can see on the left hand side all the PDFs are generated now let's check the data inside this PDF if data is correct or not so nine double one I will open the data source here is the data so let's check this nine double one so you can see the customer name in line double form is customer one zip code is 111 city is any City one and this is the example data so you can utilize your own data you can write any kind of template this is just the data replacement you can see the daily date and item name if you think this video was useful for you please hit the like button And subscribe to the channel for more videos for quick Automation and here is the code on your screen

Channel: Python2020

Views: 9,525

Keywords: Mail merge, automate mail merge, mail merge to separate file, how to use mail merge, mail merge to pdf, generate bulk pdf from mail merge, generate pdf from excel, how to generate separate pdf from mail merge, mail merge single file for each row, mail merge to single page pdf, get pdf from mail merge, generate bulk pdf using mail merge, mail merge to get pdf, how to get pdf from mail merger, mail merge save files with column values, mail merge give file name from cell

Id: FAxN6-sZdBg

Length: 14min 22sec (862 seconds)

Published: Thu Sep 29 2022