Start Free Trial

asked on

How to parse email body from outlook to excel

I am very new to VBA and would like to know the code for parsing or extracting the following data from an email in Outlook. All I need is the Name, Address, CSZ and phone number, email address.

Is there way to do this and import into excel? This also needs to have the lines breaks removed.

Any help you can provide is greatly appreciated. Lisa

Here is an example of the data I need extracted:

Message Body below:

Hi there! I am keeping my fingers crossed! : ) Thanks so much!

Name
Address1
Address2
Fortuna CA 95540

(123)456-7891 - Cell phone number. : )
(123)456-7891 - Home

Thanks!!

END OF MESSAGE

lraimondo: This is a very common request. In fact I have the code ready with me but can you post a sample of the actual message so that I get my references correct in the code?

Also would the email contain any specific word in the SUBJECT?

Sid

ASKER

Hi Sid,

I've attached a sample of the email. Each email may be different and may contain more than one phone number. All I'm looking to extract are the email addresses, names, addresses and phone numbers.

Hope the attached file helps.
email.jpg

Thanks. That helps. :)

However you missed my second question...

>>>>Also would the email contain any specific word in the SUBJECT?

Sid

ASKER

Sorry Sid, the subject would change each time however, in this case it's Spring has Sprung.

lraimondo: So you want to check this on every email that comes in?

Sid

ASKER

No, these emails are a from a promo on our website that only allows entries to be sent in between specific times.....example - 12am to 11pm. Once the mailbox is closed, all entries that are in the inbox would be processed. Hope that helps.

Lisa

Just to be clear you want to check every mail that comes between that time? I feel it is still unpractical.

Ok. Do they come from a specific email address?

Sid

Oops a typo in the above post. unpractical~~>Impractical

Sid

ASKER

Ok, maybe I don't understand why you feel its unpractical. Is there a way to run this automatically....unattended? Maybe your suggestion would be better? You would know better than me since I'm new to this.

These emails could from from anyone and would only be delivered to us - webspecial@reevesintl.com

Here's a sample ....a mailbox is setup on our end and a link is setup as follows:

Order requests will be taken via email only on Monday, March 21, 2011 from
12:00 AM EDT until 11:59 PM EDT. Please send one email per person only.
Sending multiple emails will not increase your odds of being chosen. Duplicate
emails or emails received outside the order request window will be rejected.
· To submit an order request, send an email to webspecial@reevesintl.com with
Spring Has Sprung in the subject line. Please include ONLY the following
information in the body of your email: NAME, SHIPPING ADDRESS and
DAYTIME TELEPHONE NUMBER/ CELL PHONE NUMBER.

Let me know if you need more info.

Lisa

>>>>send an email to webspecial@reevesintl.com with
Spring Has Sprung in the subject line.

If it will have that subject line then it is simple.

Let me explain how it works. The macro will run unattended. A rule will be set so that the macro runs on specific emails rather than all emails :) In the above case if the subject has that text then the macro will run each time a mail drops in with that text in the subject.

Sid

ASKER

Ok, great....but what if the subject changes? Do I have the option to change it accordingly? It may not be the same for every web special they send out. Also, can this export into excel unattended?

Lisa

>>>Ok, great....but what if the subject changes?

You will have to amend the rule

>>>Do I have the option to change it accordingly? It may not be the same for every web special they send out.

Yes

>>>Also, can this export into excel unattended?

Yes :)

Sid

ASKER

AWESOME!!!!!!!!! Can't wait to test it all out.

Thanks!

Lisa

Also 1 quick thing. You have to decide on a fixed format of the mail for example

**************************************************************
Hello blah blah

blah blah

NAME:
SHIPPING ADDRESS:
DAYTIME TELEPHONE NUMBER:
DAYTIME CELL PHONE NUMBER:

blah blah
blah blah
**************************************************************

This will ensure that the parsing can be done easily. I made a similar macro for one of my clients so I have the code ready :) Just need to make changes once you approve of the above.

Sid

ASKER

Looks good to me with one exception.......can you add a second address line, just in case they use 2 lines for an address......example:

123 john street
apt. 11111

Thanks again! Lisa

Sure. Just stepping out for a moment. Will post the code soon.

Sid

Lisa, do you have any predefined Excel file or do you want me to create it from scratch?

Sid

ASKER

If you would, please create it from scratch. I don't have anything set.

Thanks, Lisa

Ok Your code is Ready. Testing it by sending a mail to my self :)

Sid

ASKER

Great! Looking forward to testing it out myself.

Ok Finally. Tested and it is working.

Please confirm if you have outlook 2007 and Excel 2007. I am creating an instruction file for you with snapshots.

Sid

ASKER

Great news! Yes, I am using Office 2007.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

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

Start Free Trial

ASKER

Sid,

Thank you very much. I will look over the instructions and test the code. I may not be able to get back to you until Monday......if thats ok?

Lisa

Sure no problem :)

If I would have known that you have that much time in hand, I would have taken it easy... lol

Sid

ASKER

LOL :) Actually, our office closes early on Fridays. Nice, huh?

ASKER

Ok, tested it but I have one question.

This should work on emails that are already in the inbox, correct? Not as they arrive.

Lisa

ASKER

Sid,

When I run the rule, it appears to work but when I check the data.xls file that i saved to my C:\ drive....no data appears.

Lisa

>>>This should work on emails that are already in the inbox, correct? Not as they arrive.

No. They will work even when the emails arrive

>>>When I run the rule, it appears to work but when I check the data.xls file that i saved to my C:\ drive....no data appears.

Did you set the macro setting to low?

Sid

ASKER

How can I get the emails that are already in the inbox extracted then? i need to export what I've already received.

Yes, all macros are disabled.

>>>Yes, all macros are disabled.

They need to be enabled.

>>>How can I get the emails that are already in the inbox extracted then?

Click on menu tools~~>Rule and alerts. In the following window you have an option to run the rules on the existing messages.

Sid

ASKER

Ok, macros are set according to your instructions and I have Enabled Macro security in Excel.

I run the rule to run on messages already in the inbox, but they do not appear in the spreadsheet. :(

Sorry! Lisa

Show me snapshots of the following

1) Rules window
2) Vba Code
3) One sample email

Sid

And when I say snapshots, I mean printscreen. :)

Sid

ASKER

Ok, I attached images of each......hope this helps.

Lisa
Rules.jpg
vbacode.jpg
email.jpg
ThisOutlookSession.cls

The email is not in the format it is supposed to be in :)

See the text file that I uploaded. The emails have to be in that format ;) Remember we discussed it in ID: 35214953

To check if the macro works or not, simply send yourself a mail using the format as shown in the text file. See if that data gets appended in the xls file.

Sid

ASKER

Ok, I understand now! :)

I guess this won't work with the sample I sent? The emails will be in all different formats and there isn't any way for me to change over 1,000 emails to the sample you sent. Also, the emails I already have are also different. Not everyone sends in the information the same exact way. Sorry!

Lisa

ASKER

Ok, how about this......

Is there a way for me to extract the body of the email into excel and I can re-format it once it's in excel? Maybe using a code to search, find and replace hard line returns.....chr(10), chr(13)??????

Lisa

>>>Not everyone sends in the information the same exact way. Sorry!
Yes but if you want to automate it then you will have to educate your users to send them in a preferred format. Something like this maybe (taken from IDID: 35214790)

***************************************************************************
Order requests will be taken via email only on Monday, March 21, 2011 from 12:00 AM EDT until 11:59 PM EDT. Please send one email per person only.Sending multiple emails will not increase your odds of being chosen. Duplicate emails or emails received outside the order request window will be rejected.

To submit an order request, send an email to webspecial@reevesintl.com with Spring Has Sprung in the subject line. Please send the email in this format

Hello xyx

NAME: <Your Name Here>
SHIPPING ADDRESS1: <Your 1st line of address Here>
SHIPPING ADDRESS2: <Your 2nd line of address Here>
DAYTIME TELEPHONE NUMBER: <Day Time Telephone Here>
DAYTIME CELL PHONE NUMBER: <Day Time Mobile Here>

Regards

<Your Name>
***************************************************************************

>>>Ok, how about this......

For the existing 1000 emails, we can extract the data into Excel but formatting them will be a nightmare.

Sid

ASKER

Yes, that would be great for future emails. I will ask that they make the change so I can use your code.

Let me know about the other idea......not sure if that could be done?

Lisa

ASKER

Sorry, just got your updated post. Disregard my message

ASKER

Ok, how would you go about extracting the data? Do you have code for that?

I will see what I can do with that data at that time once it's in excel.

Lisa

>>>Ok, how would you go about extracting the data? Do you have code for that?

No I don't have it but can write one.

Sid

ASKER

No need to write one.....I thought you may have already had one available.

I am going to just use the one you sent for all future emails and just leave it at that.

I truly appreciate all your time, effort and patience on this. I will accept your solution :)

Thanks so much and have a great weekend!

Lisa

>>>Thanks so much and have a great weekend!

You too :)

In case you want the code for the other, open a new question and paste the link here and I will post the code :) Should take approx 15 mins to write the code :)

Sid

ASKER

Thanks! I will be in touch if I do.

Lisa

can I use this code to extract certain text from body of outlook 2010 emails with a certain subject line?