Retrieve email address from Chinese characters in Undeliverable email report using VBA

Question

When an email address is wrong, Outlook receives an "Undeliverable" (NDR report). I am trying to retrieve the email address from this Undeliverable message. Sometimes text in these undeliverable emails is changed to "Chinese" characters.

I tried multiple solutions from the Internet.

https://answers.microsoft.com/en-us/office/forum/office_2013_release-customize/problem-in-vba-reading-text-from-body-of-out-of/1d8ca369-a4c0-41da-9d28-3f490de3ed8c

Extract text string from undeliverable email body to Excel

Search Outlook Undeliverable Bounce Report

Answer 1

You can try to use the ReportItem.Body property to get the email addresses. If it still doesn't work as stated on the pages mentioned in your post you can:

1. Use a low-level API on which Outlook is based on - Extended MAPI or just any third-party wrappers around this API such as Redemption.
2. Save the item to the disk, then parse it and get the required information from here.

Answer 2

Elaborating a bit on this, essentially because the item in Outlook is actually REPORT.IPM.Note.NDR class ReportItem object, and not a MailItem object, you can't pull info from it in the way you normally would with a MailItem Body using VBA.

For background, the reason the 'body' becomes garbled is just a known bug with Outlook ReportItem objects when accessed with VBA - the contents are actually rendered by Outlook on the fly from various hidden MAPI property values, so they don't really exist as you see them, hence why the 'body' value is gobbledygook.

I made an update to my answer yesterday on the "Extract text string from undeliverable email body to excel" question you said you already looked at after having this exact same problem, doing lots of digging and piecing together a solution from various sources.

Assuming the items are all REPORT.IPM.Note.NDR class (which they are usually if using Exchange) then you can use the ReportItem.PropertyAccessor.GetProperty method against the ReportItem object to access many of the MAPI property values that aren't normally exposed to VBA/OLE.

Credit to Sidupac in this post for listing out some of the more common ones you might want with their respective Hex ID and their Microsoft Schema URLs as below:

Sub PAValues(olReportItem As Outlook.ReportItem)

Dim PA As Outlook.propertyAccessor
Set PA = olReportItem.propertyAccessor

On Error Resume Next
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x001A001E"), , "PR_MESSAGE_CLASS"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0037001E"), , "PR_SUBJECT"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x00390040"), , "PR_CLIENT_SUBMIT_TIME"
MsgBox PA.BinaryToString(PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x003B0102")), , "PR_SENT_REPRESENTING_SEARCH_KEY"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x003D001E"), , "PR_SUBJECT_PREFIX PT_STRING8"
MsgBox PA.BinaryToString(PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x003F0102")), , "PR_RECEIVED_BY_ENTRYID"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0040001E"), , "PR_RECEIVED_BY_NAME"
MsgBox PA.BinaryToString(PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x00410102")), , "PR_SENT_REPRESENTING_ENTRYID"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0042001E"), , "PR_SENT_REPRESENTING_NAME"
MsgBox PA.BinaryToString(PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x004F0102")), , "PR_REPLY_RECIPIENT_ENTRIES"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0050001E"), , "PR_REPLY_RECIPIENT_NAMES"
MsgBox PA.BinaryToString(PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x00510102")), , "PR_RECEIVED_BY_SEARCH_KEY"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0064001E"), , "PR_SENT_REPRESENTING_ADDRTYPE"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0065001E"), , "PR_SENT_REPRESENTING_EMAIL_ADDRESS"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0070001E"), , "PR_CONVERSATION_TOPIC"
MsgBox PA.BinaryToString(PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x00710102")), , "PR_CONVERSATION_INDEX"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0075001E"), , "PR_RECEIVED_BY_ADDRTYPE"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0076001E"), , "PR_RECEIVED_BY_EMAIL_ADDRESS"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x007D001E"), , "PR_TRANSPORT_MESSAGE_HEADERS"
MsgBox PA.BinaryToString(PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0C190102")), , "PR_SENDER_ENTRYID"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0C1A001E"), , "PR_SENDER_NAME"
MsgBox PA.BinaryToString(PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0C1D0102")), , "PR_SENDER_SEARCH_KEY"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0C1E001E"), , "PR_SENDER_ADDRTYPE"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0C1F001E"), , "PR_SENDER_EMAIL_ADDRESS"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0E02001E"), , "PR_DISPLAY_BCC"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0E03001E"), , "PR_DISPLAY_CC"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0E04001E"), , "PR_DISPLAY_TO"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0E060040"), , "PR_MESSAGE_DELIVERY_TIME"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0E070003"), , "PR_MESSAGE_FLAGS"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0E080003"), , "PR_MESSAGE_SIZE"
MsgBox PA.BinaryToString(PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0E090102")), , "PR_PARENT_ENTRYID"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0E12000D"), , "PR_MESSAGE_RECIPIENTS"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0E13000D"), , "PR_MESSAGE_ATTACHMENTS"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0E1B000B"), , "PR_HASATTACH"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0E1D001E"), , "PR_NORMALIZED_SUBJECT"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0E1F000B"), , "PR_RTF_IN_SYNC"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0E28001E"), , "PR_PRIMARY_SEND_ACCT"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0E29001E"), , "PR_NEXT_SEND_ACCT"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0FF40003"), , "PR_ACCESS"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0FF70003"), , "PR_ACCESS_LEVEL"
MsgBox PA.BinaryToString(PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0FF80102")), , "PR_MAPPING_SIGNATURE"
MsgBox PA.BinaryToString(PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0FF90102")), , "PR_RECORD_KEY"
MsgBox PA.BinaryToString(PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0FFA0102")), , "PR_STORE_RECORD_KEY"
MsgBox PA.BinaryToString(PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0FFB0102")), , "PR_STORE_ENTRYID"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0FFE0003"), , "PR_OBJECT_TYPE"
MsgBox PA.BinaryToString(PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0FFF0102")), , "PR_ENTRYID"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x1000001E"), , "PR_BODY"
MsgBox PA.BinaryToString(PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x10090102")), , "PR_RTF_COMPRESSED"
MsgBox PA.BinaryToString(PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x10130102")), , "PR_HTML"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x1035001E"), , "PR_INTERNET_MESSAGE_ID"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x1045001E"), , "PR_LIST_UNSUBSCRIBE"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x1046001E"), , "N/A"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x30070040"), , "PR_CREATION_TIME"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x30080040"), , "PR_LAST_MODIFICATION_TIME"
MsgBox PA.BinaryToString(PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x300B0102")), , "PR_SEARCH_KEY"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x340D0003"), , "PR_STORE_SUPPORT_MASK"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x340F0003"), , "N/A"
MsgBox PA.BinaryToString(PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x34140102")), , "PR_MDB_PROVIDER"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x3FDE0003"), , "PR_INTERNET_CPID"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x80050003"), , "SideEffects"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x802A001E"), , "InetAcctID"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x804F001E"), , "InetAcctName"
MsgBox PA.BinaryToString(PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x80660102")), , "RemoteEID"
MsgBox PA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x80AD001E"), , "x-rcpt-to"

End Sub

The syntax is simply the GetProperty method of the ReportItem.propertyAccessor with the appropriate Schema URL as the argument (slightly different for binary values)

ReportItem.propertyAccessor.GetProperty("SCHEMA-URL")

or

ReportItem.propertyAccessor.BinaryToString(ReportItem.propertyAccessor.GetProperty("SCHEMA-URL")

for standard and binary properties respectively, so you can handle the return values as you wish, into variables or in-line with your existing code, whatever it's doing.

Specifically for the original recipient email address(es) from the NDR, the schema entry you want is "http://schemas.microsoft.com/mapi/proptag/0x0E04001E" which gives you "PR_DISPLAY_TO" property value (the original To address(es) of the outgoing message to which the NDR relates).

This could return just an email address, a semicolon delimited list if the 'To' field contained more than one recipient which failed, or a DisplayName

If the recipient is in your contacts, the GetProperty method returns their DisplayName as opposed to the actual address, which could be a shortcoming if all your recipients are contacts. Also, if there was more than one recipient, you would need to use something like the Split function to break those down into an array to iterate through them to handle them individually.

Full enumeration of all properties can be found on this Microsoft resource but be aware that not all MAPI properties are accessible to the GetProperty method, only those with a hex value up to 0x8000 so I understand from my reading around this issue.