Send Notification via Outlook E-mail when an Error Occurs in Your Application

Category:

Office/VBA

Type:

Snippets

Difficulty:

Intermediate

Author:

Stanley Campbell

Version Compatibility:

More information:

This Routine sends the specified recipient an e-mail via Outlook when an error occurs in a procedure. Outlook must be installed on the machine for the procedure to work

Here is an example of how to use the procedure:

Private Sub Form_Load()

On Error GoTo errHandler
Dim i As Integer
i = 1 / 0
Exit Sub

errHandler:
If err.Number > 0 Then
ErrorNotify err, "me@mydomain.com", "Form.Load"
End If

End Sub

Instructions: Copy the declarations and code below and paste directly into your VB project.

Declarations:
' (None)

Code:

Public Sub ErrorNotify(err As ErrObject, Recipient As String, _
Optional ProcedureName As String)

Dim sSubject As String
Dim sMsg As String
Dim sErrMsg As String

sSubject = App.Title & " Error Message "

sErrMsg = "An error occurred in " & App.Title & vbCrLf
   If ProcedureName <> "" Then sErrMsg _
        = sErrMsg & "Procdure: " & ProcedureName & vbCrLf

sErrMsg = sErrMsg & "Error Number " & CStr(err.Number) _
     & " was generated by " &  err.Source & Chr$(13) & _
      err.Description
   
   SendOutlookMail sSubject, Recipient, sErrMsg

End Sub

Public Sub SendOutlookMail(Subject As String, Recipient As _
String, Message As String)

On Error GoTo errorHandler
Dim oLapp As Object
Dim oItem As Object

Set oLapp = CreateObject("Outlook.application")
Set oItem = oLapp.createitem(0)
'
With oItem
   .Subject = Subject
   .To = Recipient
   .body = Message
   .Send
End With
'
Set oLapp = Nothing
Set oItem = Nothing
'

Exit Sub

errorHandler:
Set oLapp = Nothing
Set oItem = Nothing
Exit Sub
End Sub