Create Your Own Job Application Manager With 1 Click Cover Letter Generator In Excel Free Download!

hello this is randy with excel for freelancers 
and welcome to the job application manager in   this training i'm going to show you how 
you can create your own dynamic content   cover letter along with automated email to 
help out the entire job application process   it's going to be an incredible training track 
unlimited jobs i cannot wait let's get started all right thanks so much for joining me i got 
a really fantastic training for you the entire   process of applying for jobs can be a nightmare 
we're going to simplify that today we're going to   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   these jobs those particular jobs are going to be 
in a separate folder and that's the beauty of it   so for example in here we have a jobs folder 
and each one of these we're going to be able   to track different jobs based on a company in the 
job so every time you have a new job it'll create   a brand new subfolder in here so you can track 
all the attachments all the cover letters inside   a single folder i'm going to show you how to do 
all of that so make sure you stick with us on the   entire training also we have some information we 
can keep experience and education here that's more   of a helpful hint and we also have cover letters 
you can add different cover letters in here you   can build them up so however many cover letters 
you want i just downloaded these from the word   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   documents and all we're doing here is we're hiding 
and showing columns let's take a look inside this   we have columns l through n l through n here 
okay so all we're going to be doing is simply   showing those columns l through n when we click 
show document now when we click create email   we're going to hide columns l through n and we're 
going to show columns o through r and we're going   to also show this button set here this button set 
is called email group so we're going to show all   these buttons and that's very very simple inside 
the code so let's take a look how we did that   and that's going to be called on job info screen 
macros so the first thing we've got to do is we've   also got high jobs and show jobs one more thing 
just that was a very very quick macro here you'll   notice real quick we have the ability to hide 
the jobs or show the jobs so this macro simply   is going to hide or show column d and it's going 
to hide or show this button the button called show   jobs button i want to show that or i want to hide 
it right so we're just going to do that and that's   all that is so it's a relatively simple macro 
and that's we're going to use the same macro   hide show jobs for both that means it's the same 
macro that's either hiding or showing those jobs   so all we need to do is know what condition are we 
in if column dd if the column is currently hidden   is true that means it's hidden we know that we 
need to show the jobs we need to show them right   it's currently hidden so we must show them to do 
that we want to do it right here we know if it's   currently hidden like it is now then we need 
to show it if it's currently displayed then we   need to hide it so we're just going to use the 
column visible to know what current what the   current status is so do that we just check if 
we want to show the jobs then we're going to do   show jobs button we're going to visible equals 
false right we don't need to show that jobs button   but we do need to show the hide jobs 
button so that's going to be made visible   and then i want to make sure to show those columns 
column d i want to make sure that that hidden   equals false meaning we're going to show that 
column else we're going to hide the job simply   the opposite this case we're making the button 
show jobs visible we're hiding the high button   and then we're going to actually make sure that 
column d is is then hidden that's all we need to   do for the show heights okay what about the show 
email relatively simple right this time if we're   going to be showing the email we need to show 
a few things the first thing is with job info   that's our sheet we're going to show that email 
group we want to make sure that that is displayed   i also want to hide the create cover letter button 
right that create cover letter button should only   be displayed this button right here let's go ahead 
and show the documents this button here i want to   make sure that this is hidden this is called the 
create cover letter button i want to make sure   that that button is hidden when we're adding 
those emails that button is going to be hidden   these buttons are going to be displayed so how we 
going to do that well it's just simply hiding and   showing those shapes and the columns as well so 
the columns would be l through n hidden equals   true we're going to hide those columns also i want 
to hidden also want to show columns o through r   those columns must be displayed to show the email 
and lastly p3 i'm going to take on g9 i want to   set the default to email i think that's kind of 
a nice feature if we're going to be adding let's   pull a job that has an email it didn't add any 
job data probably should have but that's okay   so this particular job has an email notice that 
we've got fred fretters at frederick's drugs so   when i create a new email create an email i want 
to make sure that whatever email is located in g9   is also moved over here to p9 right so if i double 
click on here it's going to create a link for that   so i want to make sure that it's sent there so 
we just do that we're going to set that default   email we're going to use this line of code right 
here p3 is going to equal set the default email   to once we have that i also want in case there's 
any document preview that is that what is that doc   preview shape that is the name of the shape 
that i've created for this so when we show docs   this particular word document is its shape as well 
and it's called document preview it's an embedded   object and it's called document preview it's given 
the same name so when i create the email certainly   i don't want to show that i want to hide that so 
we make sure if it exists if it doesn't exist or   it could create an error so we want to wrap that 
in on air resume next and that's just what we did   here we wrapped it in on air resume next and 
on air go to zero but we're going to hide that   we're going to hide that document now the show 
docs is going to be just basically the opposite   right we want to this case hide the email group 
but we're going to show the cover letter button   we are going to show columns l through n and we're 
going to hide columns o through r and again this   time what we're going to do is we're going to 
show the document preview again we're going to   wrap it in on our resume next on or go to zero in 
case it doesn't exist but we want to make sure to   display that that's all we need to do okay great 
so we saw another let's continue down with these   macros i've got two more macros small macros on 
this particular module and that's going to allow   us to filter jobs right so if i again as i showed 
you before if i want to put in staff right i want   to make sure that the job title only the staff 
assistant job is going to be showing up here right   so how are we going to do that i should probably 
clear out the selected row too so i'll get to that   i want to clear out that selected rows based on 
b6 when we run a filter we should clear out that   selected row okay so how are we going to do that 
well that's going to be based on a change of g   or j in either one of those cases i want to 
make a change we're going to use an advanced   filter right i want to filter based on job title i 
want to filter based on search company now notice   here in our jobs database we've got our data i 
want to search based on job title or company so   we need to set up some criteria for that and i've 
done just that here so i've sent us some criteria   based on the job title so but i want to wrap it in 
wildcard meaning i want a wildcard asterisk before   and after that that way it just simply contains it 
right so if i don't get the entire name just right   i still want to show up for example if i only put 
in staff as a staff system i still want this job   to show up so when i click on staff here when 
i enter staff here i want to make sure that we   search any job that contains the word staff to do 
that we're going to put wild cards on the criteria   both before and after it just like this so it's 
going to appear just like this so this criteria   is simply linked to what is in g3 but before we do 
that we're going to add an asterisk before and an   asterisk after we're going to do the same thing 
with company but this one's going to be based on   j3 right this one's g3 and this one's j3 we're 
going to put an asterisk before and after this   becomes our criteria then our results come here so 
how are we going to do that well the best way to   do that is just simply create an advanced filter 
run our criteria which is automatic having those   results here we don't necessarily need job id just 
thought it might be helpful determining the last   row and then all we need to do is just bring in 
that information and bring it directly inside here   so how we're going to do that well that's just a 
little bit of code and then what we're going to do   is we're going to i want another macro called 
clear clear is simply going to make sure that   this is clear and we're going to make sure this 
is clear and rerun the macro so we're going to go   in opposite order first we're going to run the 
filter then we're going to do the clear filters   clear filters here jobs filters here this is the 
macro that's going to run when we make a change so   if we take a look inside our job info sheet here 
we see that we're going to make a change search by   job our company if we're making a change worksheet 
change event to either g3 or j3 and we want to   make sure that the target value does not equal 
empty if that we're going to run the macro called   jobs filter it is this mac or jobs filter that 
we're going to run so the first thing we want to   do is determine the last row and then we also want 
to determine the last row last results row both   of them in long variables and i also want to make 
sure that any jobs are automatically cleared from   d4 through d99 i want to clear all of those jobs 
out so that we're ready for the next filter so   d4 through all the way down is going to be cleared 
out any job simply just deleting it's going to do   that and also i mentioned to you i want to clear 
out the selected row right look here inside b6 so   we should do that right now because i want to make 
sure that when we clear it out b6 should also be   clear so simply adding b6 onto that that means 
that selected row is automatically going to be   cleared okay so great so now we're going to focus 
on the job database now we want to determine the   last row of the job database right we're going 
to be filtering so we need to know the last row   if the last row is less than 3 that means we have 
no data and we need to exit out of the sub then   we're ready to run our advanced filter this is 
going to be a very basic advanced filter we don't   need to encompass all the data because we're just 
filtering out the basics so all we need to do is   just probably go from a all the way to company and 
down right so i'm just going to filter out this   the rest of it is not important because we only 
really need to extract the job title that's   it so i'm going to run advanced filter from a 
through a3 through c in the last row we're going   to have our criteria come here from q2 all the way 
through r3 and then our results are going to be in   a2 through a b2 a2 through a b2 so that's just 
we have inside that advanced filter right here   so it's going to be a3 through c in the last row 
advanced filter is going to be 2 through r 3.   and we're going to run our results going to come 
from a a through a b 2 unique equals true going   to get those results we're going to check on 
the last row of the results based on column a a   if the last one is less than three then we're 
going to exit the sub then all we need to do is   simply bring over the results from d4 through d in 
the last row plus one we're adding one because our   row our rows start here on row four here but in 
our results they start in row three so right we   need to compensate for that one last row so that's 
going to bring over all of the jobs that's it   now to clear the filter out the remember that 
button here is a tied to clear filter i've got   two little icons here inside our job info screen 
right here it's kind of hard it's a little trash   can and a filter probably not the best icon but it 
works so this is going to be our clear filter when   i do that all i want to do is make sure this is 
clear and this is clear if i know these two fields   are clear and then i run the same exact macro 
we know that it's going to automatically change   this to double asterisk and double aster so 
that means any value here is going to show up   so to do that all we need to do is just do 
that and then run the macro so all we need to   do is just clear those two fields then run the job 
filter again relatively simple okay very very good   so that's really cool i want to show you another 
macro i've got a few macros here this one's going   to be called show document preview as you've seen 
a few times that i did inside the job info i want   to be able to show either the actual cover letter 
here or i want to show the template right we can   zoom out we don't need it that big and so what i 
want to do is i want to show this right so when i   show the documents here i create the email i want 
to show that template just got moved up all right   there we go just reset that we're showing all 
the columns there we don't need to show all the   columns and all the shapes when we want when we 
make a change to m3 we want something to happen   what do i want to happen i either want to load the 
actual cover letter that got created or if it's on   another job where there's no cover letter in that 
case i want to load the template itself right so   the template itself so how are we going to do that 
well that's going to be based on a change called   m2 that's the cell there m2 and it's going to run 
that macro that we just said document preview so   we look inside our job info screen and we take 
a look at m2 this document template load if the   user makes a change to m2 and we also want to 
make sure that b8 does not equal empty what do   i mean by ba why is that important well we take 
a look at b8 if we remember that is our template   row here right b8 is located here it is our long 
path right remember we're going to extract that   path from that so i need to know that path that's 
very important also i want you to check here is   b9 is it but first we need to make sure that we 
actually have a template so it's very important   to make sure so as long as b8 is not blank then we 
can go ahead and load it up so that's what we're   going to do so how do we load that document well 
we're going to use this macro right here called   show document preview and that's the one we were 
just in right here show document for you we're   going to first the dimension the jobs folder 
i need to know what folder it's going to be in   we're going to place it in and i also need to know 
the document folder as a string what is the file   name and the file path that's important i'm going 
to focus on job info first thing what i want to do   is if there's any current document when i make 
a change right i want to make sure to delete   the first one anytime we change if i decide i 
want to change a different template i want to   make sure that we're deleting the current one and 
we're going to place the other one so to do that   we first need to do if it doesn't exist it would 
create an error and therefore we've wrapped it in   onair resume next and on air go to zero so we're 
going to delete any existing template delete any   existing output document it may not be a template 
and just put document all right so next up we're   going to set that job folder that's based on our 
info screen e3 remember we browse for that and   then what windows we're going to add a backslash 
onto that that's our shared document folder let's   just call it our document folder right our main 
document folder because we have subfolders in that   once we have our main document folder i want to 
make sure that it is correct right make sure that   it is the correct path to do that we're going to 
check it here if the directory of the job folder   equals empty or perhaps the job folder only 
equals the backslash this could happen if this is   empty and we add this it's only going to be the 
backslash so in that case i'm going to let the   user know please select a select a job folder or 
set a jobs folder and then organizers are going   to run that macro browser we're going to help 
them by running that macro again we're going to   make a second check if the directory jobs equals 
empty or the job folder still then we're going   to exit the sub right if they still did not 
manage to browse for a folder and set a folder   then we're just going to exit the sub okay we're 
going to turn off screen application updating to   false right we're going to do that that's going to 
make things faster and a little bit easier on it   also what i want to do is i want to check what is 
located is b9 equal does not equal empty right i   want to know remember this is very important 
taking a look inside our b column b here remember   if it's been saved if we've created a saved it's 
going to be in b9 if it's just the templates in b8   remember b9 is going to come directly from here 
right if we've created a cover letter it's already   saved it's going to be located right here and it's 
going to come directly when we load that job it's   going to come directly in b9 right if i select 
here we see that b9 and i'm loading this job now   contains that whole path so if it does then we're 
going to load this document if b9 is empty then   we're going to load the cover letter so we need 
to know which one to load we need to differentiate   between those two file paths so if b9 does not 
equal empty then the file path is going to be   in b9 it's the generated cover letter it's already 
been created else we has not been the file path is   going to become whatever's in b8 that's why that 
file path in b8 is so important okay so now we've   created a file path either one that's been created 
already or we're going to use the template now   what we want to do is we want to check i want to 
make sure that that file path is correct so you're   going to use the directory command file path vb 
directory if it's empty or the file path itself is   empty then we're going to exit the sub we're going 
to exit on a missing document now what we want to   do is we want to create a word document inside 
excel right i want to create that word document   just as we did so we're going to use the o l e 
objects we're going to add an object okay and what   kind of object is going to be the file path based 
on this file path right that file path is going to   be our word document we don't want to link it we 
don't want to display it as an icon and we want to   give it a specific name document preview so this 
is going to load that word now it's been loaded   but it hasn't been placed yet i want to first 
thing i want to do is with that shape i want   to lock the aspect ratio that's so it doesn't get 
skewed i want to place that top left position in   l3 and these two lines are going to do that that 
means when we create that document or load that   document it's always going to be placed directly 
inside l3 i want it right there also you're   looking at this cork background how did i do that 
well that's just basically a picture background   so if we go into the page layout and i delete the 
background you see it's been deleted and if i go   back in here and add a page right and i have that 
page that's i've got it here in this cork board   here it's just a wallpaper that i did so it's just 
a really cool wallpaper so that's all that that is   okay so leaning back i want to place it directly 
the top left position is always going to be in l3   that way it's going to have the same position and 
that's how we do that inside the code l3 the top   position the left position that's it that's all 
we have to do to open it up and then don't forget   to turn on application screen updating so that's 
a very very easy code that's all we have to do   to place that word document directly in here okay 
cool so we've been over that now let's go quickly   over saving jobs this is not an integral part of 
it but basically what i want to save jobs or new   job or delete a job this one i should be creating 
this i'll do this in patreon it didn't get to this   code yet no code assigned to you could try it 
yourself that'd be great deleting job i think   i did let's just take a look here i think i may 
have created a macro for it we should check and uh   let's uh take a look and assign the macro right 
job delete okay i did good enough all right so   let's save our work so basically i've got a few 
macros that are going to allow us to save the   job load a job when i click on here create a new 
job and delete a job so let's go over those unique   macros they're very simple so we're going to go 
quickly over them the job markers are located here   adding a new job all we need to do is first of 
all i want to make sure that any document that   got created gets deleted just like we did before 
then i've got a name range called job data this   job data is based on all the job data so if i go 
into formulas name manager and i go to job data   we're going to see those dancing ants around that 
job data it's basically all the data here inside   here so all of that data is going to be associated 
all those cells are associated so we see the job   data it's going to be based on all those cells so 
basically it allows us to clear all those cells   out very very simply there so that's based on 
job data so first thing i want to do is clear all   that out and then i want to clear a few additional 
cells out i also want to clear out b9 and b3 clear   the job id and the selected row very important and 
also i want to select g5 that's going to ensure   that just as soon as we do that on the add new 
we want g5 so that the user can start putting in   a job title or position directly in g5 okay once 
we have that that's all we need to do what i want   to do is i want to save and update now it's the 
same macro whether we're saving it a new one or   we're updating it is the same macro all we need 
to do to differentiate that if it is a new job   we know that there's no row associated with that 
job and so we want to make sure that the job row   is blank because there's no job id so we know that 
there's no job row if there's no job row we know   it's going to be a new one if it's an existing job 
row we know the rows here so b4 is going to tell   us whether it is an existing job or a new job 
okay so to do that we're going to base on that   but first thing we want to make sure is we 
want to make sure that we have some required   fields g5 that is our job title we want to 
make sure that that is not empty if it is   please let the user know also if b4 is empty 
remember this is we know it's a new job then   it's a new job right if it's else an existing 
job b4 would have value if it's a new job what   do i want to do well i want to assign it a brand 
new id and i want to give it a brand new row the   next id the next job id is going to be based on a 
max formula and that's going to be set right here   this next one right here located in b5 we're going 
to use the max formula based on all the job ids   plus 1.

So that's going to set a brand new job id 
one more than all the job ids so notice it's four   because we currently have three one two and 
three jobs so the next one is going to be four   so the first thing what i want to do is place that 
job both in a7 here which is the next available   row and i also want to place it directly inside 
b3 so we're going to do that just inside the code   so to do that we're going to take that job row 
setting that up to the first available row based   on our jobs database this is going to give us the 
first available row next up i want to set that job   id in two places one it's going to be placed in 
b3 coming from b5 and also we're going to place   it in column a just those three things that we're 
going to do for new jobs that's only for new jobs   however for existing jobs all we need to do is 
extract that row from b4 that's all we need to do   now for either new or existing jobs all we're 
going to do is we're going to run a loop from 2   to 14 and we're going to map that data so in this 
case all we're going to do is run a loop for each   column in that we're going to look to this cell 
and i'm going to look inside g5 whatever's in g5   here i'm going to place directly inside column 
2.

So we're going to do that all the way from 2   all the way to 14 placing looking in j5 placing 
it here looking in g7 placing it here so we're   gonna loop from two we're not starting at one 
because our job id has already been placed so   we're going to bring it all the way here all 
the way to the last row from the 14th column   this is column 14 here that's all we have to 
do to save that data to the database and then   all i want to do is run a saved message all 
of that saved messages you may have seen it   when we placed it here when i saved it it was 
that fade out message so as soon as i say it was   pretty quick on this one save and update you'll 
see job saved right up here i'll do that again   job save see that little green marker right here 
i just wanted to let the user know that the job   has been saved without them having to click on an 
ok so we're going to do this run this timer run   this loop and it's going to slowly fade out that 
message until it's completely gone running through   a timer and then it's going to delay and then 
we're going to show visible equals false that's   all we have to do relatively simple you can just 
copy and paste this on job load it's very very   simple all we're going to do is the opposite of 
we didn't save basically the opposite when i load   a job i need to place the id here i need to know 
the selected id that id is going to come from here   this id of the selected job is right here this 
is one job id 1.

I'm extracting that we're going   to use an index and indirect so basically we're 
going to look up i'm going to look up that job   id i want to return the job id but i'm going to 
look up the job title and i'm looking up from   d using the indirect and whatever row that's been 
selected that row is in b6 so d and v6 is going   to return our job name we're going to run a match 
through that through the job title here and then   we're going to return a row that row is going to 
be used on the index so it's going to return that   row all i'm going to do is take that row here and 
place it directly up here so when i select on the   job i want it to load that's based on selection 
change so let's see how we do that inside the job   info here based on worksheet selection change 
if the user makes any selection between d4   and also we want to make sure that d contains a 
value if it's not empty then we can move forward   first thing what we want to do is i want to 
take that row and place it directly inside b6   i want to check b7 if it equals empty then let 
the user know please select the job from the list   if it is not and we're going to exit the sub 
if it's not empty b7 is going to take that job   id right that's based on the selected i'm going to 
move that to b3 once it gets moved to b3 here that   job row is going to automatically be generated 
then i know that job row then i can run the macro   that we're going to run that next macro is called 
job load job load right here that is the macro   that we're going over right now called job load 
first of all we're going to delete any preview   that might exist we're going to clear all the job 
data here using that named range of job data we're   going to make sure that b4 isn't empty if it is 
we're going to let the user know to please select   the job from the list assuming that it's not empty 
we can then put that into a variable called jobro   running that loop again but this time bringing the 
data from the jobs database using that map data   into there that's it that's all we have to do 
then what i want to do is i want to load the   correspondence history this is not something we 
talked about a lot but this is the correspondence   right this is a correspondence database right so 
here what i want to do is i want to run all the   correspondences emails calls or whatever based 
on job id one so we're going to use a criteria   and another advanced filter that criteria is based 
on a link to b3 and the job info that way we can   show the job id we're going to have those results 
appear here then those results are going to come   directly over inside our job info here bringing 
that in notice that this is in this particular one   here is a merged cell so this takes on two columns 
so when we do that we have something brought into   a merge cell two columns we need to make sure 
that we compensate for that so notice that we   have a blank column here when we have a blank 
column in our results here we also want to make   sure that there's a blank column in our original 
data otherwise it's going to create an issue so we   want to make sure that we run our advanced filters 
always going to go to n got a criteria is going   to be r2 through 3 and our results are going to 
come from u2 through y and so the first thing we   want to do is certainly make sure we clear out any 
results that might be here if they haven't already   been cleared out but job info takes care of that 
our job data takes care of that we've already done   that when we cleared out the job data up here 
that took care of it right here so what we're   ready to do is going to get the last row on the 
correspondence database this is going to focus on   with the correspondence database we're going 
to get that last row running an advanced filter   we're going all the way to column n and then 
we're going to have the results come through   u2 through y2 and with the criteria of r2 through 
r3 we're going to check to make sure we have some   results based on column u if they're we don't have 
any results we're going to exit the sub and then   all we need to do is bring in that data just like 
this job range f19 through j in the last results   row plus 16 right we're starting on row 19 here 
we're coming from row 3 here there's a 16 row   difference so we must add 16 to our destination 
right here okay that's it and now what i want to   do the last part of load if b8 doesn't equal empty 
then we're going to show the document we want to   run the cover preview but only if b8 does 
not equal empty so let's take a look inside   here i want to make sure that b8 doesn't 
isn't empty or even in b9 if it is b9 but   also it so notice that we have b8 and b9 i'm going 
to change this to b9 so we could load the template   oh we could load this i'm going to change that 
to b9 because i like that better so that should   be changed to b9 it's going to be the same result 
though but i want to make sure if there is a saved   one we're going to show that preview when we load 
it in because we delete it automatically up here   but we need to show it up here so if there's 
one that's been saved with the job it's going to   show otherwise it's not nothing's been saved here 
something has been saved here so we want to make   sure to show it here i like that that looks really 
good that's how we load the job information okay   great but how do we get this really cool automated 
text in here from that well that's going to be   creating a word document creating that dynamic 
template with the dynamic content so let's go   ahead and go through that and see how that's done 
that was the last last one is job delete that we   didn't go over just yet all we're going to do is 
extract the row from b4 and then we're just going   to delete it that's pretty much it relatively 
simple then we're going to run add a new job which   just clears out all the content okay so let's get 
into the cool part now also the word macros i want   to know word macros then we're going to go through 
email markers and then that's going to be it's   great so how do we do this how do we create this 
and what am i going to be doing let me just show   you exactly what we're going to be doing what i 
want to do is if i have a job here let's pull this   job out we don't have any type of and i've got a 
template here right i want to take this template   and i want to place it with dynamic data again i 
went over this one but i just want you to see it   before we create that macro all i'm going to be 
doing is clicking here we don't have let's put in   some contact name let's put in a company contact 
name let's put in dave david davidson and then   we'll put an email here david david.com okay and 
the street address one two three four main street   and that's enough requirements phone now we're 
good to go with that okay so i'm gonna save that   and now what i'm to do is i'm going to create 
that contact letter it's going to take both the   personal information and the company information 
it's going to automatically create that document   so notice it's all been created based on this and 
we don't have a city that's why this is zero but   we could probably add some code so that's all 
we need to do so we could how do we do that well   that's with this macro right here that is the same 
mac or that's a time assigned to this right called   create letter and that's the macro we're going to 
go over right now called create letter first thing   we want to do is i want to develop a constant this 
is going to be for our word document replace all   we're going to use that as 2.

pexels photo 4050312

Okay that's our only 
constant i want to set a job folder as a string   document name document path template job title and 
company all all strings along with the variable   format our variable name and our variable value 
profile picture all strings as well we're going   to demand we're going to use late binding that's 
really important right you know sometimes you get   a library doesn't exist here insider references 
notice there's no word document right this is   very important it avoids error so when you're 
programming i really tried to do this when you're   giving out your applications i have not selected 
a word document what i mean by word document is   if you look down here under microsoft word all 
the way down here early binding is necessary when   you do that to do let's take a look here word this 
will be this one right here microsoft word object   14 16 or whatever it is right i'm using an older 
version but so what i don't want is for the end   user when you hand this i don't want them to have 
to find this look for it hope it is so that would   be early binding you mean this is required this is 
not required because we're using late binding okay   so we usually generally don't want to do that i 
don't want to do that so to do that we're going   to create we're going to bind that application 
inside our code we're just going to call word   application as an object and word document 
as an object just like we do when we do email   email object or email app okay first of all we're 
going to mention the variable row as long right   i want that as long why is that important because 
we're going to loop through those variables we'll   be getting into that i want to make sure that b8 
contains a value i need to have that cover letter   template that templates located in b8 without that 
full file path of that template we cannot continue   i also want to make sure that there's a job title 
and company before that that's going to be part   of our file path right when we create that file 
name let's take a look inside here here and here   i want that those get created i want these 
folders these individual folders to contain   the company name and the position notice these 
folders contain company name and the position   and the documents that created also the company 
name and the position so that company name and   the position that we're applying for are very 
important so we want to make sure that that   data is valid right so i want to make sure that we 
have a title and we have a company name so g5 and   j5 are both required so we're going to let the 
user know if the either one of those are blank   if g5 equals empty or j5 equals mt then 
please make sure to save a job title   and company before creating a cover letter we're 
going to exit the sub out okay the first thing   what i want to do is check for accurate job 
folder i want to check that's very important   checking for that we have a proper job folder and 
located in the info so info e3 backslash we went   over this once before there's a few times we're 
going to go over it could create a class on this   directory job folder making sure that it's 
not blank or that it doesn't just contain   the backslash if it is let the user know and 
run the macro we went through this already   picture profile we're seeing that picture profile 
remember that's the picture profile picture that   the user browse for and we place it inside 
e4 remember that went inside e4 right here   that is that file path for sally's profile picture 
located in e4 so we want to make sure that that is   because it is that one other thing inside this 
let's pull up a word template inside this word   template cover letter two right let's take a look 
inside this this is inside word i want to let you   know we've got this cool profile picture but it is 
this picture that we're going to be changing right   so what i did is i made sure that regardless 
of the template i wanted to give it the same   name so if we look inside our layout right here 
and we do selection pane we see that the name   of this picture is called profile picture for uh 
regardless of the template i've given it this name   that way we can call out much easier just like we 
would if we had a shape in excel this shape also   has a name if i know the name of the shape then i 
know the name so if we take a look at let's take   a look at one here same thing right this one i 
didn't use the default picture but it's the same   thing if we check layout selection pane i also 
call this profile picture profile picture okay   so now that we know that the profile picture is 
always going to have the same name it's going to   help us encode so that way we can change the 
picture just as we would inside excel okay   so getting back inside our code we've got that 
profile located in e4 we're going to check to make   sure that profile is accurate if the vb directory 
is empty meaning there's something wrong with the   file we're going to set that profile picture that 
string variable to be empty just in case i'm going   to set the job title based on g5 the company 
based on j5 i want to know the template path   that's going to be b8 that's the ta path of that 
template just a refresher b8 is our template here   and it's coming directly from our template path 
right here so these are our template paths that's   where it's coming from and we need to know that 
because we're going to pull up that template path   we're going to have to customize it then we're 
going to save those changes as a different file   name i also want to create a document name now 
the document name is going to be used for both the   name of the template the name of the cover letter 
and the folder as well so that's going to be the   company name an underscore and the job title so 
we're going to use both that for the document name   and the folder name right i want a separate 
folder for every single job that's going to   keep things nice and organized okay so we're 
going to do is we're going to check for the   job folder and we're going to create as 
needed right it does a job folder exist   if it doesn't then please create it so that's 
what we're going to do with this line of code   if directory the job folder and the job document 
name right it's inside there so we're going to   combine both the job folder and the document name 
does that folder exist if it's equal to empty that   means it does not exist then we're going to 
create that directory mk directory otherwise   known as make it directory we want to make that 
directory so we're going to do that with just   this line of code make directory job folder and 
document that creates that brand new folder then   what i want to do is i want to create a full path 
right we want to create that new word document   and we want to create a path for it so that path 
is going to be put into a string variable here   it's going to be the job folder plus the document 
name and then we're going to put a backslash on to   that once again the document name here and dot 
docx that's going to create that full document   path so that way as soon as we customize that 
template we can then save it as that new name   that way it will not disturb the original template 
okay now we're ready to open that word document   okay so we're going to do onair resume next we 
do need to check to see if word is running or not   so we're going to set the word app to get 
object right we're going to get it if it's not   running this is going to create an error so we 
know that if error number does not equal 0 there   was an error that was created it that means it's 
not running then what we want to do is we want to   create that word up so we're going to clear the 
error and then what we're going to do is we're   going to set that word up we're then going to 
create the word application because we've checked   here it's not running so now we want to create 
it so we can do that just with the air set the   word app equals create object word application 
this is that late binding here we're setting the   word app right here and not up at the dimension 
right so we're late binding this creating it here   all right now if the work is running now on air 
resume next okay on air go to zero now what we say   i'm decided just for this training i didn't want 
that word application to be visible in other words   while i'm creating it i don't want to make that 
word application visible if you do want to make it   visible which you certainly could just uncomment 
this out then the whole application will become   visible when we create it otherwise you can 
comment it out right for us we have that   word application we have it it's already inside 
excel right we are showing it here so we really   don't need it visible inside the word application 
itself so i just but you can do that if you like   all right so continuing on now what i want to do 
is i want to create the document all we've done   is create the application or set the application 
but now we need to create and open the document   itself so we can do that here setting the document 
that word document being the word application that   we just created based on the documents we're 
going to open it we're going to open it based   on the template path remember we're opening that 
template path we want to make changes to it so   read only is going to be false we want to open and 
make changes to that template once we've done that   we're going to check the profile i want to add 
that profile picture it's very simple just as   it is in excel assuming it's not nothing right we 
want to make sure that the profile picture exists   then we're going to use word document shapes 
just as we do in excel profile picture because we   know the name of that shape we're going to fill 
it user picture profile picture exactly as it is   in excel except for this part right so this part's 
exactly like it is in excel except this part we're   associating with a document as opposed to a sheet 
but that's it so we're going to set the profile   picture set profile picture okay so now that we've 
set the profile picture now what we want to do is   i want to loop through all the variables right 
i want to look inside that template i want to   find those any of any of the associated templates 
now what i'm going to do is i'm going to delete   this one here we've created it i'm going to delete 
this right and then what i want to do is i want to   load that template again since there's no created 
i'm going to load that template so what i want to   do is i want to look for anything that's called 
recipient name or company or full name or anything   like that and i want to replace it with that so 
what we're going to do is we're going to create   a loop and we have a variable row from 13 all the 
way to 30.

We're going to look for first name and   we're going to replace it with sally we're going 
to do that through every single variable here and   that's just what we're going to do inside the code 
so we're going to create that loop starting right   here starting the variable row for the variable 
row equals 13 230 loop through all the variables   we're going to set the variable name in column 
from column a in the variable row determine the   variable name what is the value of that row 
we're going to set that in this string called   variable value i also want to know the format 
what is the format maybe we have dates or number   formats or currency formats or dollar formats or 
whatever so we're going to extract the format that   format is going to be whatever format is in here 
so i'm going to extract that format so if this   phone format is like this or it's a dollar or date 
format i want to extract the format of the cell   and i want to put that format inside a variable so 
we can do that with this line of code so variable   formats equal to dot range b and the number 
format of that cell we're setting the format   it is that that's important because then we can 
apply that format inside the word document so   that dates and things get applied for it now 
that we've created that word document we can   work specifically with that word document so with 
the word document we're going to look for all the   content using content find what i want to do is 
i want to look the through the entire content of   that word document and what i want to do is i 
want to look for something i want to look for   anywhere it says those variable names inside the 
text i'm looking for that and i want to replace   it what do i want to replace it with i want 
to replace it with the variable value but i   just don't want to replace it with that value i 
want to set the format of that based on that so   we can use two things we can use format or we can 
use application worksheet function text now keep   this in mind this may not work with different 
language packs so keep that in mind format   sometimes works as well so application worksheet 
function text variable form variable value we're   going to set whatever the format is so this is 
going to apply this is going to replace that   and then all we need to do is actually tell it 
to make those replacements with this execute the   replace and we're going to place all so what 
this do is find and replace all instances of   this variable name with this value here with this 
format and that's all we have to do okay so now   that every all the text has been replaced inside 
that word document now what we want to do is we   want to save it as it however if that path if that 
exact name in that exact folder already exists   i want to make sure that we're going to delete 
it first right so i don't get an error so to do   that we're going to kill any document meaning 
delete any document with the exact same path if   it doesn't exist it could create an error so we 
want to wrap it in honor resume next and on error   go to 0.

Okay now what i want to do is i want 
to create i want to save that so we're going to   create two things we can create a pdf just like 
this inside that and we can save the document so   we can do both creating it in a pdf format which 
is great for that so the word document say that   right we're going to export as a fixed format just 
as we do in excel that output file name is going   to be that document path we're going to export the 
format as a word document we can also use a pdf   word doc export format pdf we can also do word 
document save as a document path saving it as   a document path then we can just close it we 
don't need to save it now false because we've   already saved it up here closing and then we can 
quit the entire word application that's going to   close that entire word application this closes the 
document itself this closes the word application   okay now we've created that now all i want to 
do is make sure that we update b9 remember b9   is going to take on that cover letter so when i 
save it i want to make sure that b9 takes that on   and then last thing we want to do is i want to 
save that update that's going to ensure that   that document path that we just put in b9 that 
that gets saved inside the database right here   so when i click on this create color cover letter 
it's going to take that open that word document   make all those changes create that update and 
it's also going to save it inside our js database   so right here this now took on that entire path 
right as you see this path was taken completely   done and it's been put directly in that path 
and that's where the document now resides   then what i want to do is i want to actually run 
the macro that we already created that's going to   preview that so the last thing inside this macro 
is that macro show job show document preview   is that one that i want to run so when 
i run that let's escape out of there   getting rid of this extra text we don't need 
that it's going to only create problems for us   so job information so now what i want to do 
is i want to display that document that we   just created so that does it with the macro that 
we've already done so we're running that document   preview that macro here updates the preview of the 
template with the actual document that we created   very very cool okay cool one more macro to 
show you pretty much this is the macro that   we're going to allow us to create this 
really cool automated email cover letter   we also have variables in that so when i create 
an email i also want to add attachments to this so   if i click here i want to add an attachment maybe 
we want to add some job files here some documents   here there's nothing in here there we go nothing 
in there okay so maybe we want oh this is we got   stuff in here all files i'll update that i want 
to add some files to that so how are we going to   do that well the best way to do that is to be able 
to add multiple attachments right so maybe we have   multiple attachments want to add a cover letter 
maybe we have an application that we've created so   to do that i want to be able to add multiple and 
notice i've just added two this is the first one   here this is the second one and they're separated 
by commas so the first thing what i want to do is   i want to be able to add attachments i 
want to place them directly inside here   so how are we going to do that well that's inside 
the module called emails and we're going to focus   on those macros now and that's the last macros 
so the first thing i want to do is save template   i think we went over this one saving template all 
we're going to be doing in this one is determining   the template row if it's a brand new template row 
we're going to be setting that first available row   b10 is going to hand that template row remember 
correctly b10 is going to let us know based on   this template based on this is it a new template 
or not email template row if this is a value we   know it's already out of town if we decide we 
want a new template we can do that just have a   new template okay so we have our templates here 
we saved it in here so this is simply going to   save this information saving anything any changes 
that we make in bnc all we're going to do is when   we click that save button all it's going to do is 
make changes saving the name saving the subject   and saving the message here that's all we 
need to do with that that's relatively simple   okay but continuing on i want to add attachments 
remember you saw me add multiple attachments to a   single email and it's actually unlimited so how do 
we do that well the first thing what we want to do   is i want to make sure that they get saved with 
the same name and i want to make sure they get   added so that means when i add it it could be from 
everywhere right we don't know where they're going   to browse for those attachments could be the 
desktop or whatever so i want to make sure that   they go directly to the right folder they those 
attachments need to go into the specific folder   for this job to keep everything organized they 
need to go for the position and for the company so   again we need to extract those variables i need to 
get the job folder of course that's going to be an   e3 making sure that it exists and also making sure 
that we have both a company name and job title we   went over that already those are required we need 
to have both of those and i also want an accurate   job folder again checking to make sure we did that 
already no need so we did it up here don't need   to do it again okay check for out control folder 
that's done twice don't need to do that twice but   i do need a job title that's going to come from g5 
and i do need a company that's going to come from   j5 these are going to form our folder just as we 
did before the document name combining the company   and combining the document this is going to 
combine them again we're going to make sure that   that folder exists i want to make sure checking 
to make sure that the company folder exists just   as we did before now we're going to set the email 
att this email att we're going to be setting right   here this is all up here right up here as a file 
dialog here so setting that up it's a file dialog   we're going to set that email as a file picker 
right file picker and it can be any type of file   so it just says please select a file to attach 
let's let's update that text there file could be   any type of file to attach so now that we've given 
them instructions right and again we want any file   going to be able to add all the files you should 
probably clear out these filters before that's why   it didn't come up so adding those files adding 
any type of file allowing multi-select is false   if they don't select anything we're going to no 
selection but if they do select something i'm   going to separate it i want to know one the file 
name based on the directory of the selected items   and i want to know the full file path okay now 
what i want to do is i want to copy that location   from wherever it's located into the jobs folder 
now in case if we're going to use on air resume   next in case that already exists in that folder 
in case we're browsing directly from that folder   so we want to copy it i want to run 
a copy i want to copy that file path   from its current location into this new location 
job folder document name backslash and the file   name that's by the file name we're keeping that 
file name the same so that's going to copy that   file from wherever it's located into that specific 
folder for that job and for that company once we   have that what i want to do is i want to add it to 
the attachments however i want i may have want to   call i want to add a comma to separate it in case 
there's already a value there but so want to check   is something exists in p5 p5 is where we're going 
to hold our attachments p5 is going to take on   that attachment right here so if there is a value 
there that i know to add a comma to separate them   so we do need to run a check inside the code and 
we can do that here if p5 does not equal empty   then p5 equals whatever's already in p5 plus the 
comma plus the file name otherwise that means p5   is empty simply just add the attachments so we can 
do that that's how we can add multiple attachments   because we're simply adding what's already there 
to whatever is existing and we're making sure that   if there's something there we're already we're 
separating it by a comment that's very important   okay great now we are ready to send that email 
right when i send that email i want to make sure   that those attachments get done as separate right 
when i click send now i want to make sure that an   email gets created with two separate attachments 
you see here there's two separate attachments and   i also want to make sure that the comment david 
david's here and all the information is dynamic   and gets created so how are we going to do that 
well we do that with just a little bit of code   and i'm going to walk you through it first thing 
we want to do is dimension the outlook mail again   we're using late binding also on outlook email as 
an object and the outlook app as an object we need   email to subject message variable name variable 
value variable format and the attachment file   notice the attached file is going to be an array 
attachment file is a string it's going to be an   array because they're separated by commas and 
we need to loop through all of the attachments   inside a single string so we're going to have a 
dimension the attachment number as long i want   to know the attachment number how many as we loop 
through those attachments we're going to set those   variables the job folder and e3 just as we've 
done the job title the company the document name   just as we did before the email 2 is going to 
take on whatever's in p3 subject is going to be   whatever is in p7 and the message is going to be 
whatever's in p9 great so again we're going to run   through all the variables this time again it's 
a little bit different this time we're creating   those variables before the email so this time i 
want to take the subject and the message wherever   those variables are found i want to replace them 
with the actual information so again we're going   to run a loop from 13 to 30.

We're looking for 
anywhere here and we're replacing with any values   here and so that's what we're going to do inside 
the loop however we're going to use the replace   statement to help us do that so we've already 
defined the subject in the message so now we're   going to set the variable name the variable 
value and the variable format just as we did   before exactly like we did before however we're 
going to take that subject and we're going to   take that message and we're going to look for the 
variable name and we're simply going to replace   it with the variable value and again also using 
the worksheet function text we're going to change   the format right i want to make sure that the 
format of those variables gets on or date currency   whatever it is and to do that we use application 
worksheet function we're going to take that format   so we're going to update the subject and update so 
as we move through every variable if any of those   variables are found they're going to be replaced 
with the actual value that's it that's all we have   to do to get that proper text we're ready to 
create our email so to create the email we're   going to set an outlook application we're creating 
that again late binding setting that application   then i need to create an email within that 
application so we're going to set that outlook   mail equal to the outlook app create item 0.

It's 
going to create that email we're going to focus   on that email we're sending that email to to our 
email two are subject to our subject our message   to our message that's great but now it's time to 
figure out if we're going to be adding attachments   or not how do we know well we know based on this 
if p5 is empty we know that there's no attachments   however if it is not empty we know we're going 
to be looping through possibly more than one   possibly more than one attachment so how do we do 
that well first we're going to check if p5 value   does not equal empty we know that we're going to 
loop through the attachments what i want to do is   i want to create a split i want to create an array 
using split and we're going to split that entire   string in p5 we're going to split it by that comma 
that comma is our delimiter so that's going to do   is going to allow us to create an array based on 
all of the email attachments in there now remember   these are only the names right only the file names 
we still need to add in the whole folder path   so we're going to create this array attachments 
file then what we're going to do is we're going   to create a loop four attachment equal l bound 
meaning the lower the lowest loop the lowest value   to the upper bound the highest value this is the 
dynamic loop this is going to be zero usually   and the upper bound okay so what that means is 
we're going to loop because we don't know this is   going to create a dynamic loop based on how many 
attachments are in p5 so then we're ready to do so   now i've got the file name each individual file 
name so this attachment file zero zero is going   to be one one is going to be another one starts 
off with zero so this number as this number grows   this specific value changes so of course this 
is only the file name right our attachments   only include the file name right that file name 
here is coverletter.q or fredfederspharmacy.docs   so those are only the file names so what i want 
to do is i want to combine this file name with the   folder that it's supposed to go into to create 
that full file name so we do that here that   file path is going to be based on that job folder 
and the document name and the backslash and   of course our attachment file based on that 
variable so this is how we loop that this gets us   our file path that all we have to do once we have 
that file path this we don't need that was just   for a test all we have to do then is simply attach 
that file right here attachments add file path so   this can loop through unlimited attachments inside 
a single string that we can do that that's all we   have to do last thing is i'm going to display 
the email now if you want this email to be sent   directly use dot send i'll put a comment here use 
dot send to send spell right send email directly   okay so that's all we have to do great and so that 
creates the email very very cool all right so in   this training you how to create a dynamic email i 
also show you how to create dynamic word documents   using templates insert pictures creating multiple 
jobs saving data mapping conditional formatting   selecting on job loads wow what an incredible 
training thank you so much for joining us   today i really do appreciate that if you like 
these trainings don't forget to click subscribe   smash the like button comment below i respond to 
each and every comment i really appreciate your   continued help and guidance thank you so much and 
we'll see you next week for a brand new training

As found on YouTube

You May Also Like