VBA failing to add attachment to email

Question

Not sure why this is failing with a

"runtime error operation failed"

It seems to be crashing on the ".Attachments.Add fileName" line. I've read that you can run into issues if you are passing an object to the attachments, but I don't think I've done that.

Public Sub DraftEmailWithAttachment(strTo As String, strSubject As String, _
                                    strBody As String, fileName As String)

    Dim oApp As Object
    Dim oEmail As Object

    Set oApp = CreateObject("Outlook.Application")
    Set oEmail = oApp.CreateItem(0)

    With oEmail
        .To = strTo
        .subject = strSubject
        .Attachments.Add fileName
        .display
        .HTMLBody = strBody & oEmail.HTMLBody
    End With

End Sub

and

Private Sub btnEmailActionItems_Click()

    Dim fileName As String
    Dim todayDate As String
    Dim strTo As String
    Dim strSubject As String
    Dim strBody As String
    Dim filter As String

    Dim oApp As Object
    Dim oEmail As Object

    Set oApp = CreateObject("Outlook.Application")
    Set oEmail = oApp.CreateItem(0)

    strTo = Nz(Me.cboUnderwriter.Column(2), "")
    strSubject = Nz(Me.txtNamedInsured.Value, "") & " - " & _
                 Nz(Me.txtSubmissionNumber.Value, "") & " - " & _
                 Nz(Me.txtQuoteNumber.Value, "")

    strBody = "Hello " & Me.cboUnderwriter.Column(3) & ", <br/><br/>"
    todayDate = Format(Date, "MM.DD.YYYY")
    fileName = "C:\Users\crewsj3\Desktop\tmp\Action Items Report -" & _
                strSubject & " " & todayDate & ".pdf\"

    filter = "submission_number=" & Nz(Me.txtSubmissionNumber.Value, "")

    'generate filtered report
    Call ExportFilteredReportToPDF("rptActionItemsForAllPolicies", fileName, filter)

    'generate email
    Call DraftEmailWithAttachment(strTo, strSubject, strBody, fileName)

End Sub

Any ideas?

Edit:

It looks like the problem was the trailing slash. works fine now. Thanks for the help.

@TimWilliams, I read somewhere that the attachments object is very particular, and won't work unless you have the trailing slash. I'll give it a try tomorrow without it, but I think I tried removing it, and it didn't make a difference. — the1gofer
– the1gofer, Commented Oct 9, 2019 at 5:50
I’ve never used a trailing slash and I’ve not had any problems with attaching files — Tim Williams
– Tim Williams, Commented Oct 9, 2019 at 5:56
@TimWilliams that fixed it! Gotta love programming. Thanks for the help. — the1gofer
– the1gofer, Commented Oct 9, 2019 at 14:50

Eugene Astafiev · Accepted Answer · 2019-10-09 11:48:59Z

1

The source of the attachment can be a file (represented by the full file system path with a file name) or an Outlook item that constitutes the attachment. See Attachments.Add for more information.

Based on the code listed above you just need to make sure the file path is valid and doesn't contain forbidden symbols. Try to copy the actual file path at runtime and paste it into any windows explorer window. Following that way, you can be sure that a file can be found and read.

answered Oct 9, 2019 at 11:48

Eugene Astafiev

49.8k3 gold badges28 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA failing to add attachment to email

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related