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        jamesdoe@wow.com               This is the subject             This is the body
2        karendoe@boop.com               This is the subject              This is the body
3        angela@reef.com                      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
Who is Participating?
apache09Connect With a Mentor Commented:
I beleive you can do 90-95 % of this simply by using Outlooks Export Option

Go to File>Open>Import

Choose Export to a File

if you want it to be in access, you can simply choose an MS access File

Once selected, choose your folder you want to export

Before the export is run, choose your feild mappings

Remove all the fields on the right other than From Email, Email Subject and Email body

Then save

The export will run and save to an access MDB

Now open the file in access
You should find only the fields you chose were exported

Now if you dont want to do this yet is access you can simply export the whole file to an CSV File

When opened in Excel you will find all the colunms related to the emails
You can then delete the non-required coloumns leaving on the From, Subject, and Email Body
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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)?
GeekamoAuthor Commented:
@ 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?
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

GeekamoAuthor Commented:

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:

      test@test.com on 8/6/2012 10:14 PM
            There was a SMTP communication problem with the recipient's email server.  Please contact your system administrator.
            <mail.XXXXXXXXX.com #5.5.0 smtp;550 Rule imposed mailbox access for test@test.com 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
GeekamoAuthor Commented:

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?
GeekamoAuthor Commented:

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.
GeekamoAuthor Commented:

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
GeekamoAuthor Commented:

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
GeekamoAuthor Commented:

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.
GeekamoAuthor Commented:

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...
GeekamoAuthor Commented:

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
Jeffrey CoachmanMIS LiasonCommented:
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: bademailaddress@domain.com

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
GeekamoAuthor Commented:
@ 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.
Jeffrey CoachmanMIS LiasonCommented:
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...

GeekamoAuthor Commented:
@ 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
Tom KincheloeCommented:
Hey Geekamo,

I too am late in joining this discussion, but I would take a stab at using mailparser.io 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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.