No announcement yet.

Send Email On VBA Macro Error

  • Filter
  • Time
  • Show
Clear All
new posts

  • Send Email On VBA Macro Error

    Is it possible to send an email if an error occurs with all the information regarding that error?

    I've created a workbook with a lot of macros and a lot of information. It's used by several people and sometimes those people have several other excel workbooks open and running at the same time. I've done my best to fix all the bugs but should one come up I'd like to be able to have the workbook send me an email alerting me of the error and the details surrounding the error such as what the error was, what macro was running, ranges selected, all that useful info. Is this even possible? or is there some sort of work around to at least bring up a prompt or page for the user to fill out then send by email after the error occurred?

    Any insight or suggestions regarding this are greatly appreciated.

  • #2
    Re: Send Email On Error

    Try along the lines of below to send an email on error;

    Sub MyMacro()
    On Error GoTo ErrorHandler
    Exit Sub
    Call Emailerr(ActiveSheet.Name, Selection.Address, "MyMacro", Err.Number, Err.Description, Application.UserName, Now)
    End Sub
    Sub Emailerr(StrSheetName, StrSelection, StrMacroName, StrErrNum As String, StrErrText, StrUser As String, DateTime As String)
    With ThisWorkbook
           .HasRoutingSlip = True
                With .RoutingSlip
                        .Delivery = xlOneAfterAnother
                        .Recipients = Array("[email protected]", _
                                        "[email protected]", "[email protected]")
                        .Subject = Err.Source & " " & StrMacroName
                        .Message = StrSheetName & StrSelection & vbNewLine & _
                            StrErrNum & vbNewLine & _
                            StrErrText & vbNewLine & _
                            StrUser & vbNewLine & _
                End With
        End With
    End Sub
    Send Email From Excel AND Example Code for sending mail from Excel


    • #3
      Re: Send Email On VBA Macro Error

      Is there anyway to set this to happen automatically? Or do I have to put this in Every Sub? It would be great if I could just add this to the way Excel handles errors so that I can create a single sub that it draws from every time and then allow the User to continue on.

      Is it possible to set this up so when the "On Error GoTo 0" is Set it will go this regardless of which Sub the Error occurs from?


      I assume when the "On Error Resume Next" or "On Error GoTo [WhereEver]" are set that they will follow those instead.