How to Send Multiple Attachments within a Single E-mail Using VBA

This question keeps coming up piratically on a weekly basis:

How can I include multiple attachments within a single e-mail?

or

How can I send out an e-mail containing multiple attachments?

or

How can I include external documents as attachments in my e-mail?

The simplest, and most used, e-mail technique used in MS Access is to use the built-in SendObject method and for basic e-mails, or e-mails containing a single MS Access object, it works beautifully.  That said, it is majorly lacking when it comes to wanting to include multiple items as attachments, or wanting to use external documents as attachments.

Now, why in all these years Microsoft hasn’t added such functionalities is an entirely other discussion and well outside the realm of this post.

All that you need to know is there are a couple viable alternatives to enable us to do exactly what we need.  What is even nicer is that most, if not all, of the alternatives can be ported to any VBA program.  So they are even better IMHO since you can create an e-mail module and then import it into Word, Excel, Access, … and it will work as is.

So let us look over a couple options:

Outlook Automation

Several years ago I created a simple function to automate MS Outlook which accepted an array input variable for which attachments to include in the e-mail.  For more information, please refer to my post entitled VBA – HTML Outlook Automation.

With this function, sending an e-mail including multiple attachment becomes as easy as a single VBA command line. For example:

Call SendHTMLEmail("RecipientEmail", "Subject", "EmailBody", False, , array("C:\Users\Test\Documents\sample.pdf", "C:\Users\Test\Documents\ballons.gif"))

CDO Mail

Another very reliable technique is to use the CDO mail library. What is nice about this approach is you do not need to have an e-mail client (Outlook, Thunderbird, Lotus Notes, Windows Mail, …) installed on your computer to be able to send e-mails (unlike SendObject, Outlook automation, …), the library takes care of everything.

To keep things up-to-date properly, please refer to my complete article on the subject by using the following link :

VBA – CDO Mail

Conclusions

The one thing that you need to keep in mind when selecting a method to implement is how the database, or other automated program/file, is being deployed.

Do your users have Outlook installed? If this is the case, outlook automation is simple to implement.  It simply piggy backs off of the existing account and works, no configuration necessary.

Will they have access to the VBA source code?  You wouldn’t want to use CDO to utilize your personal e-mail account and distribute it to users in an unsecured manner where they could simply extract your login credentials and then be in a position to use your account in a malicious manner!  On the other-hand, if you are distributing a compiled database, not knowing whether your users have Outlook installed, CDO mail becomes a very effective solution!  You could even setup a table to house to configuration settings so your users could input their e-mail account information and then simply read them into the function at runtime.

12 responses on “How to Send Multiple Attachments within a Single E-mail Using VBA”

  1. Karen Silva

    Thank you so much for this article. I was looking all over the place for such a simple explanation. I’ve already got everything working thanks to you. Great site!!!

  2. Crystal Laird

    I currently have a database program that I run to extract files I have set up that include excel, word, and pdf files. When I distribute those to my list of email recipients, each person receives variations of each report; for example the detail IS each department manager receives only the page # that applies for their department instead of the entire report. The program I have at the moment is slowing beginning to malfunction, drops full packages off a persons email and it is random. I am looking to find a way to send the reports out not in pdf, as it currently does, but in excel. Is there a way to do what I am describing using VBA in excel? It would also need to compile in a word document and pdf files.

    1. Daniel Pineault Post author

      Crystal,

      It can most certainly be done, but you’d need to explain in more detail the requirements. Your best bet is to post your question in the UtterAccess forums and you will most certainly get the proper guidance you seek.

      On the other hand, if you are looking for profesionnal help with the project, feel free to contact me through my commercial site at: https://cardaconsultants.com/contact-us/

      Daniel

  3. Tatjana

    Hi,
    thanks for this!!
    Could you please tell me how can I use your function to send email without an attachment?

    Call SendHTMLEmail(“RecipientEmail”, “Subject”, “EmailBody”, False, , array(“C:\Users\Test\Documents\sample.pdf”, “C:\Users\Test\Documents\ballons.gif”))

      1. Tatjana

        Thanks so much! You saved the day! 🙂
        Now I have a problem to send email to multiple recipients, I don’t know if this set or its problem with Outlook security?

        1. Daniel Pineault Post author

          To send an email to multiple recipient you simply create a delimited string with all the recipients’ email addresses and pass it as your TO input variable.

          “someone@somewhere.com;someoneelse@somewhereelse.com;”

          So then you could simply do
          Call SendHTMLEmail(“someone@somewhere.com;someoneelse@somewhereelse.com;”, “Subject”, “EmailBody”, False)

          Or

          Dim sTo as String
          sTo = “someone@somewhere.com;someoneelse@somewhereelse.com;”
          Call SendHTMLEmail(sTo, “Subject”, “EmailBody”, False)

      2. Tatjana

        HI, Thanks for this. You save the day!
        Sorry to bother you but it will not send email to more than 1 address.
        Is that set in the code?

        Thanks!!!

  5. Vairamuthu

    Sub send_Email_complete()
    Dim MyOutlook As Object
    Dim MyMail As Object

    Dim i As Integer
    Dim vairam As Worksheet
    Dim last_row As Integer
    Dim Col As Integer

    Set MyOutlook = CreateObject(“Outlook.Application”)
    Set MyMail = MyOutlook.CreateItem(olMailItem)

    Set vairam = ThisWorkbook.Sheets(“Mailing_Details”)
    last_row = Application.WorksheetFunction.CountA(vairam.Range(“A:A”))

    i = 3

    If i <= last_row Then

    MyMail.To = vairam.Range("a" & i).Value
    MyMail.Cc = vairam.Range("b" & i).Value
    MyMail.Bcc = vairam.Range("c" & i).Value
    MyMail.Subject = vairam.Range("d" & i).Value
    MyMail.Body = vairam.Range("e" & i).Value

    For Col = 6 To 15
    MyMail.attachments.Add vairam.Cells(i, Col).Value
    Next Col

    i = i + 1

    MyMail.send

    MsgBox "Mail Sent to :-" & vairam.Range("a" & i).Value & "cc:-" & vairam.Range("b" & i).Value & "Bcc:-" & vairam.Range("b" & i).Value, vbInformation, "Mails Sent Successfully"
    End If
    End Sub

    Sub Add_Attachment()
    Dim File_Picker As FileDialog
    Dim Attachment As String
    Set File_Picker = Application.FileDialog(msoFileDialogFilePicker)
    File_Picker.Title = "Select a Attachments Maximum upto 10 Files" & Filetype
    File_Picker.Filters.Clear
    File_Picker.Show

    i = Selection.Row
    j = 6
    For n = 1 To File_Picker.SelectedItems.count

    If n <= File_Picker.SelectedItems.count Then
    Attachment = File_Picker.SelectedItems(n)

    Cells(i, j).Select
    Selection.Value = """" & Attachment & """"
    j = j + 1
    End If
    Next n
    End Sub