Solved

Extract Email Addresses & Body into Spreadsheet or Database

Posted on 2013-01-07
20
1,085 Views
Last Modified: 2016-08-27
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
0
Comment
Question by:Geekamo
  • 11
  • 5
  • 2
  • +2
20 Comments
 
LVL 84
ID: 38751901
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)?
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38751927
@ 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?
Capture.PNG
0
 
LVL 47

Accepted Solution

by:
apache09 earned 500 total points
ID: 38752028
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
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38752166
@apache09

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.

Subject: "Undeliverable: NOTICE: XXXXXXXXXX STOCK PRICE CHANGE for XXXXXXXXXXX."

Body:

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

      Subject:      NOTICE: XXXXXXXXXXX STOCK PRICE CHANGE for XXXXXXXXXX.
      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?
0
 
LVL 47

Expert Comment

by:apache09
ID: 38752182
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
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38752225
@apache09

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?
CaptureAgain.PNG
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38752367
@apache09

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.
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38752384
@apache09

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...
0
 
LVL 47

Expert Comment

by:apache09
ID: 38752391
Hrmm will have a look and get back to you
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38752401
@apache09

Ok, thanks.
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 47

Expert Comment

by:apache09
ID: 38752454
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
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38752485
@apache09

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.
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38752487
@apache09

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...
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38752555
@apache09

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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38752907
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
FROM INBOX


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

JeffCoachman
0
 
LVL 47

Expert Comment

by:apache09
ID: 38752920
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
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38769346
@ 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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38770456
When I run the query (slightly modified to allow for a field named "From"):

SELECT [From], Subject, Contents
FROM INBOX

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

FWIW...
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38785635
@ 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
0
 

Expert Comment

by:Tom Kincheloe
ID: 41773310
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.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Use email signature images to promote corporate certifications and industry awards.
This process describes the steps required to Import and Export data from and to .pst files using Exchange 2010. We can use these steps to export data from a user to a .pst file, import data back to the same or a different user, or even import data t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now