And we're also going to be able to email those templates while they're either in a Word or PDF so simply clicking on a create Now button will go ahead and create an email create a word document and put that in a PDF format along with it customized so that your PDF is automatically customized with the actual data in here so it's going to be a fantastic training today and I wanted to get started and let's go over some of the reasons why we may do this now word does have a great mail merge feature.
Itself but we are limited in word to some of the things that we can do with that and we can also track items in Excel so if we go ahead and create those templates we can go ahead and see what was sent and when it was sent when we do it Excel and we can avoid duplicating that information we can also set some parameters in Excel like let's say in this particular sample we have.
Our last appointment date and then we have the days since we've sent since that last appointment now what if I want to send out this specific template for letters from 1 to 30 days and what if I want to send out this specific template for days of 31 to 90 well when we have that kind of option we create this functionality in Excel we can do that simply by clicking on the.
Create now when we have the email set is going to open up our word document and then create PDFs based on that and it's going to create multiple emails each with their own individual PDF customized to the data and so we see in this sample for different I believe it's four different or PDFs along with their emails corresponding each one unique in its own way so let's go ahead and go over how we did that and how you can use.
This for any type of email sent for any type of data and you can customize this exactly as you would want to do that so we'll go ahead and go with that now we can also do this in Word format too so for example if we change this to word and I'm gonna go ahead and delete this because it won't send out which is what we want it won't send out again so we'll delete it so that it knows yes we can send one more let's go ahead and click create now and you'll see that a word document will open up in the background and then this time it's we've.
Actually created a word document if we open that up we can open that up and take a look and see what was created here you see in the word document all of the variables get changed out just as we have done let's go ahead and inspect the actual template and so you can see where we start so what we've done is we have a list of data in here and what I want to do is I want to map that data I want to be able to use this data inside our word.
Template just as we do with mail merge but this is far more powerful because we can use multiple templates we can use multiple formats and we can use multiple send variations whether it's email or print so this is far more powerful and easier and we have a history tracking as well so what I've done here is I've used a mapped named range so this is last name and this is first name address so what I've done is I've added DEET these symbols before so.
That we can easily recognize it and you as well can do the same in your word template you can also map your own so let's go ahead and take a look at that we'll open up one of the templates that I've created here into word and you'll see here we have first name last name now be careful these are not although it looks the same these are not mail merge labels okay this is simply a user typing in you know less than less than first okay name just like that and so that's.
All you need to do to to adding your data label so it's quite simple so to do that simply replace whatever your data wants to be with the names here and so in these that we have days okay because we want to know how many days it's been since our last appointment and we want another last appointment day so I have multiple data labels just as we have in our excel file here where we have last appointment dates and days so it's super simple to customize this just the way.
You had it all you have to do is add your own data add your own data label and then make sure that exact same label is used in your Word document so that you can make sure that it's mapped out properly it's very simple so all we've did for the days here it's just a simple formula I've taken today's date and subtracted the date of the last appointment day and that gives us our whole number let's go ahead and make sure that's general it'll often think of.
It it's a date and when you see that just make sure it's general sometimes Excel automates and when we don't want
It to automate and once the template descent I use VBA to automatically place the template that was sent and the date and the time that it was sent so that's really powerful here and we do that through VBA we have the option to select from multiple templates now I've also created a sheet where we have templates and if I were to remove this template okay adding a template is simple we can do that from.BBA simply by clicking this button where it brings up our file dialog and then we can choose whatever template we want to add and just click OK alright and that's going to add our template so whatever templates you add here will be available in here ok so how did we do that under templates we've created a named range and I've created a variable named range I could create a named range that covers this entire you know table column but what I don't want to do is I don't.
Want the extra spaces if there's only three I want to show three if there's four right if there's one more I'll want to create just that fourth I don't want to see any blank spaces so to do that what we've done is we've created a of dynamic named range using offset using offset and I'll show you how that works into the formulas tab click on named manager and I just have one here and it's called document templates when we go and inspect that what this is is offset I've used to offset formula and.
Basically how the offset formula works is first what it does is it counts all of the rows with text to count a means count to text and we're counting the text so we need to know number counting the text from a six to e 25 so it counts right now there's three so this number is three so this gives us our starting point our starting point is six okay we're not going to offset the number of.
Columns okay we're not going to offset any other variables here but we do need the number of rows here okay and this is just one column so the offset allows us to set that and that's what helps us that's what keeps our field values here only those field values okay so that's how we do that and that has that's really really helpful so that as this table grows so does our document list.
Here okay and this was used we just used the data validation when you click on data validation you'll see that this contains document templates it's all it is and it'll bring you right back to where it's located so that's how we've done that again email and word is also a data validation just using two two variables here when you click on data validation you'll see that's both word and PDF as well you've cried could what I've done is I've created I want to know the number of days in this particular I when we when it VBA runs through the list I only.
Wanted to send based on a couple of conditions I want it to be 31 or over and I want it to be 90 or under so those are the conditions that we set so that that's why when you see that the code ran it ran through and it just chose just these and didn't send for any of these because these are all under 90 so those are some parameters that I've set and what did I wanted to do is I wanted to create an email okay I also have the option to print and this this will print to your default printer alright let's go.
Ahead and you've covered the basics here so let's go ahead and see how we did that through VBA know one more thing actually I wanted to show you was that in VBA I need to know what template this is okay I need to what I need to know is I need to get this this value here this tells us our template name we know our template name because we've selected it here but when Excel VBA has to pull up the actual template we need that entire file name which is here that long file.
Name we need to know exactly where to find that and so this helps us so what I need to know is I need to know what row okay if we have selected 90 right customer reminder 90 I need to know for sure that it's on seven okay I need to know it's on seven how do we get seven we need to know seven we already know F right we know it's in column F but we need to know what so how do we get that row well we know if we use the match column if we use the.
Match we know it we're gonna find this is gonna be a third right so example a match call match would be equals match
What are we looking for we're looking for this and what are we looking at we're looking in document templates right and we need an exact match okay so it's gonna tell us - right it's the second item but I don't want to write I want seven right well why why do we need seven we need seven for the row now since this starts on row six remember the first one is row six so all we would.Need to do is add 5 to that right and that it's going to give us 7 so that's exactly what I have done right here okay that's the only thing the template rows 7 and then I've added the if air which is always good because when vba season air it's gonna pull up a bug so we always want to say if an error just leave it blank so in that case all I've done is use the match column and then I've added five it's a very simple B 3 is where our template row is located that's all we need to know we can hide that for right now it's not so important I just wanted you to see B 3 because you're gonna see that in the code let's.
Go ahead and go into the code under the developers tab you'll see Visual Basic here you can also press alt F 11 if you don't see the visual tab available you can locate that under the options and then go ahead and click customize ribbon and you can see make sure the developers tab is selected here so let's go ahead and go in that and we see we have just two macros it's very simple there are no on sheet macros here they're blank there's nothing going on on the sheets themselves let's go ahead and expand.
That a little bit so we only have two and the first of which is simply a browse remember we browsed for the folder right we need to know what template file is I'll go ahead and show that to you again this macro right here add template it's gonna basically help you browse for a particular file and put it right here that's all that macro does and so basically we've gone over the Browse file dot file dialogue before so we're going to simply.
Open it up and then put that in the first row available first row being sheet 299 and then plus one this tells us the last row with data so we no need to add make sure we add one which is going to be the first available row so this code will say hey put that file name right remember we've selected the item but if we just use select item is gonna give us that long file name when we add dir directory short for directory.
It's gonna tell us just the file name so when we use directory it's gonna give us just this file name just the file name that's all we want there so that's dir and then the file name and then when we want the long file name the entire thing we can use this one selected items this will give us our long file name right so in e goes just the file name and F goes the long pathway including the file name so those.
Are the only two things and those are going to go in the first row so that's it for that matter it's very simple we've have gone over it before in other videos but I did want you to make sure we went over and I didn't want to skip it our next macro and actually the only one that's the only macro that this needs is just one single macro that does everything for us and I'll go ahead and go over that now with you we're only working on sheet1 primarily a little bit in sheet 2 so for for our purposes we're.
Gonna use wish sheet 1 and that includes all this code until the end with here so everything everything here is with sheet 1 the first thing we want to do is we want to make sure that we do have a correct template our templates are located in remember that template Road remember we said b3 if it's empty then we know there's a problem they have it selected the right error okay so if that match formula causes an error we use that if error right and then blank right so so if there's NER it's blank so that's gonna say if b3 is empty that.
Means there's an error so we can say please select the correct template from the drop-down list so that's why and what I've done is selected g3 so that so for example this is what would happen if we select a wrong I don't think it's gonna let us I'd won't even let us but let's say okay let's say we've got a space and we try to run that code it's gonna say please select a correct template from the drop-down so that helps us avoid any errors for fer for missing templates so that's why we have that if they're right.
In the code so that's this little piece right here next up now that we know we've got a correct template and we can go ahead and get that template row and we need to know the template role and that's in b3 we just went over that the template name is located in g3 we need to get that we want to know the template name why do we need the template name because when our macro is finished I want to take this template name and I want to put it right here in this column so I want to make sure we have that template name next up.
We have from days now from days is is that filter that we're gonna put it on there so we know if we're going to be sending out or printing one of these letters and our firm-- days is located right here in l3 and our two days is located in n3 so we've go ahead and defined those two from days and two days next up I want to find the location that is the long filename remember this is in sheet 2 this is our templates if we know.
That afternoon we went is the file name F is the long pathway that includes the file name and we know the row it's on right we know the rows in B 3 so F and b3 is gonna get us our long document file name our long is in sheet to call em F and the row and that's our long file name so we need that because we've got to open it up so now we're ready to open up now we want to open up that word document and up above we've used late binding and late binding means simply.
That we've defined certain items as objects ok we're going to be using two different we're gonna be using Outlook for email and we're gonna be using word for document so we've outlined the word document itself that's the you'll document and the application forward and just it's very similar with Outlook we've outlined I'd mentioned the outlook application and the outlook mail itself as both its objects and the word content has a range so we're gonna use this a little bit later on in the range and then we also have so basically what.
We need to do is we need to say if word application itself is open we don't need to open the application so this is gonna this little code is going to help us with that it says on-air resume next that means if a word because if we try to open word as an application that's already open it could cause an error so we just want to say if it's already open just skip this okay we don't need this and then it's going to say if the error number is zero because if the error number does not equal zero right then we need to clear the air and then we can.
Also launch a new instance of word right here and this will help us add the words Edward set word app create word application and all this does is launches word itself it doesn't launch the document itself we have a nope we haven't opened the document all we've done is open word as an application so that allows us that that allows us to instances open the document a if it's not been open next up we want to get the last row the last row of our table which.
In this case is 22 but your table will probably grow so we want to make sure that the last row is dynamic so that it grows as your table grows or shrinks as your table strings so we're going to use E and the last row in this case 22 so we will go ahead and define the last row as E and then we'll use a high number here X up row this will get us our last row we're going to start it row 8 we're.
Gonna create a loop a for next loop right here we're gonna actually create - but we're gonna create one loop that goes from row to row to row to row and that's this loop for customer oh and it stops right here next customer okay so that is the loop that we're going to create and in that loop we're just gonna basically go from row to row to row from the first to the last so that's how we do that next up what I want to do is I want to know the day's I want to know the days for that specific.
Row and our days are located in column M and the customer row so I want to get those days since that is this value right here I want to get this value because that's important because I need to know if the days here are equal or between these two then there's actually three conditions three conditions if we're going to send it one condition is if the days are equal to or over this that's one the second condition is are the days equal to or under this and the.
Third condition is has there been is the same template been sent already I don't want to send a template to a customer if we've already sent that specific template maybe it's a new template but I don't want to send the same template i if this template and this template match or in this case if this template and this template match I don't want to send it so those three conditions have to be met before we actually send an email to a customer so let's go ahead and see how we map those three customers now we've.
Defined the day since so here's those three conditions right here here's one if the template name does not equal and in the customer oh that's one condition remember we can't have matching template names that's one and here's number two and the days since must be greater than or equal to the front days this is condition number two and condition number three is and the days since must be less than equal to.
Two days okay remember we've defined two days and from days up here okay so we know those values so those three conditions have to admit if those three conditions aren't met then skip all of this right until we see the end if right here okay until this end if I don't go ahead and mark it customer let's see three condition conditions met so that you know where that end is so that's how we do that if they are met I.
Want to do the following things if those three conditions are true then we then the next thing we do is we want to open the word document the reason we haven't opened it up until now is because there might be different as we go through as we go through the list right we may open different word documents this one we're only going to be sending out customer reminders this particular one in this case so we want to make sure that we open it up and we save it because each one's going to be different each one is.
Going to be different so we want to open up the template each time I want to customize that template and then I want to save as something else and then I want to close it so it's important because every customer is going to have different data so that's very important so the next step is we open up that word document and all we do here is simply set the word document to equal the word application documents open an open what filename file name in the document.
Location remember that's that long file name right here in F that's that long file name that's the entire pathway plus so it's not going to be read-only because we do need to make changes to read on these false so this opens up the template the next is now I want to go through and I want to start getting all those values now I'm going to run another loop and this is going to be four columns so now that I've got a specific row now that we're going to send it out I want to go through each of these columns one two I want to go from.
Here to here okay I want to go through all these and what are the column numbers okay you can do equals column okay that's column five so I want to go from columns five all the way to thirteen so I'm going to go five to thirteen that's how we get the column numbers so let's go ahead and do that so that's why I've mapped out those steps in VBA from five actually fourteen probably go I think it could be 13 we don't need the fourteenth 13 we do 13 because our goes.
Nothing to do in the 14th call we just need to 30 which is the day's the rest we'll put in information yet so first of all we need the tag name here and the tag name here is in this case it's always going to be in row 7 and it's always going to be the customer column our tag name what is that what have we assigned to that it's here tag name in this case its first name last name so basically what I want to do is I want to get this I want to look in our word template I want to find it and I.
Want to replace the first name with anything so pulling up our word templates go ahead and pull that up so we can look at it I want to find anywhere in this document it says first name and I want to replace that with the actual first name in the table so in this case it'll be twice so two times I'll replace it with the actual first name I want to do that so and then the next it'll go to the next column excuse me it'll start at last name then first name then address so as it goes.
Through each one of these column in our loop it's going to look for this it's going to get this it's going to replace the contents of the letter with the actual data and it's going to go through so let's go ahead and go through VBA so first we get the tag name we know it's we know it's in row 7 it's always going to be in row 7 and now we know that the value what is the value the values actually in the customer row and the customer column so for example in in the first row our value is going to be James.
Right for last name our value is going to be John for the first name so it's going to get that value and then it's going to so now we have the name and now we have the values now we just have to perform a simple replace so we can do that with and with and and end with with the word document look for the content ok what are we going to look for we're looking for the tag name what are we going to replace that text with we're gonna replace it with The Vow it's very just basically a find and.