search inbox emails, report string

[peersu]

Hey guys,

I'm very new to VBA; what I am looking for however is a script that will search my inbox and generate a report containing the Recipient, E-mail address of recipient, and a string such as 'SDFSD3523S' from inside the text of the e-mail.

The search itself is relating to e-mails with "Undeliverable" in the subject field. For those e-mails with "Undeliverable" in their subject field, I need a list of the information listed above.

Ideally that list would be deliminated tab, or exported to an excel spreadsheet.

ATM, I am reading everything, everywhere, to try and figure this out. Just wondering if anyone has any input to pseudocode or logic, or even code that performs a similar search function would help.

Thanks guys

[Killian]

Hi and welcome to VBAX

It's easy enough in principle, but I have a couple of questions...
First, the priniple.
Step 1:
Open the VBE (Alt+F11), add a new module and paste in the routine below. It's a script that accepts a mailitem object as an argument, extracts the info and writes a comma delimited list that you can open in Excel.[VBA]Sub LogUndeliverable(m As MailItem)

Dim f
Dim s As String
Const LOGFILE As String = "C:\TEMP\Undeliverable.CSV"

s = m.To & "," & "Some other text"
f = FreeFile
Open LOGFILE For Output As f
Print #f, s
Close #f

End Sub
[/VBA]Step 2:
In Outlook, use the Rules Wizard to create a rule: "check all new mail", "for a specific word in the subject" and "run a script".

That would do it but the questions are:
The "To" field will be you - the original recipient will be somewhere in the subject or body, no?

We can get the text content of the mail with the Body property, but how are we going to identify the reference number in the resulting string?

[peersu]

yeah i was thinking about that. in reality the identifier i need to extract is simply the recipients e-mail address.

with a list of those i can hopefully populate the rest of the info needed from our legacy database... atleast thats what i'm thinking atm

[Killian]

Well no doubt if you google searching for string patterns you'll have enough reading material for several lifetimes. At it's simplest, you can create a function to seach for "@", then from that position search back and forward for the first space and you should end up with an email address between the resultant characters.[VBA]Sub LogUndeliverable(m As MailItem)

Dim f As Integer
Dim s As String
Const LOGFILE As String = "C:\TEMP\Undeliverable.CSV"

s = GetEmailAddy(m.Body)
f = FreeFile
Open LOGFILE For Output As f
Print #f, s
Close #f

End Sub

Private Function GetEmailAddy(strBody As String) As String

Dim a As Long
Dim s As Long
Dim f As Long

a = InStr(1, strBody, "@")
s = InStrRev(strBody, " ", a)
f = InStr(a, strBody, " ")
If f = 0 Then
GetEmailAddy = "Nothing"
Exit Function
End If
GetEmailAddy = Mid(strBody, s + 1, (f - 1) - s)
If GetEmailAddy = "" Then
GetEmailAddy = "Nothing"
End If

End Function[/VBA]
A simplistic script like this can quite easily fail to give you good results; like if the email addy is at the end of a sentence, paragraph or the entire string. If you've got some examples to test against, you might be able to get reliable results. Also, if the first occurance of "@" isn't the recipients address, but some other address in the return header, maybe you'll have to loop the function and test the results or log all of them (?)

[peersu]

actually, i ended up getting really frustrated with vba.

I just made a ruleset that sorted the emails i wanted into a folder... then highlighted all the files in that folder and > file > save as .txt

then i just used PHP to extract the email addresses to a .csv file... and thats good enough lol.


but i thank you so much for your help

hopefully someone else will come here and read this!

[MOS MASTER]

actually, i ended up getting really frustrated with vba.

then i just used PHP to extract the email addresses to a .csv file... and thats good enough lol.

LOL! happens to me all the time...
Glad to see you've found your way with the Tool you know best! (like php too btw...)