(Solved) – Excel VBA Error – Run-time error -2147467259 (80004005) Method ‘MailEnvelope” of object _Worksheet failed

(solved)-–-excel-vba-error-–-run-time-error-2147467259-(80004005)-method-‘mailenvelope”-of-object-worksheet-failed

I am using the below VBA which copies a range of cells into the body of an email, attaches the whole workbook, and then sends.

This first time I run the macro it works fine. If I try to run it again I get the above error. Saving the work book (without closing) “resets” the issue and I can use the macro again perfectly; opening/closing has the same positive effect.

I cannot see any open processes in process manager.

Any suggestions on how to diagnose the issue? I’ve tried searching various forums and can see people who have had the same issue but can’t find a fix.

Option Explicit

Sub Mail()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Mail Form")

sh.Range("B11:J22").Select

With Selection.Parent.MailEnvelope.Item
    .to = "email@gmail.com"
     .Subject = "Subject Line: " & Range("D5").Value
    .Attachments.Add (ThisWorkbook.FullName)
    .send
End With

MsgBox "Your request has been submitted"

End Sub

Leave a Reply

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