Send Email With PDF Attachment from Excel

This week, I was experimenting with sending email from Excel via Outlook. The goal was to send an email to each name in a list, and attach a couple of PDF files.

So, before creating my complex email code, I set up a little test file, with a short list of fake customers. I created this file using Outlook and Excel 2013, but it should also work in Excel 2010, and perhaps Excel 2007 (see Ron de Bruin’s article for 2007 requirements).

emailwithpdf01

Another sheet in the file has a report, to send to each customer, as a PDF attachment. I like pens, so my imaginary business is a pen shop.

The macro puts the current store number in cell E8, and INDEX/MATCH formulas pull the name and sales amount for that store.

emailwithpdf02

Set Up the Email Settings Sheet

When doing the email tests, I wanted to be able to change some of the settings, without going into the VBA code. So, I added a Settings sheet, where you can:

  • enter text for the subject line and email body
  • select a folder where a copy of each PDF file will be saved
  • enter an email address for receiving test emails

emailwithpdf03

Send the Emails

The final sheet is the Menu, with buttons that run the macros.

emailwithpdf04

If you’re sending out an email message to a list, it’s a good idea to test it first, by sending the reports to your own email address, instead of using your customers as guinea pigs!

So, click that “Send Test Emails” button, and see what arrives in your inbox. Check the PDF attachments, to make sure that the information is correct, and that each person is receiving the right attachment.

Then, if everything works well, you’re ready to click the “Send Store Emails” button, to send out the actual email.

More Excel Email Tips

For lots more examples of sending email from Excel, please visit Ron de Bruin’s website. He also shows how to use other mail programs, and different types of attachments.

Download the Sample File

To test the email code, you can download the sample file from my Contextures website. Go to the Sample files page, and in the VBA section, look for UF0023 – Send Email With PDF Attachment

And be sure to test carefully, before you send a big email to your customers and co-workers!

_________________

You may also like...

23 Responses

  1. Dave Roberts says:

    The Olde Pen Shoppe? As long as you’re skewing olde, shouldn’t that be Ye Olde Pen Shoppe? (Ye versus The)

  2. Dave Roberts says:

    And really should be Ye Olde Quille Shoppe.
    Sorry. I’ll stop now.

  3. billr says:

    Dave: funny. You can keep going at Deb’s expense… of course. :)
    //b

  4. billr says:

    Might be helpful to note the above emailing from excel project is set to work from Outlook, which I stop using years ago.

  5. Simon Hughes says:

    I have deleted three names from the store list (with their fake emails) and inserted my own email address in the remaining one and put my own email address in the test email. However, I am receiving four emails when I send the test but just the one when sending store email. what needs to be done to fix this?

    • @Simon, the StoreNums range is static, so it didn’t adjust automatically if you just cleared the cells, instead of deleting the rows.

      The test email uses your test address for each email, so those would create 4 valid messages.

      The store email takes the address from each row, and since only one row has an address, it can only send one email.

  6. Simon Hughes says:

    Hi Debra, got it! Deleted the rows from the range and as you say – 1 email sent. This is a very clever trick and one I shall use after appropriate modifications. Many thanks.

  7. Simon Hughes says:

    Hi Debra, when sending test email I have no problems but when I send store emails, then although the dialog box comes up with “9 emails will be sent” and then the second dialog box “emails have been sent” shows, no emails actually get sent. Can you help solve this?

  8. Simon Hughes says:

    Yes, the name manager includes all 10 names in the range and the email addresses are valid. strangely, when I send Store Emails, it only says none emails will be sent. I have tried to check my sheet against the original that I have downloaded and cannot see any differences in the basic architecture.

  9. papsajt says:

    Exelent VBA. I want to give one more box in the mail settings

    “Enter the attachment file name”

    I named a cell “rngfilename”

    I changed the line
    – strPDFName = “rngfilename” & c.Value & “.pdf”

    but it is not works

    What I have to change int the sendemailstores VBA sript?

  10. Ijlal Ashraf says:

    Dear papsajt, the modified code you require is as follows:

    strPDFName = wsS.Range(“rngfilename”).Value & c.Value & “.pdf”

  11. J Law says:

    no matter how many emails i put in the storelist tab I only get 4 emails

  12. tonycel says:

    I noticed there is no way to copy data into the StoreList sheet. I had to insert 3200 rows then I was able to copy the info. I’m I missing something?

  13. Vijeth says:

    Hello,

    How do i modify the same to send a file which is already in the folder ?

    I want this to send the emails i have mentioned,but it should send the pdf which are in the folder ?

    Please help .

  14. Katy says:

    This code is amazing! I love all the stops put into place with error message explanations. it helps this newbie troubleshoot! I have one major request…Can you make it loop through a data validation list as opposed to a named range on a secondary sheet? My data validation list lives in one cell on my main spreadsheet I want to export as PDF and send. I have named this cell “FacilityName” in name manager.

    • Katy says:

      Set Fac = sh.Range(“FacilityName”)

      For Each c In Fac
      strMsg = “Could not create PDF for ” & Fac.Value

      strPDFName = Fac.Value & “.pdf”

      If bTest = False Then
      ‘False = quarterly emails
      strSendTo = rngSendTo
      strSendTocc = rngSendTocc
      End If

      sh.ExportAsFixedFormat _
      Type:=xlTypePDF, _
      Filename:=strSavePath & strPDFName, _
      Quality:=xlQualityStandard, _
      IncludeDocProperties:=True, _
      IgnorePrintAreas:=False, _
      OpenAfterPublish:=False

      Set OutMail = OutApp.CreateItem(0)

      strMsg = “Could not start mail process for ” & Fac.Value
      On Error Resume Next
      With OutMail
      .to = strSendTo
      .cc = strSendTocc
      .BCC = “”
      .Subject = strSubj
      .Body = strbody
      .Attachments.Add strSavePath & strPDFName
      .Send
      End With
      On Error GoTo 0

      Next c

    • Thanks, Katy! I’m not clear on how your data validation is set up. What do you mean by “lives in one cell”?
      Did you type a list right in the data validation dialog box, such as “One, Two, Three”

  15. viraj says:

    i am only able to send 4 mails ,what i have to update macro to send more than 4 mails , i have updated new rows but still its sending 4 mails please help with this

  16. Voon says:

    This code is very helpful.
    However, i am only able to send 4 emails.
    Besides that i would like to send the attachment file name like” Sales report – Store Number – Store Name – Sep17″
    Could you please help with these. Thanks in advance

Leave a Reply to Katy Cancel reply

Your email address will not be published. Required fields are marked *