如何判断Outlook邮件草稿窗口是已发送还是已关闭？

要实现仅当Outlook邮件发送时执行后续代码，关闭草稿窗口则跳过循环继续，你可以通过监控邮件窗口状态并检查邮件的Sent属性来实现。以下是修改后的完整代码：

Sub SendEmail_Draft()

Dim EmailApp As Outlook.Application
Dim EmailItem As Outlook.MailItem
Dim row As Integer
Dim emailSent As Boolean
Dim Email As String
Dim Email_Subject As String
Dim Emailcc As String ' 补充定义变量

Set EmailApp = New Outlook.Application

For row = 2 To 1000
    If Cells(row, 13) = "Draft" Then
    
        ' 读取邮件相关信息（根据实际列调整）
        Email = Cells(row, 4).Value
        Email_Subject = " "
        Emailcc = Cells(row, 5).Value ' 假设CC信息在第5列，需根据实际修改
        
        Set EmailItem = EmailApp.CreateItem(olMailItem)
        With EmailItem
            .To = Email
            .CC = Emailcc
            .Subject = Email_Subject
            .HTMLBody = " "
            .Display ' 显示邮件草稿窗口
        End With
        
        ' 循环等待邮件窗口关闭，避免程序假死
        Do While EmailItem.Inspector.IsVisible
            DoEvents
        Loop
        
        ' 判断邮件是否已发送：处理邮件被销毁（用户关闭时不保存）的情况
        emailSent = False
        On Error Resume Next
        emailSent = EmailItem.Sent
        On Error GoTo 0
        
        ' 仅当邮件发送成功时执行后续代码
        If emailSent Then
            Cells(row, 13).Value = "Completed"
            
            With Rows(row)
                '.Interior.Color = RGB(23, 213, 13)
                .Font.Name = "Calibri"
                .Font.Size = 11
                .Font.ThemeColor = xlThemeColorLight1
            End With
            
            Cells(row, 14).Value = Now
            Cells(row, 15).Value = Environ("USERNAME")
            Rows(row).Cut Destination:=Sheets("Completed").Range("A" & Rows.Count).End(xlUp).Offset(1)
        End If
        
        ' 释放对象，避免内存泄漏
        Set EmailItem = Nothing
    End If
Next row

Set EmailApp = Nothing
End Sub

关键说明 #

• 等待窗口关闭：通过Do While EmailItem.Inspector.IsVisible循环，配合DoEvents让Excel保持响应，直到用户关闭邮件窗口。
• 判断发送状态：窗口关闭后，检查EmailItem.Sent属性是否为True。添加错误处理是因为如果用户关闭草稿时选择“不保存”，邮件对象会被销毁，直接访问属性会触发错误。
• 变量补充：原代码中Emailcc未定义和赋值，需根据实际数据列补充对应单元格引用。

内容的提问来源于stack exchange，提问作者Tom Dobrokhot