Be able to create your own dynamic cover letters automate email process track unlimited types of jobs we're going to be able to show jobs load jobs here track all the jobs track correspondence between those jobs what kind of correspondence and also add of course new jobs and delete jobs and of course take word documents customize them add pictures into them and even regardless of the template so you're going to be able to add load different types of cover letter templates it's going to be an incredible training so i cannot wait we're going to get started right away on.
This so let's go ahead and go into the details of that i'm going to show you how we're going to do every step this particular training is going to include lots of different uh features and focus so i hope you'll stick with us on the entire training so how we going to do this well we've got a lot to show you here i've got an info and settings screen here you're going to track different information about job status job information here we've got a job database here correspondence and we're going to be able to save email templates we've got template emails so a lot to go on this i hope you.
Enjoy these training i make these for you each and every week in fact every tuesday so if you do like these trainings don't forget to subscribe and click that notification icon bell that's going to ensure that these trainings are free each and every weekend you can also download this workbook absolutely free using the links in the description below either with your email or facebook messenger and we're going to get that sent on over right away to you however if you do like these templates and you want to help us out a great way to do that would be to download the 200 workbooks it's just 77 and that's 200 of my best templates you're free to do whatever you want with them once you get them downloaded so it's a great purchase and it's just less than 40 cents per template so i hope.
You'll pick that up all right let's get started on this training because i've got a lot to show you like i had mentioned job applications can be a nightmare managing documents managing applications cover letters customizing them sending them following up with the jobs it's a big deal and so we want to make that entire process easier this applicant is going to do that but not only that and more importantly so i'm going to show you how you can make your own applications not only that i'm going to show you how you can automatically work with excel and word together this is an embedded word document and then we'll be able to automatically just create and customize it based.
On the template so we're going to use different variables so that all we need to do is just click once and it's going to be edited just like that automatically so that dynamic information is going to come in we've got company information of information about the job right what is the name of the job what is the company name and all the information concerning the job we've also got your own personal information and that's going to come here so you've got your name your last name address what's the information about you and also we have a folder where do we want to store.
Library microsoft word has some libraries online you can download these cover letters so we've got that going on so we got a little personal info and settings so it's going to grab all that information you'll want to select what folder you want your job so all that job information has to go in one folder now what folder is that you'll be able to set that folder just by clicking here and you'll be able to set that jobs folder right here and just click ok and i'll show you that's just a basic browse you'll also want to set a profile picture as you saw in this we have a specific profile picture now the template didn't have any picture for example the template itself.
Just had a standard stock photo so when i delete that that's going to show you what that is in a moment that's going to delete this file so that when we reload the template the template's going to either load so we have the stock picture we have full name job title contact information however as soon as we create a covered letter based on this job information all of that notice the recipient the company it's all standard static text there variables however when we create that cover letter that's all going to be replaced with the individual company information your name your address your information all of that is going to come directly from the settings here that.
Information this personal information here and it's going to go right into that word template and that word template you can easily create an email from it you can add templates and stuff so if you have a cover letter here let's load in our cover letter we have a cover letter here and we want to attach things right so we're going to add an attachment so what do we want to do we've got some cover we've got cover letter templates we can add we can add specific jobs right they're all saved in here so we can add that we may want to add additional features maybe we want to add that cover letter here so we can do that so we can have multiple attachments when we send that email it's going to create a brand new email and it's going to show up and display that email and i'll bring that down for you see right here we've got cover letter and fetters and we've got a dynamic.
Email content based on the specific job details so dynamic email content dynamic word documents we've got lots of going things going on here so how are we going to get all this done well very easily i'm going to walk you step by step through the code so nothing to worry about sit back relax have your drink of choice and we're going to get started right away okay i also want to have different jobs right so we can hide jobs or show jobs right so we may want to look up specific jobs we may also want to search for specific jobs if for example if i want to search in front right and then based on a job title i can do that and it's going to search just that specific job here or i can search by a company maybe i want to to look at the freder's pharmacy and i want to know.
Only that job for that company we can do that right here or we can clear that out and show all the jobs when we select on a job it's going to load those job details and it's going to also load if you've created a cover letter it's going to load that cover letter so for example this particular one has no cover letter associated with that so you see here when we show the documents there's no cover letter but we could also simply just select the cover letter and that cover letter is going to display here it's just going to be a template but it'll display right there so there it is there's that cover letter it's going to display here so all we need to do is just create that cover letter here or any template it's going to show up again just like we did so we can use multiple cover letters these are word documents these word documents are embedded in excel the.
From a specific template if you notice right here different templates these four templates are right here cover letter one through two so they're just word documents if we open one up it's no different
Than any other word document as you can see right here but however through the beauty of excel vba we can automate the process of converting it to a custom template and then we can create an actual letter from that so a lot of really really really cool things so i hope you'll stick with us on this i'll try to go slow and cover everything as possible okay so how are we going to get that.Done well the first thing we want to do is we want to be able to save jobs right create a new job right if we want to clear it out or we want to save a job or maybe we want to delete a job or we want to load a job if i select on the job here it'll load back up so that's how we're going to do it including any saved cover letters that we've created or saved for a specific job all right so how are we going to do this well let's start inside the code we've got a few different codes i'm going to be able to browse for a folder i want to be able to browse for a file picture that's about the only code that's going to be running on the personal info and settings so let's take a look inside the code and it's going to come from our developer and visual basic you can.
Also click alt f11 to quickly get you in there and now if we take a look at it we've got some info and setting macros here i've got a module called info and settings that has to do with just the three different macros that we're going to be associating now there's three different macros on that and they're relatively simple right so i've got two here that are going to cover the browse and i've got one here for adding a cover level so as i select on here that browse so if i want to add one it's going to browse for a specific cover letter here just like that so how are we going to.
Get that done well the best way to go about it is going to be based on either the button or the selection change so the first one is going to be browse for the jobs folder now when we browse for a jobs folder we want that full path of that job folder to go directly inside here inside e3 and so to do that we'll have to place use the file directory so we're going to dimension the job folder as a file dialog then we're going to set that job folder to the application file dialog and we're going to browse for a folder picker right we're looking for a specific folder not a file then what we're going to do is we're going to work with that job folder i'm going to set a title called browse for jobs folder that is the title that appears on that pop-up we don't all want the.
User to select more than one one is sufficient so allow multi-select is going to be equal to false then if show does not equal negative one then go to no selection and basically that means if they've pressed the cancel or they have not selected anything or closed the window out without selecting something it's going to skip this go to no selection however if they have info screen range e3 remember info is the name of that screen that's the long code name of that e3 is where we're going to put the select item that selected item is the long file path now we're going to do.
Something very similar with the picture now notice that the picture itself is going to go into the one below i want the full file path of the picture the profile picture that's where the user is going to put their profile picture so if we browse for that and i'm looking for a specific picture on that person i would find it here let's say sally we're looking for a jpeg but this one i want to look for jpg png or gif pictures so i'm going to select sally and click ok it's going to place that entire path located right here on e4 and so how do we do that well to do.
That all we need to do in this case again profile picture file dialog but this one we're going to set that profile picture equal to the file dialog but this time it's going to be a file picker right we're picking a specific file this one was a folder this one it's a file this one i also want to set some filters on it right filters meaning i don't want every single type of file i want to only want specific types so we're going to give it a title please select the member default picture.
We'll just put that please select let's change that your profile picture your profile picture and then what do i want those filters to be i only want the user you could probably add more like bmp and a bunch of jpeg and things like that but i just put three jpeg png or gif so these are the three types of pictures that we're going to allow again multi-select we only want them to select one picture if they do anything other than select a picture go to no selections this time we're going.
To put that entire file path which is going to be the selected items i'm going to put that inside e4 in the next step we want to browse for the cover letters now browsing for the color cover letters that is the macro that's been tied to this button here this icon here as it moves down that is the macro that's been tied to this button so how are we going to do that well the first thing is we want this to appear on the selected row selected row means selection change when the user makes a selection change it is then we want to appear there so that's going to be a selection change event event that's going to appear directly on the sheet so if.
We take a look at the sheet here inside our info here settings we have a selection change event
Simply by selecting worksheet and then selection change so it's event if the target account meaning the user selects more than one cell we're going to exit the sub out if they select one cell then what we want to do shapes browser letter button now this particular shape the name of the shape browser letter button that is the same shape that we set to that so when we select on that specific.Shape we see that the name of the ship is called browser letter button okay so that's just going to name that we've given to that group it's actually two shapes so it's a group of both of those shapes okay if for some reason it's visible then we're going to hide it and what does that do that means anything that that they select on the sheet automatically it's going to hide it i just want that hidden unless they select anything from o through p right i want to select anything from o through p then i want it to display so if the user makes the selection between 07 and p19 then i want to do the following with the shapes this browser button i want to place the left position in column.
Q i want to place the top position in column q on the top and of course the target row that row that they've set and then i want to make it visible so i want to make sure that it's visible making it visible that's all i have to do to make sure that's visible once it's visible the user can then set a specific file for that using the macro that's been assigned to that button which is this one right here first thing we need to do is to find the selected row as long and i want the cover letter as a file dialog cover letter this time again we're picking a file picker right we want.
A file and then what we want to do is we want to set the selected row equal to the active cell row that means whatever row they've selected we're putting that into a variable with retro 7 8 9 10 or whatever we're going to put that into a variable a long variable once we have that what we can do is we can open that file dialog with the cover letter we're going to give it called browse for word templates and in this one again we're going to set a filter because we want them to select only specific word templates doc or dlcx you could add additional templates in here no problem there's a bunch of them but i just added the basic ones here so what we're going to do again allowing them to select only one just if they're if they make no selection skip that but.
This time what do i want to do i want to take two parts of that i only i want both the file name here and i want the entire path located in p so that's going to do inside o we're going to put the file name so how do we extract the file name well we can use the directory command to do that so info dot range o in the selected row select row equals the directory of the selected name this is the entire path when you add directory around it it just extracts the file name from it.
So all we're placing is the file name in column o however in column p we want the entire file path so to get that we just use the selected items that's going to put that entire path all right so that's all we need to do now we have a little bit of a named ranges we've got some name range that we've set that are going to help us work with that so let's take a look at some of the named ranges that we have on here so inside the formulas and name manager we'll bring this up here and i've got a few of them i've got cover letter name and i'm going to shrink this down it's a little bit larger than we need for our purposes cover letter name well that's based on the names of the cover letters so if we move it up here and i tab into it we see that the dancing ants around the names of.
The cover letter because we're using an offset formula that are going to only wrap around the specific data right so we're using offset we're counting all the ones using offset so that we only contain the names and not the blanks i'm going to do the same thing for the cover letter path so we have two named range one cover letter name and one color cover letter path so that's it we also have another one here called job status so if we tab out here we see that we've got another one called job status this is all the different types of job status that you can have job status is going to come in handy because it is that status that we're going to put right here inside the job status i've got this zoomed out it's a little bit let's bring it up to 100 so you guys can see that.
Okay so job status is here now we have the job status here and we've got all got information on here so job status is going to be a named range using the data validation here and it's going to be based on that job status right here okay great so we've got that now also what i'd like to do is we understand that and let's take a look at some of the job database now this job info has to be saved somewhere right as we add more information for example if i want to put in the contact.
Name of let's say fred again fred's very popular guy freders right i want that saved so as soon as i save that update and let's say i'm going to load something else and load it back in i want to make sure that that name returns and that's going to be saved right here inside here so we've got a contact name right here so all that information job id job title is going to be saved here in case if there was a cover letter that got created i want to save that right here so i want to make sure that that gets saved as well so we've got that cover letter that's the one that we created it's also going to be saved so all we have to do is make sure that we say that but the best way to.
Do that to save it easily and save us some code is to map the data if you look in this top row here we've got a bunch of cells cell b9 j15 g15 j5 those are the same cells where the same data stayed here inside g7 or j7 here so all that information gets mapped when we're going to use that very simply all right so notice we also have one other thing we've got some information here i want to know if there's a cover letter that's been saved notice this is b9 that is the cover.
Letter i'm going to put that here as we created this color letter it had to get saved somewhere right it got saved in this location here and i want that file path to appear in b9 so if we look in our jobs database we see that cover letter is mapped to b9 so when we load this job that entire file path is going to come into b9 and that's how we know so when we load that job if we if there's a value here it's going to load the cover letter just like we do here so if there's a value in b9 it's going to load that cover letter it's going to load it here as an object that word object.
Inside excel i'll be showing you how to do that in just a moment but let's take a look at some of the other information here we've got a job id notice each individual has a job id we have the row that's associated with the job we're going to use match we've done that before we're using the match formula and we're using a named range called job id so when we go into the name manager and we see job id okay this is a dynamic named range based on those ids so what i want to do is i want to extract the row meaning i want to know that job id 1 is on row 4 and we can use a match for that so we're going to do just that using the match we're matching whatever's in b3 job id and we want to.
Add 3 because this in itself here this will return 1 because it's the first value but i don't want the first value i want the first row the row that it's held in so we're going to add 3. if it's not found it's just going to show blank because that would create an error i also want to know the selected job row as i select specific jobs here and here i want this row to change and notice we're going to use conditional formatting based on that so when i select that and i go into the home and i go into conditional formatting and manage rules we will see that there's a set of rules based on what is in b6 if the row is said to be six i want to call this this.
Dark brown with white bold font and it's going to apply to all the cells from d4 to d200 i've also got some alternating colors for alternating rows and those are two conditions based on that d4 notice there's no dollar sign before four it's going to be all the rows that apply and i want to color odd rows odd rows are going to give it this light brown right whereas even rows here even rows which would be a zero also making sure that there's a value in column d it's going to give it a little bit lighter and that's very easy so that as we add data in here.
Automatically the conditional formatting will just set automatically okay all right so we've also got a clear filter here and a filter here so there's a lot of cool things going on here okay i want to know the cover letter path what does that mean now let's take a look at this remember we created two named ranges for our cover letters one we created for the path and the other we created the name when user selects a cover letter this is a data validation based on our cover letter names right data validation based on the names of our cover letters that we created in the info section i want to know the path of where this is located right i want to know that full path i want to.
Know that cover letter 3 is located here so i'm going to extract that path basically what i want to do is simply look this name up and extract the associated path with it and i want to place that path directly inside b8 and we can do that using an index and match so we're going to index here that cover letter path because it is that path that we want to extract and also what we want to do is we want to know the row that's associated on that we're going to use the match whatever is in m2 that is our cover letter name and we're going to use the cover letter name named range to locate it and we want an exact match and we want that specific column that cover letter path column.
Once we have that it's going to pull that cover letter name if there's an error it's going to be blank so we know that if this is not blank that is the path of that cover letter and that's really important because when i decide i want to load a specific cover letter let's say there's a cover letter that has not been associated i need to know inside the code what is the full path of that cover letter so if i pull that up it's going to pull that cover letter up and it's going to place it directly inside here that cover that is going to be placed here so regardless but we need to know that full path so now we know to where to get that full path and that's going to come directly.
From here b8 and we notice there's no cover letter associated with this particular job here so that's no problem and then also i want to know the email template road now email templates can be saved if i click here create the email it's going to load in email templates now i've got let's say i want to load in a cover email template i've just got one save cover letter i need to know what row that's been saved on let's take a look inside the email templates we got wrap text here if we.
Want to show wrap text but i really don't in this case i'm going to turn that off and basically what i want to do is i've got email templates here email names different email names i've got the subject and the message so i want to know what role this email name is associated on so again i've got a named range so we go into formulas and we see that we have email names email names again using the offset formula based on those so again like i want to know i want to know the row that's been associated with if i know that there's a row associated with this cover i can then load in the subject and i can then load in the message in the appropriate cells and that's what i want to do.
When i change it when i make a change here i want to load in anything and then i want to save it so let's say i want to put in test subject here and test message here i want to save that if i save it i want to make sure that gets saved to the database here so if we look in the email templates here we now have test subject and test message so if i load it back in let's go ahead and load one with nothing which has which is the third template right here which doesn't have any data and then i load in the second one which i just saved i want to make sure that that comes back in so that's all going to happen on a change event right when i make a change to r3 that's what i want to happen but i only want to trigger that when we have a specific row that's set up here so we're going to use mash to do that based on whatever's in r3 which is that email template.
Email name and we're gonna add two because we want the row associated that first row is row three so this is our second template which is on row four continuing down we have some email documents email and document variables now these are going to be very useful because when we create a template we need to replace those variables with the actual data so we see when we're showing that document here we want to replace recipient name with the with the recipient name now we just open the word so notice this open word so we can double click on it and open the word if we want to or the company we want to replace with the actual company right and the first name the first name of the person who's filling out the form and the sure name or last name either way.
So we want to make sure that we're going to do that with that so we need those variables so what we're going to do is we're going to loop through these variables so i want the first name in other words wherever the first name is found it could be in the email or in the document i want to replace it with sami where the last name is found i want to replace it with smith and that's just how we did inside the template so for example i'll do it again here if i load this template in cover letter 2 which is the one i've been using right and notice we have a default picture here so we can load up that and once it's loaded up you'll see all we need to do is create cover letter all of that data is going to be replaced with the actual data so i just click this button it's going to automatically save that job and replace the app now we don't have any data in here inside our company data but our personal data got off in other words there's no.
Company address there's nothing in here so if i change that company name would work just fine so this company doesn't have that information okay so what we want to do is i'll select this one because it's got all the data associated what i want to do is i want to make sure that we have our personal information and then we have our company information now the personal information is simply linked to everything everything inside our info and settings so it's simply linked to all the data here so when this change it's automatically going to change here so notice it's all based on the info and settings okay so everything i just right justified it so it wouldn't bleed over into here but we could easily add spaces so for example if you want to left justify it let's let's judge that.
We don't we don't want the bleeding into the other cells all we need to do is just add space here and then so dragging this space down and it'll it will stop that so that's another way to do that okay so in other words we also have recipient information right the company or job information i want to know the recipient name so this is going to be linked to whatever's in g7 this is going to be linked to whatever is located here in g5 right so we're just simply linking all the data and the reason we want to do this in a single column is because when we need to create that word.
Document or when we need to create that email and we need to replace all those variables all we need to do is just run a loop from 13 through 30. we're going to look for the first name if it's found we're going to replace it with sally it's very very easy to replace tons of variables with just a tiny bit of code right in here so that's why we put everything here in one column because all we need to do is look for state anywhere in the document and replace it with california also likewise we also want to make sure that the format is set too so we're going to also update formats and check for the format to make sure if there's any date formats that or number formats that they get honored as well so i'll show you how to do that coming up okay so we want to we.
Understand everything in this panel here now let's go ahead and go back to this selection i'll show you how we're going to do let's go with the crete email how do we load this is very simple so if we do this how do we load this information it's going to be based on a worksheet change event based on r3 and that's on that specific sheet so let's take a look inside our job macro here it's actually our job info here and it's going to be based on that change event right so we want to we will have a document template and we have an email template load let's look at that email template load first making a change to r3 we also want to make sure that b10 doesn't equal empty why is that so important well b10 is going to let us know the row of that template if b10 is empty we don't know.
What row to associate we don't know what row to load that subject or what row to load that message so b 10 is essential that it contains a value so assuming that b10 does have a value we could then load it's very simple inside the email subject which is p7 we're going to load whatever's in the email template sheet column b and whatever's in b10 i could easily have put this into a variable called email row but that was just one less step just easy so this is our email row so basically.
We're going to take whatever's in b10 whatever's in b in that email row we're going to place it directly in p7 and just to clarify what that means is all we're going to be doing inside our email templates whatever's in b is going to go directly inside our this one right here p 7 p 7 let's move that over here p 7 and also whatever's located in column c of that is going to go located in p9 so that's just all we're going to do to place that email data relatively simple okay and also what we're going to do is next one let's do this one what i like to do is i'd like to load this template remember we're loading the template now this template can be loaded into two conditions.
Right when a user makes a change to this one right here i want to check for two things one is has there been a template if there's already a cover letter saved i want to load the cover letter whatever's been saved i want to load it however if there's a job in which you have not we have not created a cover letter for it's important to know so we want to load the template so we're either going to load either the template which looks like this or we're going to load the actual cover letter if one was created so we're going to show you how to do that just two things on this sheet that i'm going to show you two small things on the sheet one is simply create email or show.