Excel VBA邮件自动化：ML.Display后暂停执行，操作后恢复流程

Great question! Your current setup with MsgBox adds an unnecessary extra step—users have to handle the email then switch back to click Yes/No in the prompt. We can fix this by leveraging Outlook's Inspector events to detect when the user has either sent the email or closed the compose window, letting your code resume automatically without extra prompts.

Step 1: Create a Class Module for Inspector Events #

First, we need a class module to handle events from Outlook's email compose window (called an "Inspector" in Outlook's object model):

1. In the VBA Editor, right-click your project → Insert → Class Module.
2. Rename the module clsOutlookInspector (use the Properties window, press F4 to open it).
3. Paste this code into the class:

Public WithEvents objInspector As Outlook.Inspector
Public blnProcessComplete As Boolean

Private Sub objInspector_Close()
    ' Triggered when the email window closes (after sending or manual close)
    blnProcessComplete = True
End Sub

Step 2: Update Your Existing Code #

Modify your standard module to use the class we created. This will make the code wait until the user finishes interacting with the email, then pick up automatically:

' Declare a global instance of our inspector handler class
Dim oInspectorHandler As clsOutlookInspector

' ... your existing code ...

ML.To = Madd ' address
ML.CC = MaddCC ' CC
ML.Subject = "Testing"

' Initialize the inspector handler to monitor the email window
Set oInspectorHandler = New clsOutlookInspector
Set oInspectorHandler.objInspector = ML.GetInspector

' Display the email for the user to interact with
ML.Display

' Wait loop: pause code until the user closes/sends the email
Do While Not oInspectorHandler.blnProcessComplete
    DoEvents ' Keeps Excel responsive while waiting
Loop

' Check if the email was sent, then update your worksheet
On Error Resume Next ' Handle cases where the mail item might no longer exist
If ML.Sent = True Then
    Mws.Cells(j, 9).Value = "Sent"
Else
    Mws.Cells(j, 9).Value = "Not Send"
End If
On Error GoTo 0

' Clean up objects to avoid memory leaks
Set oInspectorHandler.objInspector = Nothing
Set oInspectorHandler = Nothing

' ... your existing error handling and loop code ...

ErrCatcher:
If Err <> 0 Then
    Mws.Cells(j, 8).Value = "Name Resolution Failed"
End If
j = j + 1

Key Details Explained #

• Inspector Events: The objInspector_Close event fires as soon as the user closes the email window (whether they sent it or just closed the compose window). This signals our code to resume.
• Wait Loop: The Do While loop with DoEvents keeps Excel responsive while waiting—no more frozen windows!
• Sent Check: The ML.Sent property lets us verify if the email was actually sent, so we can update your worksheet accurately.
• Cleanup: Always release object references with Set ... = Nothing to prevent memory issues in VBA.

Notes #

• If processing multiple emails in a loop, reinitialize the oInspectorHandler instance for each email.
• This works seamlessly for both manual sending (user clicks "Send") and closing the window without sending—no extra prompts required!

内容的提问来源于stack exchange，提问作者Tejas Kore