asked on

REVISED - Exporting Data from an Outlook email into an Excel spreadsheet

Hello,

I use an online email form to collect product registration infomration. All forms submitted contain the same data string titles, but the values change obviously. I would like to be able to export the data from any given email into a specifc Excel spreadsheet where the new data is appened to the next blank row in the spreeadsheet. Not all emails coming into this account contian this information so I would like to be able to run it selctively based on the content of any given email. The same spreadsheet would thus become a repository for the information and be updated as new infomration is received. The emails which do contain the data all have the same fromat as indicated;

email:2bobs@home.com
First Name:Bob
Last Name:Robert
Phone:4011213455
Address:1455 Main Street
City:Burlington
State:Vermont
Postal:04502
Country:USA
Brand:Best
Model:Best1
Serial:B12346543
Date:2011-05-02
Place:Walmart
Ext Warranty:No

Any help or input would be greatly appreciated.

rspahitz

What I usually do in cases like this (if you only have a few e-mails per day) is copy the entire e-mail and paste into cell A1 of a "raw data" sheet.
From there, I have various formulas designed to extract all the information, as needed.
Finally, I take the formatted data and copy/paste into the desired location.

You can also automate parts of these steps, depending on how complex and tedious the parts are.
If you'd like to go in this direction, let me know and I'll help with the macro to put the resulting data in the right place.

Paul Sauvé

If these emails are from your own Internet site, you can probably add the information to your server data base and export it to Excel once a day. simply define the table in your database and create a new occurence each time SEND is clicked. Or have it sent as an attachment to the email as a cvs file.

Table fields:

email:
First Name:
Last Name:
Phone:
Address:
City:
State:
Postal:
Country:
Brand:
Model:
Serial:
Date:
Place:
Ext Warranty:

I would add a time stamp field as well (yyyy-mm-dd-hh-mm-ss) in order to easily sort the records.

dlmille

I've recently done something like this. You might find this especially useful if the fields you're mentioning are user defined fields in an email form.

See posts, here. If you're headed down THIS route, let me know and I can assist..

http:/Q_26928681.html

Enjoy!

Dave

Ramke51

ASKER

Paulsauve

Your solution sounds interesting but my ISP wants to charge an arm and a leg to implement the dtat tablke and thus I was looking for a low cost work around that would achieve the same results. Do you have any sample macros to share. The date stamp is not so important since the warranty registration is validated on the site iteself before submitting the info.

rspahitz

I think the date is to ensure you don't double-post an entry; with the date, you can confirm that it's a new post and not an existing one in the spreadsheet.

Paul Sauvé

Seems like you should create an email form, then use the code suggested by dlmille to move the data from the email to the spreadsheet! Click on the link for his code (ref.: ID: 35510005 Author: dlmille)...

Ramke51

ASKER

Dimillem,

I would like to explore your propsed solution further. Can you assist me with building the macro?

dlmille

Apologies, I must have missed your post. Yes, I can assist. I'm off right now, but will take a look first thing in the morning and see if I can't get a working demo going for you.

First - a question: Are those fields in the emails User Defined Fields, or free text? This is important to understand, before I proceed. Either way, we can get toward a solution, fairly quickly.

Dave

dlmille

Or, perhaps better stated, can you ensure your email form that collects this information has the requisite User Defined Fields? Or do we need to parse free text?

Thanks for the clarification...

Dave

dlmille

Ok - Assuming ALL the fields you get from the email form are User Defined Fields, the attached should work.

If it is free text in the Body, you'll need to advise, as that would take a bit of parsing.

If you run the macro (hit the button to Extract from Outlook), you'll be prompted for an Outlook folder to process - you can choose your inbox, or some other folder where you keep the forms that come in.

The macro will process all records and publish the results in the Extract Output tab, without differentiating whether it was a web email form or not. Let's just ensure this is working for you, and we can tailor later.

It might be useful to create an outlook folder and drop a couple of your email forms in there, then use this macro to select that folder and process.

Again, if these are Outlook User Defined Fields, this should work, though some of the field names may need to be tweaked (see code, as it should be self apparent).

If they're not, let me know and perhaps post a couple dummy example messages for testing?

Cheers,

Dave
GetEmailFormDataToExcel-r1.xlsm

Ramke51

ASKER

Hi DeMille,

Sorry for the delay I was occupied with a number of other pressing matters. All field are used defined or static. I will try your solution out in the next day or so and let you know.

Thanks Kelly

Ramke51

ASKER

Hi Demille,

I put the product registration emails into a separate folder in my outlook called warranty.

The macro installs okay in Excel, but when I click on extract from within excel, I get a VBA error message that says "Compile error, can not find project or library".

What am I doing wrong?

Thanks,

Kelly

dlmille

Sorry about that. Go to the VBA Project Explorer (ALT-F11) and click menu TOOLS-> REFERENCES and add the reference to the Microsoft Outlook Object Library.

Dave

Ramke51

ASKER

Hi Demille,

Okay worked, but only extracted email address of sender but none of the other information. What do I need to get to extract all the info?

Thanks,

Kelly

dlmille

Would it be possible to pull up one of your emails, change the data so its not sensitive info, and then save the message and upload for me to see?

Another alternative would be for you to take a PICTURE of the email form (again, after changing the data, if sensitive) so I can see what the email form looks like.

Let me know if you need help with that.

Thanks!

Dave

Ramke51

ASKER

Hi Demille,

How do I sent you the email?

Kelly

dlmille

well - you could mail it to me at dlmille at hotmail dot com

or you could change the saved email file suffix to .txt and post it here and I can change it back to .msg...

Dave

Ramke51

ASKER

Hi Demille,

Click on the following link and the complete the product registration form. I will then upload the email I receive this way you will get full circle. For seial number input can be anything.

http://www.asept-air.com/pages.php?pageid=47

Regards,

Kelly

dlmille

Ok - registration complete.

Dave

Ramke51

ASKER

Got it, as below

Asept-Air---Register-a-Product.msg

dlmille

Ok - these aren't user defined fields. But the data is nicely formatted. Give me a bit and I'll revert.

Can I assume the subject line to be consistent to filter out any other emails you may get?

Dave

Ramke51

ASKER

Hi Demille,

Subject line is as you see it. Never changes.

Regards,

Kelly

dlmille

Great -

by the way, my handle is

dlmille

thanks,

Dave

Ramke51

ASKER

Sorry dimille, my bad on that.

Regards,

K

dlmille

Help me out here. In the message, I see the last field, called "Send info:" - do you want to collect that?

I also noticed there's not a field called "Ext Warranty:" - is this still desired?

I'm pulling it together in a way you should be able to easily modify, re: more fields/obsolete fields going forward...

Dave

ASKER CERTIFIED SOLUTION

dlmille

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial