How to add an attachment to an email using VBA in Excel

by

Try this:

Sub emailtest()

Dim objOutlook As Object
Dim objMail As Object
Dim rngTo As Range
Dim rngSubject As Range
Dim rngBody As Range
Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.CreateItem(0)

With ActiveSheet
Set rngTo = .Range("E2")
Set rngSubject = .Range("E3")
Set rngBody = .Range("E4")
End With

With objMail
.To = rngTo.Value
.Subject = rngSubject.Value
.Body = rngBody.Value
.Attachments.Add "Z:\PHS 340B\Letters of Non-Compliance\..Resources\W9 Form\VPNA W-9 01 09 2017"
.Display 'Instead of .Display, you can use .Send to send the email _
            or .Save to save a copy in the drafts folder
End With

Set objOutlook = Nothing
Set objMail = Nothing
Set rngTo = Nothing
Set rngSubject = Nothing
Set rngBody = Nothing

End Sub

You need to use the .Attachments.Add when working within Outlook not Excel.

More Related Contents:

  1. How to copy Outlook mail message into excel using VBA or Macros
  2. Copy Excel range as Picture to Outlook
  3. Paste Excel range in Outlook
  4. Update excel sheet based on outlook mail [closed]
  5. Sending mail using Outlook where the Send method fails
  6. Email a single attachment from folder of files each to a different person
  7. How to send mail when the .Send does not work?
  8. How to send email to multiple recipients with addresses stored in Excel?
  9. Type Mismatch in Loop to scan Outlook Messages [duplicate]
  10. Finding a workbook in one of multiple Excel instances
  11. Convert Early Binding VBA to Late Binding VBA : Excel to Outlook Contacts
  12. Change HTML email body font type and size in VBA
  13. How to Send Email When Computer is Locked?
  14. For Loop not fully cycling in excel VBA [duplicate]
  15. Unique Random Numbers using VBA
  16. Populate unique values into a VBA array from Excel
  17. Closing Excel Application using VBA
  18. Delete Row based on Search Key VBA
  19. How to add a button programmatically in VBA next to some sheet cell data?
  20. What are the benefits of setting objects to “Nothing”
  21. How can I kill task manager processes through VBA code?
  22. VBA: Usage of parentheses for a method
  23. Get Unicode characters with charcode values greater hex `FFFF`
  24. Copy a selected range to another worksheet
  25. Excel macro – paste only non empty cells from one sheet to another
  26. The MacScript function is not working well in Office for Mac 2016! Any ideas?
  27. Pointers needed for speeding up nested loop macro in VBA
  28. ‘1004’: “The sort reference is not valid.”
  29. Create hyperlink to another sheet
  30. Delete all data rows from an Excel table (apart from the first)

Leave a Comment