• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2616
  • Last Modified:

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
0
lraimondo
Asked:
lraimondo
  • 24
  • 23
1 Solution
 
SiddharthRoutCommented:
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
0
 
lraimondoAuthor Commented:
 

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
0
 
SiddharthRoutCommented:
Thanks. That helps. :)

However you missed my second question...

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

Sid
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
lraimondoAuthor Commented:
Sorry Sid, the subject would change each time however, in this case it's Spring has Sprung.
0
 
SiddharthRoutCommented:
lraimondo: So you want to check this on every email that comes in?

Sid
0
 
lraimondoAuthor Commented:
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
0
 
SiddharthRoutCommented:
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
0
 
SiddharthRoutCommented:
Oops a typo in the above post. unpractical~~>Impractical

Sid
0
 
lraimondoAuthor Commented:
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
0
 
SiddharthRoutCommented:
>>>>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
0
 
lraimondoAuthor Commented:
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
0
 
SiddharthRoutCommented:
>>>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
0
 
lraimondoAuthor Commented:
AWESOME!!!!!!!!!  Can't wait to test it all out.

Thanks!

Lisa
0
 
SiddharthRoutCommented:
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
0
 
lraimondoAuthor Commented:
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
0
 
SiddharthRoutCommented:
Sure. Just stepping out for a moment. Will post the code soon.

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

Sid
0
 
lraimondoAuthor Commented:
If you would, please create it from scratch.  I don't have anything set.

Thanks, Lisa
0
 
SiddharthRoutCommented:
Ok Your code is Ready. Testing it by sending a mail to my self :)

Sid
0
 
lraimondoAuthor Commented:
Great!  Looking forward to testing it out myself.
0
 
SiddharthRoutCommented:
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
0
 
lraimondoAuthor Commented:
Great news!  Yes, I am using Office 2007.
0
 
SiddharthRoutCommented:
Attached Three files

1) Excel file where the data will be saved. The code assumes that it is saved in C: If you change the location of the file then do amend the same in the code.

2) Instruction.Doc. Would request you to read it first as I have explained everything in detail :)

3) Sample email

Hope this helps.

Sid

Code Used

Sub CaptureData(MyMail As MailItem)
    Dim SenderName As String, SentTime As String, MailBody As String, strFileName As String
    Dim MyArray() As String, SenderName As String, Address1 As String, Address2 As String
    Dim strTemp() As String, TlNo As String, MobNo As String
    
    Dim LastRow As Long
    Dim oXLApp As Excel.Application
    Dim oXLBook As Excel.Workbook
    Dim oXLSheet As Excel.Worksheet
    
    On Error GoTo Whoa
    
    '~~>  Change File Name Here
    strFileName = "C:\Data.xls"
    
    '~~> Extraction of Details
    strID = MyMail.EntryID
    MailBody = MyMail.Body
    
    MyArray = Split(MailBody, vbNewLine)
    For i = LBound(MyArray) To UBound(MyArray)
        'NAME:
        If InStr(1, MyArray(i), "NAME:", vbTextCompare) Then
            strTemp = Split(MyArray(i), "NAME:")
            SenderName = Trim(strTemp(1))
        End If
        
        'Address1
        If InStr(1, MyArray(i), "SHIPPING ADDRESS1:", vbTextCompare) Then
            strTemp = Split(MyArray(i), "SHIPPING ADDRESS1:")
            Address1 = Trim(Replace(MyArray(i), "SHIPPING ADDRESS1:", ""))
        End If
        
        'Address2
        If InStr(1, MyArray(i), "SHIPPING ADDRESS2:", vbTextCompare) Then
            strTemp = Split(MyArray(i), "SHIPPING ADDRESS2:")
            Address2 = Trim(Replace(MyArray(i), "SHIPPING ADDRESS2:", ""))
        End If
        
        'DAYTIME TELEPHONE NUMBER:
        If InStr(1, MyArray(i), "DAYTIME TELEPHONE NUMBER:", vbTextCompare) Then
            strTemp = Split(MyArray(i), "DAYTIME TELEPHONE NUMBER:")
            TlNo = Trim(Replace(MyArray(i), "DAYTIME TELEPHONE NUMBER:", ""))
        End If

        'DAYTIME CELL PHONE NUMBER:
        If InStr(1, MyArray(i), "DAYTIME CELL PHONE NUMBER:", vbTextCompare) Then
            strTemp = Split(MyArray(i), "DAYTIME CELL PHONE NUMBER:")
            MobNo = Trim(Replace(MyArray(i), "DAYTIME CELL PHONE NUMBER:", ""))
        End If
    Next i
    
    '~~> Create a new instance of Excel
    Set oXLApp = New Excel.Application
    '~~> Open Excel File
    Set oXLBook = oXLApp.Workbooks.Open(strFileName)
    '~~> Work with First Workbook
    Set oXLSheet = oXLBook.Worksheets(1)
    oXLApp.Visible = False
    oXLApp.DisplayAlerts = False
    oXLApp.ScreenUpdating = False
    
    LastRow = oXLSheet.Range("A" & oXLApp.rows.Count).End(xlUp).Row + 1
    
    '~~> Index Number
    oXLSheet.Range("A" & LastRow) = SenderName
    oXLSheet.Range("B" & LastRow) = Address1
    oXLSheet.Range("C" & LastRow) = Address2
    oXLSheet.Range("D" & LastRow) = TlNo
    oXLSheet.Range("E" & LastRow) = MobNo
    
LetsContinue:
    oXLApp.DisplayAlerts = True
    oXLApp.ScreenUpdating = True
    
    '~~> Close and save
    oXLBook.Close savechanges:=True
    
    '~~> CLEANUP (VERY IMPROTANT)
    Set oXLSheet = Nothing
    Set oXLBook = Nothing
    oXLApp.Quit
    Set oXLApp = Nothing
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

Open in new window

INSTRUCTION-ON-SETUP-OF-MACRO---.doc
Data.xls
Sample-email.txt
0
 
lraimondoAuthor Commented:
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
0
 
SiddharthRoutCommented:
Sure no problem :)

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

Sid
0
 
lraimondoAuthor Commented:
LOL   :)    Actually, our office closes early on Fridays.   Nice, huh?
0
 
lraimondoAuthor Commented:
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
0
 
lraimondoAuthor Commented:
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
0
 
SiddharthRoutCommented:
>>>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
0
 
lraimondoAuthor Commented:
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.
0
 
SiddharthRoutCommented:
>>>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
0
 
lraimondoAuthor Commented:
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
0
 
SiddharthRoutCommented:
Show me snapshots of the following

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

Sid
0
 
SiddharthRoutCommented:
And when I say snapshots, I mean printscreen. :)

Sid
0
 
lraimondoAuthor Commented:
Ok, I attached images of each......hope this helps.

Lisa
Rules.jpg
vbacode.jpg
email.jpg
ThisOutlookSession.cls
0
 
SiddharthRoutCommented:
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
0
 
lraimondoAuthor Commented:
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
0
 
lraimondoAuthor Commented:
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
0
 
SiddharthRoutCommented:
>>>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>
***************************************************************************
0
 
SiddharthRoutCommented:
>>>Ok, how about this......

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

Sid
0
 
lraimondoAuthor Commented:
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
0
 
lraimondoAuthor Commented:
Sorry, just got your updated post.  Disregard my message
0
 
lraimondoAuthor Commented:
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
0
 
SiddharthRoutCommented:
>>>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
0
 
lraimondoAuthor Commented:
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
0
 
SiddharthRoutCommented:
>>>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
0
 
lraimondoAuthor Commented:
Thanks!  I will be in touch if I do.

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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 24
  • 23
Tackle projects and never again get stuck behind a technical roadblock.
Join Now