Solved

How to parse email body from outlook to excel

Posted on 2011-03-23
48
1,367 Views
Last Modified: 2016-02-11
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
Comment
Question by:lraimondo
  • 24
  • 23
48 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35205605
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
 

Author Comment

by:lraimondo
ID: 35207359
 

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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35213238
Thanks. That helps. :)

However you missed my second question...

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

Sid
0
 

Author Comment

by:lraimondo
ID: 35214557
Sorry Sid, the subject would change each time however, in this case it's Spring has Sprung.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35214562
lraimondo: So you want to check this on every email that comes in?

Sid
0
 

Author Comment

by:lraimondo
ID: 35214627
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35214698
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35214705
Oops a typo in the above post. unpractical~~>Impractical

Sid
0
 

Author Comment

by:lraimondo
ID: 35214790
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35214826
>>>>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
 

Author Comment

by:lraimondo
ID: 35214854
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35214879
>>>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
 

Author Comment

by:lraimondo
ID: 35214907
AWESOME!!!!!!!!!  Can't wait to test it all out.

Thanks!

Lisa
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35214953
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
 

Author Comment

by:lraimondo
ID: 35214985
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35215000
Sure. Just stepping out for a moment. Will post the code soon.

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35215351
Lisa, do you have any predefined Excel file or do you want me to create it from scratch?

Sid
0
 

Author Comment

by:lraimondo
ID: 35215378
If you would, please create it from scratch.  I don't have anything set.

Thanks, Lisa
0
 
LVL 30

Expert Comment

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

Sid
0
 

Author Comment

by:lraimondo
ID: 35215504
Great!  Looking forward to testing it out myself.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35215835
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
 

Author Comment

by:lraimondo
ID: 35215850
Great news!  Yes, I am using Office 2007.
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 35215999
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
 

Author Comment

by:lraimondo
ID: 35216063
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35216081
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
 

Author Comment

by:lraimondo
ID: 35216098
LOL   :)    Actually, our office closes early on Fridays.   Nice, huh?
0
 

Author Comment

by:lraimondo
ID: 35216236
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
 

Author Comment

by:lraimondo
ID: 35216336
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35216569
>>>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
 

Author Comment

by:lraimondo
ID: 35216694
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35216773
>>>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
 

Author Comment

by:lraimondo
ID: 35216847
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35216866
Show me snapshots of the following

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

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35216869
And when I say snapshots, I mean printscreen. :)

Sid
0
 

Author Comment

by:lraimondo
ID: 35216943
Ok, I attached images of each......hope this helps.

Lisa
Rules.jpg
vbacode.jpg
email.jpg
ThisOutlookSession.cls
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35216983
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
 

Author Comment

by:lraimondo
ID: 35217024
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
 

Author Comment

by:lraimondo
ID: 35217047
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35217058
>>>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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35217079
>>>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
 

Author Comment

by:lraimondo
ID: 35217080
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
 

Author Comment

by:lraimondo
ID: 35217084
Sorry, just got your updated post.  Disregard my message
0
 

Author Comment

by:lraimondo
ID: 35217093
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35217098
>>>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
 

Author Comment

by:lraimondo
ID: 35217137
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35217156
>>>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
 

Author Comment

by:lraimondo
ID: 35217163
Thanks!  I will be in touch if I do.

Lisa
0
 

Expert Comment

by:Natashar7
ID: 40966107
can I use this code to extract certain text from body of outlook 2010 emails with a certain subject line?
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

When it comes to writing scripts for a Client/Server computing environment it is essential to consider some way of enabling the authentication functionality within a script. This sort of consideration mainly comes into the picture when we are dealin…
This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

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

13 Experts available now in Live!

Get 1:1 Help Now