Geek asks and answers

Automatically send email when VBA code gives error

I am writing a VBA macro that should be used by others who will not be VBA users. Therefore, I would like to embed the system in code that, when the code causes an error, automatically sends me an email from the Outlook user account. Is this possible with VBA? In addition, the user will not have administrator access to his account, will this create a problem? Thanks in advance for your help!

EDIT - now I know that this is possible, as well as the vba code for it (see below). However, we can eliminate the “Security Warning Window” that appears when we try to send an email automatically. In addition, I would like to add an erroneous file along with the email. It would be great if I helped this, thanks!

+3
source share
1 answer

Try it. UNTESTED

Option Explicit

Sub Sample()
    On Error GoTo Whoa

    '
    '~~> Rest of the Code
    '

    Exit Sub
 Whoa:
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    With OutMail
        .To = "abc@abc.com"
        .Subject = "Error Occured - Error Number " & Err.Number
        .Body = Err.Description

        .Display '~~> Change this to .Send for sending the email
    End With

    Set OutApp = Nothing: Set OutMail = Nothing
End Sub

Followup

Is there a way I can attach an excel file with a macro? I will also edit the main question to reflect this. - hardikudeshi 5 minutes ago

Try it.

Option Explicit

Private Declare Function GetTempPath _
Lib "kernel32" Alias "GetTempPathA" _
(ByVal nBufferLength As Long, _
ByVal lpBuffer As String) As Long

Private Const MAX_PATH As Long = 260

Sub Sample()
    Dim OutApp As Object, OutMail As Object
    Dim wb As Workbook

    On Error GoTo Whoa

    '
    '~~> Rest of the Code
    '

    Exit Sub
 Whoa:
    Set wb = ThisWorkbook

    Application.DisplayAlerts = False
    wb.SaveAs TempPath & "ErroringFile.xls", FileFormat:= _
    xlNormal
    Application.DisplayAlerts = True

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    With OutMail
        .To = "abc@abc.com"
        .Subject = "Error Occured - Error Number " & Err.Number
        .Body = Err.Description
        .Attachments.Add TempPath & "ErroringFile.xls"

        .Display '~~> Chnage this to .Send for sending the email
    End With

    Set OutApp = Nothing: Set OutMail = Nothing
End Sub

Function TempPath() As String
    TempPath = String$(MAX_PATH, Chr$(0))
    GetTempPath MAX_PATH, TempPath
    TempPath = Replace(TempPath, Chr$(0), "")
End Function
+3
source

More articles:


All Articles