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.
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