Hello--I'm new and hope you can help me.
I need to compile a list of bounced email addresses. I set up folders in Outlook Mail for the various bounce reasons. I right-clicked the folder I wanted, selected the body as the custom field data I wanted, and extracted it as a .csv file to Excel. I can extract the first bad email from each list, but I don't see whether it's pulling all of them. Here's what I cobbled together in the way of Excel code from other places online:
Column A = Body
Column B = Bounced emails flagged by mailto:
Column C = Column B without the maito: (finished product)
To extract the bad email(s) from each body, I use this in B2:
=MID(A2,FIND("mailto:",A2)+1,FIND(">",A2)-FIND("<",A2)-1)
To remove the mailto: command, I use this in C2:
=SUBSTITUTE(B2"mailto:","")
To remove long strings of non-human-readable code as "=VALUE!" in Column A, I use this:
Home > Find & Select > Go To Special > Blanks > OK
right-click in empty cell
Delete > Entire Row > OK
rinse and repeat as needed
BUT...
I think this may only be picking up the first bad address in messages containing two or more bad addys.
Is Outlook Mail not exporting the full message body? Or is there a little piece of code I'm missing--perhaps a way to concatenate?
I can't post the actual data, but here's a made-up example of the problem:
Undeliverable: Important E-mail For You
From: [email protected]
Sent: 1/1/2015
To: [email protected]
Delivery has failed to these recipient or groups:
[email protected]
Your message wasn't delivered due to a permission or security issue. It may have been rejected by a moderator, the address may only accept e-mail from certain senders, or another restriction may be preventing delivery.
[email protected]
Your message wasn't delivered due to a permission or security issue. It may have been rejected by a moderator, the address may only accept e-mail from certain senders, or another restriction may be preventing delivery.
[email protected]
Your message wasn't delivered due to a permission or security issue. It may have been rejected by a moderator, the address may only accept e-mail from certain senders, or another restriction may be preventing delivery.
(and in the body of the message, the tags <mailto:[email protected]> etc. appear.)
So all of the above would be in a single Excel cell, and I want to extract not only "[email protected]", but also "[email protected]" and "[email protected]" -- preferably into separate cells, even if they were in the same row as the body message.
Any ideas or suggestions greatly appreciated. Thank you.
Bookmarks