Extract email address from bounces

[benniesanders]

Greetings,

Is there a way to extract the email address from a bounce like this? (I am exporting all of the bounces from Outlook to an Access database.)

This is an automatically generated Delivery Status Notification.

Delivery to the following recipients failed.

       person@email.com

I have 300 of these and of course I can go through and hand pick the address out and plop it into another field, but I'm probably going to be having bounces formatted like this forever, so if anybody has a great idea, please let me know. I figured out how to extract the email addresses out of brackets and parens, but these "stand alones" have me baffled.

Many thanks in advance.

Bennie

 

[SgtJarrow]

This greatly depends on the data. Does the "bounce" always look like the sample you provided?

The following would work, but might not ALWAYS catch the right value:
Check each line for the existence of an @ sign and . (period). Almost every email address I can think of is formatted similar to: emailaddress@carrier.format
Once you find a line that has an @ symbol and a period, you can parse that out. Do you just want the actual email name, or do you also want the @carrier.format part? If just the email address portion, grab everything before the @ symbol and trim off the white space. If you want the whole addy, grab the whole line and trim off the white space (this is based on your sample.)

You have asked a question, that while doable, has a wide margin of decision-making to be made - both before design and for business rules.

Just my opinions...

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[benniesanders]

Hi Robert, that sounds great. Yes those bounces always look like that. Those are the only ones I have probs with. and what you are suggesting sounds great, but I don't now how to do it.

Check each line for the existence of an @ sign and . (period). Almost every email address I can think of is formatted similar to: emailaddress@carrier.format
Once you find a line that has an @ symbol and a period, you can parse that out. Do you just want the actual email name, or do you also want the @carrier.format part? If just the email address portion, grab everything before the @ symbol and trim off the white space. If you want the whole addy, grab the whole line and trim off the white space (this is based on your sample.)

Not smart enough to figure out how to do this

Thanks very much!

 

[SgtJarrow]

Of course you're smart enough...you just need some exposure. Let's see if we can learn you a bit of knowledge.

Let's gather some more info. Can you post the code you currently use to access the emails? Also, you say you figured out how to do this based on brackets and parenthesis. What did you try to make that work? You say you are exporting the emails to an Access database. What is the table structure like?

Gathering the info and applying new techniques to what you already know will help you a lot more in the long run than me just posting some code. Plus, how we continue from this point depends on how you are accessing the emails.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[benniesanders]

Hi Robert,

Here's the code I'm using to extract email addresses from brackets and <>:

brackets
email_address: Mid([body],InStr([body],'[')+1,InStr([body],']')-(InStr([body],'[')+1))

<>
email_address: Mid([body],InStr([body],'<')+1,InStr([body],'>')-(InStr([body],'<')+1))

I made a few feeble attempts with the @ symbol but I didn't get anywhere. I'll keep plugging away and let you know when I figure it out! many thanks for your help.

 

[SgtJarrow]

Well, it sure does matter when I realize this is the QUERY forum. This whole time I was thinking directing you towards a function to make this work.

Since we are looking at a query formula, I found the following to work based on the sample you originally posted:

Trim(Mid([body],InStr(1,[body],"failed.")+11))

This looks for the location of the "failed.", adds enough characters to get to the last line, takes that whole portion and trims off the white space. You should be left with just an email....such as person@email.com from your sample.

Give it a shot, look up the functions I used in help if you don't know them, and let me/us know how it works.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer