excel - Excel 通过 Outlook 发送电子邮件时如何处理错误？

Question

我通过 Outlook(来自 Excel)将 Excel 工作表作为电子邮件附件发送。

强烈简化的代码:

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

这个想法是代码将跳到 ErrorHandler (并清理一些设置，删除临时文件等)一旦发生错误。

如果在发送电子邮件之前发生错误，则此方法有效。但是，如果发送电子邮件出现问题，代码将停止。

一个例子:我在一个没有在 Outlook 中设置任何电子邮件帐户的 VM 上测试了代码，因此 Outlook 将启动并提示我设置一个帐户。如果我关闭 Outlook，宏将不会继续运行。



我做了一些网络研究，发现发送电子邮件的代码通常使用 On Error Resume Next 运行。陈述。我试过了，这次代码完成了运行。但是，我无法检查电子邮件是否已发送 - 因此即使无法发送电子邮件，用户也会收到确认消息框。

如何确保代码始终完成？以及如何检查电子邮件是否实际发送(以显示错误消息)？

Answer 1

如果您想完全确定您的电子邮件是否已发送，那么最好检查您的邮件是否在“已发送”或/和“发件箱”文件夹中(参见 here)。如果 vba 将在那里崩溃，那么您将知道 Outlook 应用程序可能存在错误。
如果您一次发送数百封电子邮件，请留意“发件箱”文件夹。即使在宏完成工作之后，电子邮件也会存储在那里并一一发送。

下一个更大的检查是等待服务器响应一段时间，如果电子邮件无效，检查收件箱文件夹中的错误返回消息。但该选项可能对这项任务来说太过分了。