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.
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)?
.Send
call is located in a place where any error would jump execution to theErrorHandler
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 GuindonThisOutlookSession
before attempting to send an email. Not very familiar with the Outlook OM though :-/ – Mathieu GuindonIf OutlookApp.Session.Accounts.Count = 0 Then GoTo OutlookNotSetup
(right afterSet 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