使用VBA从Outlook退信（Undeliverable）报告的乱码中提取邮箱地址

I’ve dealt with this exact frustration before—garbled text in NDRs can turn body parsing into a mess, but leveraging Outlook’s underlying MAPI properties and regex can cut through the noise. Here’s a robust VBA script that prioritizes reliable structured data extraction, with a fallback for trickier cases:

Step 1: Access Outlook’s VBA Editor #

• Press Alt + F11 in Outlook to open the editor.
• Insert a new module: Right-click your Outlook project in the left pane > Insert > Module.

Step 2: Paste the VBA Code #

Sub ExtractNDRRecipients()
    Dim olApp As Object
    Dim olNamespace As Object
    Dim olInbox As Object
    Dim olItems As Object
    Dim olItem As Object
    Dim strRecipient As String
    Dim regex As Object
    Dim matches As Object
    Dim match As Object
    Dim ws As Object
    Dim row As Integer
    
    ' Initialize Outlook objects (late binding avoids reference setup)
    Set olApp = CreateObject("Outlook.Application")
    Set olNamespace = olApp.GetNamespace("MAPI")
    Set olInbox = olNamespace.GetDefaultFolder(6) ' 6 = Default Inbox
    ' Filter items to only include NDRs (adjust subject keyword if needed)
    Set olItems = olInbox.Items.Restrict("[Subject] LIKE '%Undeliverable%'")
    
    ' Set up regex for standard email pattern matching
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = "\b[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}\b"
    regex.Global = True
    regex.IgnoreCase = True
    
    ' Optional: Create Excel worksheet to export results
    On Error Resume Next
    Set ws = CreateObject("Excel.Application").Workbooks.Add.Worksheets(1)
    On Error GoTo 0
    If Not ws Is Nothing Then
        ws.Cells(1, 1).Value = "NDR Subject"
        ws.Cells(1, 2).Value = "Extracted Recipient"
        row = 2
    End If
    
    ' Process each NDR message
    For Each olItem In olItems
        strRecipient = ""
        
        ' 1. First try extracting from MAPI property (most reliable, skips garbled text)
        On Error Resume Next
        ' PR_REPORT_DESTINATION_ADDRESS property maps to this hex code
        strRecipient = olItem.PropertyAccessor.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0E04001E")
        On Error GoTo 0
        
        ' 2. Fallback: Parse message body if MAPI property fails
        If strRecipient = "" Then
            Dim bodyText As String
            ' Combine plain text and HTML body to cover all content
            bodyText = olItem.Body & olItem.HTMLBody
            
            Set matches = regex.Execute(bodyText)
            For Each match In matches
                ' Grab first valid email (adjust to collect all if needed)
                strRecipient = match.Value
                Exit For
            Next match
        End If
        
        ' Output results
        If strRecipient <> "" Then
            Debug.Print "NDR: " & olItem.Subject & " | Failed Recipient: " & strRecipient
            If Not ws Is Nothing Then
                ws.Cells(row, 1).Value = olItem.Subject
                ws.Cells(row, 2).Value = strRecipient
                row = row + 1
            End If
        Else
            Debug.Print "Could not extract recipient for: " & olItem.Subject
        End If
    Next olItem
    
    ' Clean up objects
    Set olItem = Nothing
    Set olItems = Nothing
    Set olInbox = Nothing
    Set olNamespace = Nothing
    Set olApp = Nothing
    Set regex = Nothing
    
    ' Show results
    If Not ws Is Nothing Then
        CreateObject("Excel.Application").Visible = True
        MsgBox "Extraction done! Results are in the new Excel workbook.", vbInformation
    Else
        MsgBox "Extraction done! Check the Immediate Window (Ctrl+G) for results.", vbInformation
    End If
End Sub

How This Solves Your Garbled Text Issue: #

• MAPI Property Priority: The script first pulls the failed recipient directly from Outlook’s internal PR_REPORT_DESTINATION_ADDRESS property. This is a structured, machine-readable field that’s never garbled—completely bypassing messy body text.
• Regex Fallback: If the MAPI property isn’t available (rare, but possible with non-standard server NDRs), it scans both plain text and HTML body content for valid email patterns. The regex ignores surrounding garbled characters as long as the email itself follows standard formatting.

Customization Tips: #

• Adjust NDR Filter: If your NDRs use subject lines like "Delivery Failed", modify the Restrict line to: olInbox.Items.Restrict("[Subject] LIKE '%Delivery Failed%'")
• Collect Multiple Recipients: To capture all failed recipients in an NDR, remove the Exit For line inside the regex loop and store matches in an array.
• Security: Enable macros in Outlook (File > Options > Trust Center > Trust Center Settings > Macro Settings) or sign the script digitally for safer execution.

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