CODEWITHSUNDEEP
vbaexceloutlook
Selkie
Selkie

Reputation: 1255

Struggling to attach files to Outlook e-mails using Excel VBA

I'm trying something fairly simple, and I can't tell how what I'm doing is different from how other code that's written is doing it.

I have a very simple send e-mails from Excel using Outlook:

Sub SendEmail()

    Dim OutApp As Object
    Dim OutMail As Object


    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    With OutMail
        .To = Range("ETF_CAB_Recon_Initial_Email_To")
        .CC = Range("ETF_CAB_Recon_Initial_Email_CC")
        .BCC = ""
        .Subject = Range("ETF_CAB_Recon_Initial_Email_Subject")
        .HTMLBody = Range("ETF_CAB_Recon_Initial_Email_Body")
        .Attachments.Add Range("ETF_CAB_Recon_Initial_Email_Attachment")
        .Display  
    End With


    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

However, it gets grumpy at my file attachment.

Error message: "Object doesn't support this property or method" Doing some research, that's apparently because .add isn't a part of Attachments? But then why do so many examples have Attachments.Add as their code for attaching files to Outlook e-mails?

I've made sure that the Outlook object library is on, and the rest of the email fills in nicely - just not the attachment. Debugging is also showing the attachment as the issue.

I've tried a few different ways of defining the location.

Any guidance towards a solution would be appreciated.

Upvotes: 1

Views: 1552

Answers (2)

0m3r
0m3r

Reputation: 12495

Try to fully qualify your workbook and assign string variable to your range

See example 

Option Explicit
Sub SendEmail()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim Sht As Object
    Dim RecipTo As String
    Dim RecipCC As String
    Dim Subject As String
    Dim Atmt As String

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    Set Sht = ThisWorkbook.Worksheets("Sheet1")

    With Sht
        RecipTo = Sht.Range("A1").Value
        RecipCC = Sht.Range("B1").Value
        Subject = Sht.Range("C1").Value
        Atmt = Sht.Range("D1").Value ' Attachment Path

        With OutMail
            .To = RecipTo
            .CC = RecipCC
            .BCC = ""
            .Subject = Subject
            .HTMLBody = Sht.Range("E1")
            .Attachments.Add Atmt
            .Display
        End With

    End With

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

See another example here https://stackoverflow.com/a/38303646/4539709

Attachments Object (Outlook)

Upvotes: 1

Dmitry Streblechenko
Dmitry Streblechenko

Reputation: 66341

Attachments.Add only takes either a string (file name) or an instance of one of Outlook items (such as MailItem). You are passing a Range object.

Upvotes: 2

Related Questions