2

I send an Excel worksheet as an email attachment through Outlook (from Excel).

Strongly simplified code:

Private Sub SendWorksheetByEmail(sEmail As String)

    'This error handler should make sure that the code always
    'goes through a "clean up" procedure where
    'all settings are reset and temporary files are deleted
    On Error GoTo ErrorHandler

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    ActiveSheet.DisplayPageBreaks = False

    Dim sFile As String
    Dim wbCopy As Workbook
    Dim OutlookApp As Object, OutlookMail As Object
    '.......some more declarations.................

    'Here comes code that will do some changes in the workbook,
    'then copy one of the sheets to a new workbook (wbCopy) and
    'store the workbook to the temp folder. I guess it is not
    'neccessary in order to understand the question.
    '..............................................
    '..............................................


    'Sending the email
    'I somethines see that people put a "On Error Resume Next" here.
    'Then, the code will always finish - but I do not get an error
    'Message. What is the point of doing this?

    Set OutlookApp = CreateObject("Outlook.Application")
    Set OutlookMail = OutlookApp.CreateItem(0)
    With OutlookMail
        .To = sEmail
        .CC = ""
        .BCC = ""
        .Subject = "Some subject text"
        .Body = "Some email body text"
        .Attachments.Add sFile
        .Send
    End With

    MsgBox "Your email was successfully sent to " & sEmail, vbInformation, "Email sent"

ErrorExit:
    'Various "clean up" (delete temporary file) and restore settings
    On Error Resume Next
    wbCopy.Close
    Kill sFile
    Set OutlookMail = Nothing
    Set OutlookApp = Nothing
    ActiveSheet.DisplayPageBreaks = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True

    Exit Sub

ErrorHandler:
    MsgBox "An error occured and the email might not have been sent.", vbCritical, "Error"
    Resume ErrorExit

End Sub

The idea is that the code will skip to the ErrorHandler (and clean up some settings, delete a temporary file and so on) once an error occurs.

That works if the error occurs before sending the email. However, if something goes wrong sending the email, the code will simply stop.

An example: I tested the code on a VM where I do not have any email account set up in Outlook, so Outlook will start and prompt me to set up an account. If I close Outlook, the macro won’t continue to run.

Outlook is not set up and asks to create a user account

I did some web research and see that the code that sends the email is often run with an On Error Resume Next statement. I tried that and the code finished running this time. However, I do not manage to check if the email was sent – so the user gets a confirmation message box even if the email could not be sent.

How do I make sure the code always finishes? And how could I check if the email was actually sent (in order to display an error message)?

Community
  • 1
  • 1
Michael Wycisk
  • 1,590
  • 10
  • 24
  • 1
    The `.Send` call **is** located in a place where any error would jump execution to the `ErrorHandler` label. Are you saying `.Send` is throwing an error *that is unhandled*? Despite the handler? Verify your VBE options, make sure "Break on **unhandled** errors" is selected. – Mathieu Guindon Dec 11 '19 at 17:13
  • 1
    Thank you @MathieuGuindon for the idea. I just checked it and "Break on unhandled errors" is selected. To clarify, the code is not throwing an error. The Outlook window appears and after I click on "Cancel", nothing happens. The code simply stops an no error is highlighted in the VBE (no error message or "success"-message from the code either). It is really weird, I worked with VBA for quite some years, but such thing has not happened to me before. – Michael Wycisk Dec 11 '19 at 17:25
  • 1
    I think there should be a way to validate whether there's a connected account in `ThisOutlookSession` before attempting to send an email. Not very familiar with the Outlook OM though :-/ – Mathieu Guindon Dec 11 '19 at 17:28
  • @MathieuGuindon Thanks. I am not really programming towards Outlook either - but I will search the web if I can find something. My concern is just that - even if I can check for an Outlook account - there might be other reason that prevent the message from being sent (Internet connection, problems with the email provider, and so on). So I would like to hear with the experts if there is anything I can improve in my error handling so any Outlook related error will be catched :-). – Michael Wycisk Dec 11 '19 at 17:32
  • 1
    I believe most problems that could pop up trying to send an email would actually throw a run-time error that your error handling code would handle. Thinking you've hit an edge case (who uses an Outlook macro without a connected account?); verifying that there's a valid, connected account would remove the edge case from the equation (assuming there aren't more surprises!) – Mathieu Guindon Dec 11 '19 at 17:34
  • 3
    @MathieuGuindon Thank you so much for your help! I have been looking around and found this [answer](https://stackoverflow.com/a/32378713/12418842). I have setup up an extra handler and use `If OutlookApp.Session.Accounts.Count = 0 Then GoTo OutlookNotSetup` (right after `Set OutlookApp = CreateObject("Outlook.Application")` to check if an Outlook account is configured or not. This one opens the "Welcome" dialog, but interestingly, the code does not stop. So I can handle the problem now. Thank you again for your advice. It's a pity I can't pass some reputation to you by upvoting your comments. – Michael Wycisk Dec 12 '19 at 00:12
  • 1
    @MathieuGuindon Too late - I already have and use Rubberduck :-). – Michael Wycisk Dec 12 '19 at 00:26
  • 1
    And that has just made my day lol! Cheers! – Mathieu Guindon Dec 12 '19 at 00:27

1 Answers1

0

If you would like to be totally sure that your email has been sent then maybe it would be good idea to check if your message is inside "Sent" or/and "Outbox" folder (see here). If vba will crash there, then you will know that there can be an error with Outlook app. If you are sending hundreds of emails at once then have an eye on "Outbox" folder. Emails are stored there and sent one by one even after macro will finish the work.

Next bigger check would be to wait some time for server response and check Inbox folder for error return message if email was not valid. But that option could be an overkill for the task.

szewski
  • 79
  • 4