Link to home
Start Free TrialLog in
Avatar of Geekamo
GeekamoFlag for United States of America

asked on

Extract Email Addresses & Body into Spreadsheet or Database

Hello Experts!

Each month our company sends out an email blast to our customers.  After every blast, we receive several thousand bounce backs.  Any email that bounces back, is acted upon internally one by one.  It is such a manual process and I'm looking to stream line this as much as possible.

Ideally, I would love some code that when run - it will extract several things,...

1.) From Email Address
2.) Email Subject
3.) Email Body/Message

Example Spreadsheet

                           A                                         B                                               C
1               This is the subject             This is the body
2               This is the subject              This is the body
3                      This is the subject              This is the body

This information will eventually end up in Microsoft Access, but in terms of simplicity - is it possible to get all of that data extracted/copied into a spreadsheet/csv?

I am unable to install any software on my computer, so I believe my only solution is going to be VB related?

I would like this code to run ONLY on a specific folder.  The last thing I want is for the code to somehow run on emails that have nothing to do with this project.

Also, my skill level is definitly a beginner. Especially when it comes to any kind of coding.

If you need anymore information, please ask!  :)

Thank you in advance for your help!

~ Geekamo
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

I would love some code that when run - it will extract several things,.
When would you run this code, and on what would you run it?

Is your goal to parse the "bounce backs" in some manner? If so, does those bounce backs always end up in a particular place (like an Outlook folder)?
Avatar of Geekamo


@ LSMConsulting

The emails end up in the "Broadcast" folder.  This folder does contain subfolders, so emails can be within the parent and sub folders.

> When would you run this code, and on what would you run it?

Any time we send out an email blast and I get bounces back.  So generally once a month this will happen.  I'm not sure what you mean on "what you run it"?

I'm not sure what you mean by parse?
Avatar of apache09
Flag of New Zealand image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Geekamo



Your solution is fantastic, it does exactly what I want - but I did notice a problem.

I just tested this out on 6 emails.  Two of those emails, are missing the body of the email message.

The two emails in question are different then the others, IE.



Your message did not reach some or all of the intended recipients.

      Sent:      8/6/2012 10:06 PM

The following recipient(s) cannot be reached: on 8/6/2012 10:14 PM
            There was a SMTP communication problem with the recipient's email server.  Please contact your system administrator.
            < #5.5.0 smtp;550 Rule imposed mailbox access for refused>


It appears that if it was a normal email, then the export is working correctly.  But if the email was bounced, like the example above - it's not exporting the body of the message.

Any ideas?
have you tried expanding the row all the way out

The Top LInes may be blank and the body may be further down in the cell
Avatar of Geekamo



Yup, these fields are completly blank.

I have attached a screenshot of the export.  It appears that if it's a normal email, (IE, normal email, auto out of office, etc) then it captures the body like normal.  But if it's a bounced email, (which is the whole point of this project) - undeliverable, then for some reason it's not capturing the body.

Any ideas?
Avatar of Geekamo



Ok, so I have taken one of those emails - fowarded it to myself with the addition of "Test" in the fowarded body of my email.  Then when I export again, it shows "Test" but doesn't include the rest of the forward.

So for some reason, the undeliveriable message - still doesn't show up.
Avatar of Geekamo



Another thing I noticed, is the undeliverable emails - show an attachment icon.  But when I view the email, there's no attachment.  Not sure if that helps you any...
Hrmm will have a look and get back to you
Avatar of Geekamo



Ok, thanks.
I do see what you mean
Possibly because its a system generated message...? Not sure

However, i did get it to work simply by forwarding to myself with test in the Top of the forwarded msg

Then ran the export

in Access is does show the top line as Test and Blank underneath
However I expanded the row down furthur and eventually I saw the PLain text NDR msg

So what does this mean....If you can get it to show, as I did, ona forwarded msg

You could setup a seperate email account just for this purpose
Forward any and all related mail items, to this specific account

Then run the export from that account

Although, still trying to see what it might take to get it to work from the current account
Avatar of Geekamo



Hmm, even as a foward - it doesn't work properly.  It only keeps the "Test", and still doesn't show the rest of the body.

As for having a seperate account, that wouldn't be an option for me.

I do suspect it's an issue cause it's system generated.
Avatar of Geekamo



Uh-oh.  I think I am seeing the missing text.  Just like how you mentioned it's all the way at the bottom of the cell.

Let me run this again...
Avatar of Geekamo



Ok, I just tested this out.  You are correct, only when I forward the email to myself and include "Test" in the forward, then the rest of the email does appear.

But, this is going to be a problem.  Cause I won't be able to fowarded all these emails to myself.

~ Geekamo
I know I am coming late to the party here, but stick with me...

You would seem to need to detect what original email these bouncebacks were from, correct?

Or are you looking to simply collect this data for every single reply you receive?
In any event, my guess is that you will need some type of filter

You can link the Inbox:
External Data-->Import & Link-->More-->Outlook folder-->Link...

Then make a query from this Linked table:
SELECT From, Subject, Contents

Again, you will probably need a WHERE clause to filter, but this gets you the info you asked for in Access.

Just having a rethink here:

Is the body of the undeliverable important?

If you have the Subject, From, TO Name, and the TO: Address

When exporting to Access or even Excel,

You would see

Undeliverable      From: System Administrator    To:

So without reading the Body, witch is a generic msg and likely you already know what is says........ is it required?

From the info above, you do get

You know there was an undeliverable
You know its a msg from System Administrator
And you shold be able to determine the email address that returned the NDR
Avatar of Geekamo


@ All,

Sorry for the delay in getting back to this thread.

@ boag2000,

I'm not sure your solution would help.  For example, even in the Import Preview area, it basically shows me snippets of what it will capture.  And even in the preview - the body is missing.  But then again, maybe connecting to it directly - maybe the body would then become viewable?  It's worth a shot, so I will definitely explore this option during the coming week.  Assuming I can figure it all out.  I am such a newb when it comes to all this, I just barely get by.  :)

@ apache09,

Sadly, it means everything to me.  I noticed on these specific emails, (all the undeliverable) every piece of information I need is located in it - and no where else.

The body of these emails contain the email addresses itself, and the reason for the email bouncing back.
When I run the query (slightly modified to allow for a field named "From"):

SELECT [From], Subject, Contents

It works fine and displays the email body ("contents") just fine for me...

Avatar of Geekamo


@ apache09,

Thank you again for all your help!  This project is slightly on the back burner cause other things have come up, so I don't technically have a solution to my specific problem.  But your answer is definitely the correct solution.  In the next week or two I'll probably talk with our IT department and explain my issue, and maybe one of them might know what is creating my issue.

~ Geekamo
Avatar of Tom Kincheloe
Tom Kincheloe

Hey Geekamo,

I too am late in joining this discussion, but I would take a stab at using for this. Zapier suggested them to me for email parsing everything from system alerts, to purchase orders, and your scenario seems to be a case that would work pretty easy too.

Their blog has posts for Excel, Google Sheets, and DB deposit, of your parsed data, and all 3 of those are native integrations, so no paying a web automation company (Zapier), to assist the sending of that data where you want it to go.

Here is there getting started blog post that has links to some of the options you have.