Parsing email messages in Outlook 2007

[Jumranb]

I have done a lot of macro programming in Excel, but never in Outlook. Here is the situation:

We do Phone Tech Support, and then send out an email survey to a random sample of about 200 customers per day about their experience with us.

Some of these customers gave us their email address several years ago, and these addresses are no longer valid; we find about a 25% bounce rate due to invalid email addresses.

I would like to pull the invalid emails from the Delivery notification email and put into an excel spreadsheet (or even just a quick text output or all addresses) for easy find/clear in our database.

The main layout of email we receive is the following:

[tt]The following message to <user@domain.ext> was undeliverable.
The reason for the problem:
5.1.0 - Unknown address error 550-'No such user - psmtp'[/tt]

Or similar. These emails are in a "Email Failures" subfolder of the Inbox in one of 10 different mailboxes I have listed in Outlook.

Is there a way to parse out these email addresses in all emails that match this wording/format in that folder? Additionally, moving the email message once this is done to another subfolder of the Email Failures folder would be beneficial, but not required.

Any idea on where I start? I have as yet been unable to find anything helpful enough to get me started on this.

Thank you in advance for any suggestions!

 

[Bong]

It looks like "@" is unique to the substring you're looking for, right? Will there always be leading "<" and trailing ">"?



_________________
Bob Rashkin

 

[Jumranb]

Yes, it will always be enclosed by the leading "<" and trailing ">"

The third line varies, occasionally repeating the email address and adding another "@" character to the body of the message. However, the email address I need will always be in the first line of the body, if that can be filtered separately. The first line will always read the same, other than the variable email address I need to parse.

 

[Bong]

I would set this up in 2 parts.
The first part is a rule that is run on all incoming mail. Presumably, these notifications come from some recognizable address (system administrator or some such), so the rule will be of the form: "when mail arrives from <address>, run a script".
The second part will be the script, itself. Now I don't have any particular experience with this but I imagine the item is sent as an argument to the script. In that case it would look something like:

sub badAddress(msgRcvd)
  strMsgBody=msgRcvd.Body
  firstbracket=instr(1,strMsgBody,"<")
  secondbracket=instr(firstbracket,strMsgBody,">")
  mailAddress=mid(strMsgBody,firstbracket+1,secondbracket-1)
end sub

_________________
Bob Rashkin

 

[Jumranb]

Thank you for your help! Parsing a string is something I have difficulty with for whatever odd reason, and you helped me not only parse it, but get into the message itself to GET the string to parse!

A rule will not work in this case, I have (had) over 1100 all ready to process, and I need to create a list of these (and now multiple future messages) as a query including all addresses needed.

I used the code at the end of this post to successfully do what I wanted, after some additional research and using the code you provided to help. What I had to do, though, is manually separate the emails I knew fit the known bad format I listed above into their own folder. Others messages (only 64 out of the 1100+) say things such as this:

---
[tt]Hi. This is the qmail-send program at domain removed.
I'm afraid I wasn't able to deliver your message to the following addresses.
This is a permanent error; I've given up. Sorry it didn't work out.

<address@domain>:
Mailaddress is administratively disabled. (#5.2.1)[/tt]
---

Is there a way to check the message, and process it if it matches the required format, but ignore it otherwise? Would I simply add something like this to do it, or is there a more certain way?

if Mid(strMsgBody, 1, 25) = "The following message to " then
. . .

Thanks again for your help! This project has been interesting, I've never used Outlook directly in macros before.

My current code:

Sub badAddress()

'Assign shared mailbox for use
Set myNamespace = Application.GetNamespace("MAPI")
Set myMailbox = myNamespace.CreateRecipient("Support@ourdomain")
'email address above has been changed for demo purpose, but is the address of the mailbox to be used

myMailbox.Resolve
'Using a shared mailbox (one other than the user default) requires resolution to continue

'If someone runs this that does not have rights or access to the mailbox, the script simply does not run
If myMailbox.Resolved Then
'Assign inbox folder type for use, and define the Inbox as a folder
   Set inboxFolder = myNamespace.GetSharedDefaultFolder(myMailbox, olFolderInbox)
   
   'Subfolders are accessed through parent folders
   'There may be a better way to do this, I do not know it
    Set useFolder = inboxFolder.Folders("Email Failures")
    Set myFolder = useFolder.Folders("Process These")

    'Create variable to store the Query to input into UI for database (=<address> or <address> or . . .)
    queryStrng = "="
    
    'Using the "Process These" folder, go through all messages in it, and parse out email addresses
    'If the folder is empty, do not process anything - doing so would cause an error
    If myFolder.Items.Count > 0 Then
        For i = 1 To myFolder.Items.Count
        strMsgBody = myFolder.Items(i).Body
        
        'Seperate out the email address
        firstbracket = InStr(1, strMsgBody, "<")
        secondbracket = InStr(firstbracket, strMsgBody, ">")
        mailAddress = Mid(strMsgBody, firstbracket + 1, secondbracket - firstbracket - 1)
            
            'If this is the last message to be parsed, end the query, otherwise add an extra "or" as needed
            If i = myFolder.Items.Count Then
                queryStrng = queryStrng + mailAddress
            Else
                queryStrng = queryStrng + mailAddress + " or "
            End If
        Next i

        'Create/update txt file with query string
        filecreate = "c:\query.txt"
        Set fs = CreateObject("Scripting.FileSystemObject")
        Set a = fs.CreateTextFile(filecreate, True)
        a.writeline (queryStrng)
        a.Close
    End If

End If

End Sub

 

[Bong]

I think you can certainly do what you suggest, if the message body you're interested in always conforms to a particular format.

_________________
Bob Rashkin