i'm gonna show you how to write a macro
to send email to a specific email
address related to something that people
select on your form we have a basic
application form here so it's just so
I've created a very basic application
form here and so let's fill this out
let's put in Charlie Brown and then
Brown we have an address place here but
then we all have the location we have a
drop-down list and i'm going to select
Edmonton if you notice here i have a
table set up that we're going to use in
our macro and it's gonna it has a
pertinent evidence about the only real
email address on here this is my my
address and the rest of these are just
made up so let's let's select edmonton
from my drop down list so now let's
go toward Developer tab and from the
Developer tab
we're going to click on the tool box
here and we're going to click on insert
and we're going to choose the form
controls and we're going to choose a
button here
so what I'm going to do is I'm going to
place a button on the bottom here and
now i'm on button click it says we can
change that later lets you record a new
macro so the first thing we have to do
is we have to define some variables
let's define path as string
and then let's define file name as
string so i'll go file name one as
string and then i'm going to define
email address as string and then i'm
going to define outlook.application as
an object and i'm going to define
outlook mail item as an object and then
i'm going to define my attachments as an
object's we're going to set a few things
here and so what we're going to do is
set up outlook app create object with
outlook.application and the next one
we're going to do is set my outlook mail
item equals outlook app create item and
then we have 40 at the end of this and
then my last one that I'm gonna set is
set my attachments i want to send an
email with this as an attachment so i'm
setting set my attachments equals
outlook mail item attached now let's
define a few things I'm going to find my
path name and I've just created folder
on my desktop and i'm going to set the
path equal to the folder on the desktop
so i say path equals C users barb
desktop examples with the folder that's
what i want my path to be and now if we
go back i'm going to set my file name to
range c4 so let's go take a look at what
c4 is and ce4 is the last name so I want
filename to be the attachments name to
be brown
ok now so i'm going to put in filename 1
equals range c4 so now what I'm going to
do is I'm going to export the the object
the application
form itself to one of my folders
and I'm gonna save it as a PDF so what
I'm going to do is I'm going to go i'm
gonna go active sheet export as fixed
file format which is a PDF type XL type
PDF the file name is this path that I
had before in my desktop or my folder on
my desktop is example and I mean this as
app form application form and then after
publishing I wanted to open that will be
true now the location is the then what
the drop-down list of cities that we
have on our fom and that location is
range be five so i'm going to go
location equals range E5 value so
whatever it says they're now what I'm
going to do is I'm going to set a range
what we're going to do is we're going to
use a vlookup so i'm going to set my
range to the cells are emails
corresponding to our locations so my
range is c5 to see roses is a fifth row
to the ninth row and the column is the
eighth to the ninth what I'm going to do
is I'm knows set what my email addresses
so my email address is basically
discovered by using the vlookup function
and what we're going to do is we're
going to go email address equals
application worksheet functions so it's
saying go back to the old worksheet and
use the vlookup and using the vlookup
the location is what we're looking for
my range is what we're are what we look
at is the table that we're looking in
and we want the second column value and
we want it to be exact match so we'll
say false at the end here what we're
going to do
we've got this all figured out so with
the outlook mail item that we defined
earlier using a with statement with our
outlook mail item that we defined above
will touch with
and then what we're going to do is we're
gonna put our two and let's put a little
tab here let's go dot 2 and then equals
and we're going to go email address that
what we defined above and then the next
one that we're going to do is we're
going to do a subject so it's going to
be dot subject and what I'm just going
to put a subject line in this in this
email is I'm just going to say your
recent application so dogs subject
equals recent application form so that
was something that somebody just
submitted and then what I'm gonna do is
I'm going to do a body and i'll start
with dot body it's basically just
telling you please find please find the
application form attached and this is a
carriage return and then it just
assigned best regards what we're going
to do is we're going to attach our file
and so we've defined my attachments
above and so then we're going to pull
that PDF form that we save to our folder
and so what we're going to do is we're
going to go my attachments dot add and
then it's easy it's giving the pathnames
were this application form is alright
and then we're going to finish it off
we're going to go dots and all right and
then we're going to do is do an end with
and with statements so we will go back
and then we're gonna clean up our
outlook files so what we'll do is we
will set up outlook mail to nothing and
we're going to set outlook mail to
nothing and organized out set
outlook.application to nothing just to
clean this up with one of a problem and
then we're going to end sub
alright so now let's run this and see
what we get
so the first the first thing we got was
our application form that was saved as a
PDF so here is our email that we've got
figured out now it's got exactly we
mailed it to its a subject to the
application form
there's your PDF dad attach the bottom
here and there is just a little body
that's added so that's how you add email
to a specific result from excel sheet
with the macro please subscribe