Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Results 1 to 3 of 3

Thread: Send Email On VBA Macro Error

  1. #1
    Join Date
    29th August 2008

    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.

    Excel Video Tutorials / Excel Dashboards Reports

  2. #2
    Join Date
    24th January 2003

    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. #3
    Join Date
    29th August 2008

    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.

    Excel Video Tutorials / Excel Dashboards Reports

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Send Email To Email Address Specified In Cell
    By ayounis in forum Excel and/or Email Help
    Replies: 5
    Last Post: June 6th, 2008, 20:06
  2. Send Email With Macro
    By Michal in forum Excel and/or Email Help
    Replies: 1
    Last Post: November 20th, 2007, 10:34
  3. Send Email With Macro
    By jstevens in forum Excel and/or Email Help
    Replies: 12
    Last Post: July 13th, 2007, 01:20
  4. Send Email & Write to Email Body
    By jillyb in forum Excel General
    Replies: 2
    Last Post: November 11th, 2006, 04:21
  5. Replies: 1
    Last Post: February 13th, 2004, 09:50


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts