How to Send PDFs or Files through Outlook using VBA

Hey User! Hope you having a good learning. I have been hearing lot of issues about sending the emails to your managers one by one and end up having entire one hour wasted. For instance, i want to send 100 emails to different managers with different files. It can be PDF or excel file or whatever. As long as its not a garbage, we are happy to send it. 🙂 Confused? Pls Check Image3 here , if you think this is what you need to do then read this article. And, no need to thank me 🙂 It is ok. More to come in your way.

But how to do that. Shall we write this long list in VBA Code? Grrrhhh……no….What if tomorrow file path or name or recipients list changes. I am not going to edit my code…may be I am too lazy or may be I am too busy in other important things.. well, I might be busy in learning Power query or PowerBI or SQL Server. So, how about linking it with excel spreadsheet and that’s it. You have changes ? Please do it in excel . Be my guest.

First thing first, go in Tools-Reference and select your outlook library so we can use Outlook VBA Classes . (See Image1)


Copy below code and paste in your VBA Editor but have a look on image2 first and set up your excel data accordingly. 🙂


Sub sending_files_or_PDF()

Sheets("Email").Select  ' your sheet name here

Dim to_email As String   ' CAPTURE Who is in to
Dim empid As Long
Dim i As Long
Dim File_name As String    ' CAPTURE THE FILE PATH

Dim o_look As Outlook.Application    '   Outlook object is dimensioned here
Set o_look = New Outlook.Application  ' created an outlook object it is ready to use

Dim o_mail As Outlook.MailItem
'Set o_mail = o_look.CreateItem(olMailItem)   'dont use "set" statement here because we have to send new email for each file.
' if you write this line here and not inside the for next loop, it is going to overwrite first email by second and second by third and so on.
'Because o_mail object needs to be created from a fresh start, every time your email is going to your distribution list. So, down below
'it is written inside loop. This is so beautiful concept...cheers :)

'starting a loop which is going to take values from excel sheet one by one and do not stop until all rows covered.
For i = 2 To Range("A10000").End(xlUp).Row

'this is where we write o_mail statement...because if your loop runs 100 times, 100 times you
'will have a new email created...the most important line code in this program

 Set o_mail = o_look.CreateItem(olMailItem)
  'fill in the below variables using excel cells...

    empid = Range("A" & i).Value
    to_email = Range("C" & i).Value
    File_name = Range("D" & i).Value
        o_mail.To = to_email
        o_mail.Attachments.Add File_name
         'you can use ".send" as well.but I suggest first for testing purpose using ".Display" method will be ok.

Next i

End Sub

That’s how output is going to look like for every file. Just one email snapshot attached here for your help. Happy Coding!