I'm gonna show you today how to send email with attachments through outlook
from Excel now I've done this before but this one is slightly different I did
this for a gentleman a while ago and he who was sending out because it's his
customers a list of customers which were obviously much longer than this his
their statements or whatever monthly monthly invoices whatever but he also
wanted to have the option of sending to other additional files now the files
didn't always exist and he still wanted it to run whether it existed or not and
so what I did is I created this little this little thing here and so what we
have here is an attachment which is always has to go so this is their
invoice of their statement or whatever and then these are possibly a catalog
and the other one we like the specials for the month and sometimes you'd want
to send the catalog but sometimes he'd want to send this batch of specials for
the month so let me show you how I did it so first of all we're gonna look at
my director ruler this is coming from and if you look at this I have all the
statements and I have catalog but I don't have the specials file here okay
so now normally if you had if you had this running and you didn't have the the
additional files that you're saying it would it would would stop with an error
so let me show you the code so what I did is the same thing is I send email
from Excel the address subject is a string message is a string file name is
a string outlook as an object Outlook mail as an object my attachment as an
object now I've made three paths here path one or path path 2 and path tree is
strings last rows integer attachment attachment to an attachment three is
string this is what's different and so now what I have here is I set it up all
the same without looked and outlook mail and path
is the path to their invoices or statements and so this one doesn't have
a file name on it but these ones on the second the second - path 2 and path 3
are just pamphlets or brochures that are sending out along with the mail and so I
have not only the path I also have the file name at the end of the path here
okay now the subject thus up the subject is sheet one sells for so that would be
that would be here the first name is sheet one cells one file name is sheet 1
cells 5 which is the attachment which is their invoice and then attachment equals
path + filename okay so then we set Outlook mail to e
address and then carbon copy the blank and blank blank carbon copy the blank
and the subject being the subject that we have here which is their invoice
number Outlook mail body so I say first name and I say please find your
statement attachment then I say care to return in best regards now this is where
it's two different so if path two equals if that doesn't
exist then sheet 1 x6 not sent so it's going to put in there not sent and then
go to next attachment or else if it does exist attachment 2 equals path to my
attachments add attachments to sheet 1 cells XC x 6 equals sentence oh it's
gonna put a sent there and then it's gonna do the same thing for the next one
so if directory path does not exist then sheet one's x3 it's going to say not
sent and then go to an D which is the last thing here and then if it does
exist then it's going to send it and so then here we go down the rest and it's
all very similar to what we've had before like my attachments add Outlook
mail display and of course if you take this the italics before the outlooks you
could send it right away and then last row address equals is
nothing and X plus ones he's got go to the top of the list and when this is all
done then you said outlook is nothing and Outlook mail equals nothing
so let's f8 through this and see what it looks like when it runs so I'm going to
start at the top here and go f8 so it's quite happy as it's going along here and
here we go then it's going through that now it's gonna check and see whether
this exists and if it does exist because it's going to the next one and then it's
gonna add this attachment and then we're gonna go to the next one and see if this
one exists so it does not exist so it's gonna go to not send and go to nd which
is at the end here and then it's going to do the rest of the so now you can see
the email here and it says it says you're the one that you be your
statement or whatever and it says catalog which existed and that there
there's your message now it didn't send an error message when that thus the say
the sales didn't exist and it also if you go back to your excel sheet it also
said under under catalog that it was sent and specials it was not sent so
that is how you send email with attachments from Excel and you send
attachments if they exist please subscribe