Excel Macro : How to get notification when a VBA script stops execution

I am running a macro on a remote PC which executes every 5 seconds throughout the day.

Now I want th开发者_如何学运维at if something goes wrong and the macro stops execution I should be informed or notified via an Email.

How can I do that?

Error handling.

Sub MySub()
    On Error GoTo ErrorHandler

    ' Work done here...

    ' Screen for an expected error 
    If somethingWentWrong = True Then
        Err.Raise Number:=myErrorNumber, Source:="MySub", _
            Description:="This thing went wrong."
        ' Will now go to ErrorHandler
    End If

    ' More work done here...        

ExitProcedure:
    On Error Resume Next
    'Cleanup code goes here...
Exit Sub
ErrorHandler:
    ' If an error occurs (anticipated or not), the following will be executed.
    Call SendEmailNotification( _
        Recipient:="you@there.com", _
        Subject:="Something went wrong.", _
        Message:=Err.Number & vbCrLf & Err.Description & vbCrLf & Err.Source)
    ' Any other error handling goes here...
    Resume ExitProcedure
End Sub

To send an e-mail, there are various solutions out there. Search for CDO, MAPI, Sockmail. Searching on those will give you examples for how to write your SendEmailNotification sub. Heads up: None of these are exactly straightforward.

You can add a mail send functionality within your error handling. If the machine the macro is being executed has Outlook properly set, I believe you can use ActiveWorkbook.SendMail.

If this PC doesn't have the Outlook set, you'll need to find out for a mail solution that fits your environment.

It would then looks like:

Sub MySub()

    On Error GoTo ProcError

    'Your stuff


ProcExit:

    Exit Sub

ProcError:

    ActiveWorkbook.SendMail "your.mail@yourdomain.com", "Application failed!"
    Resume ProcExit

End Sub