VBA - Email Error '-2147467259 (80004005)': Method 'MailEnvelope' of object '_Worksheet' failed

[Animalised]

Hello Everyone,

I'm very new to VBA but i have written some code to send an email when someone clicks a button.

The code works fine if you only want to click one button, but if you want to click other buttons while in the same session, it will show the following error message:

'-2147467259 (80004005)': Method 'MailEnvelope' of object '_Worksheet' failed


The code is as follows:

Private Sub RoyTattersfield_Click()        Sheets("Roy Tattersfield").Select
        ActiveSheet.Unprotect "TIRCarnet"
   ' Select the range of cells on the active worksheet.
   Sheets("Roy Tattersfield").Range("A1:H30").Select
   
   ' Show the envelope on the ActiveWorkbook.
   ActiveWorkbook.EnvelopeVisible = False
   
   ' Set the optional introduction field thats adds
   ' some header text to the email body. It also sets
   ' the To and Subject lines. Finally the message
   ' is sent.
   With ActiveSheet.MailEnvelope
      .Introduction = "Hi Tom," & vbNewLine & vbNewLine & _
        "Below is an detailed extract of Roy Tattersfield's spending."
      .Item.To = "Example@Email.com"
      .Item.CC = ""
      .Item.Subject = "Roy Tattersfield - Overspending"
      .Item.Send
   End With
        Sheets("Roy Tattersfield").Select
        ActiveSheet.Protect "TIRCarnet"
    Sheets("Summary").Select
    Cells(11, "D").Value = "Notified"
    Range("D11").Interior.Color = RGB(0, 0, 255)
    Range("D11").Font.Color = RGB(255, 255, 0)
    Range("D11").Font.Bold = True
End Sub


Private Sub SteveAuty_Click()
        Sheets("Steve Auty").Select
        ActiveSheet.Unprotect "TIRCarnet"
   ' Select the range of cells on the active worksheet.
   Sheets("Steve Auty").Range("A1:H30").Select
   
   ' Show the envelope on the ActiveWorkbook.
   ActiveWorkbook.EnvelopeVisible = False
   
   ' Set the optional introduction field thats adds
   ' some header text to the email body. It also sets
   ' the To and Subject lines. Finally the message
   ' is sent.
   With ActiveSheet.MailEnvelope
      .Introduction = "Hi Tom," & vbNewLine & vbNewLine & _
        "Below is an detailed extract of Steve Auty's spending."
      .Item.To = "Example@Email.com"
      .Item.CC = ""
      .Item.Subject = "Steve Auty - Overspending"
      .Item.Send
   End With
        Sheets("Steve Auty").Select
        ActiveSheet.Protect "TIRCarnet"
    Sheets("Summary").Select
    Cells(6, "H").Value = "Notified"
    Range("H6").Interior.Color = RGB(0, 0, 255)
    Range("H6").Font.Color = RGB(255, 255, 0)
    Range("H6").Font.Bold = True
End Sub

(As demonstrated in the code, i repeat the exact same code across all members of staff).


I want to be able to fix this, but i cant find a solution to this online.

Any help would be very appreciated!

Thank you,

Harley.

 

[gallen]

The code works fine if you only want to click one button

So you need to stop users doing anything while the code is executing?

Could you disable the buttons, run your code, then enable them again? Would that solve it?

 

[Animalised]

Hi Gallen,

I did not explain too well.

The code will execute in the first instance (first instance = click the button to run the macro; the macro will unlock the sheet; the macro opens up the email after copying the information and pastes it into the email; the email sends and the sheet is updated with the "Notified" cell turning blue (0, 0, 255); the sheet is then locked).

If i was to then click another button to send an email (regarding another person), the error message appears.
[h=2]'-2147467259 (80004005)': Method 'MailEnvelope' of object '_Worksheet' failed[/h]
Thank you,
Harley.

 

[gallen]

Can you highlight which line exactly does it? I'm assuming its this:

 ActiveWorkbook.EnvelopeVisible = False

 

[haripuni]

I am also facing the same issue.

Here is the code

Function mail_envlop()


Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")
Dim lr As Integer


lr = sh.Range("B" & Application.Rows.Count).End(xlUp).Row

sh.Range("A1:H" & lr).Select

With Selection.Parent.MailEnvelope.Item
.to = sh.Range("L6").Value
.cc = sh.Range("L7").Value
.Subject = sh.Range("L8").Value
.send
End With

End Function

Getting the same error error in code "With Selection.Parent.MailEnvelope.Item"

 

[haripuni]

Some additional input , the above code is working when i execute for the first time and getting the error when executing for the second time.To avoid this error for each execution i need to open and close the file.

 

[thewoz]

I found a fix for this error but would prefer another option due to time it takes.
If you add this line before the code to send the email it works everytime

ActiveWorkbook.Save

Does anyone know anything else that will work?