How can I display a MsgBox when the email is sent? Excel VBA

Question

I have write a code to send email with outlook via excel. But I want to add a MsgBox when clicking to the "Send" Button that says "Email sent Successfully". But it doesn't work. Can I have some help

I tried to create a variable "Dim IsSent As Boolean" and set it at False at the beginning and then set it to True when it's sent. But it doesn't work. Here is my code :

Sub subMail_Sheet_Outlook_Body()
    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object
    Dim IsSent As Boolean

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    Set IsSent = False
    Set rng = Nothing
    Set rng = ActiveSheet.UsedRange
    'You can also use a sheet name
    'Set rng = Sheets("YourSheet").UsedRange

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

    On Error Resume Next
    With OutMail
        .To = emailform.emailEnter.Value
        .CC = ""
        .BCC = ""
        .Subject = emailform.emailSubject.Value
        .HTMLBody = "Here" & RangetoHTML(rng)
        .Send  'or use .Display


    End With


    On Error GoTo 0

    With Application
        .EnableEvents = True
        .ScreenUpdating = True

        Set IsSent = True

        If IsSent = True Then
        MsgBox "Succes"
        Else
        MsgBox "Noooon"
        End If

    End With

    Set OutMail = Nothing
    Set OutApp = Nothing


End Sub

With this code I expect "Success" if the mail is sent and "not success" if it isn't but instead I have an error that says:

Compile Error: Object required

Remove the Set from Set IsSent = True. Set is for object variables. — BigBen

Dorian Dorian · Accepted Answer · 2019-09-26T13:36:39

I think you can use a simple On error GoTo and avoid this random boolean condition

Sub subMail_Sheet_Outlook_Body()

On Error GoTo IFErrorMail

    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With


    Set rng = Nothing
    Set rng = ActiveSheet.UsedRange
    'You can also use a sheet name
    'Set rng = Sheets("YourSheet").UsedRange

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


    With OutMail
        .To = emailform.emailEnter.Value
        .CC = ""
        .BCC = ""
        .Subject = emailform.emailSubject.Value
        .HTMLBody = "Here" & RangetoHTML(rng)
        .Send  'or use .Display


    End With


    With Application
        .EnableEvents = True
        .ScreenUpdating = True

    End With

MsgBox "Success"
If (Not (OutMail Is Nothing)) Then Set OutMail = Nothing
If (Not (OutApp Is Nothing)) Then Set OutApp = Nothing

Exit Sub

IFErrorMail:
If (Not (OutMail Is Nothing)) Then Set OutMail = Nothing
If (Not (OutApp Is Nothing)) Then Set OutApp = Nothing
MsgBox "Error Mail could not be sent"    
End Sub

How can I display a MsgBox when the email is sent? Excel VBA

3 Answers