?
Solved

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

Posted on 2011-05-02
26
Medium Priority
?
431 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:Ramke51
  • 12
  • 10
  • 2
  • +1
26 Comments
 
LVL 22

Expert Comment

by:rspahitz
ID: 35508777
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.
0
 
LVL 33

Expert Comment

by:Paul Sauvé
ID: 35509109
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.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35510005
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Ramke51
ID: 35514478
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.
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 35514691
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.
0
 
LVL 33

Expert Comment

by:Paul Sauvé
ID: 35516356
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)...
0
 

Author Comment

by:Ramke51
ID: 35689746
Dimillem,

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

Expert Comment

by:dlmille
ID: 35717501
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
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35717504
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
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35726644
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
0
 

Author Comment

by:Ramke51
ID: 35728827
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
0
 

Author Comment

by:Ramke51
ID: 35728992
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
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35729417
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
0
 

Author Comment

by:Ramke51
ID: 35729754
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
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35731144
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
0
 

Author Comment

by:Ramke51
ID: 35732044
Hi Demille,

How do I sent you the email?

Kelly
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35732106
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
0
 

Author Comment

by:Ramke51
ID: 35732166
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
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35732401
Ok - registration complete.

Dave
0
 

Author Comment

by:Ramke51
ID: 35732419
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35732494
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
0
 

Author Comment

by:Ramke51
ID: 35732525
Hi Demille,

Subject line is as you see it.  Never changes.

Regards,

Kelly
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35733067
Great -

by the way, my handle is

dlmille

thanks,

Dave
0
 

Author Comment

by:Ramke51
ID: 35733404
Sorry dimille, my bad on that.

Regards,

K
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35733932
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
0
 
LVL 42

Accepted Solution

by:
dlmille earned 2000 total points
ID: 35734245
Ok - a note on early versus late binding, re: whether you have to add the Microsoft Outlook Library reference or not.  Early Binding - yes, you add it, and when programming, you also get the intellisense to help use properties/methods - most programmers use early binding in development, then convert to late binding for deployment.  Not knowing whether others will use this, I've commented out the early binding references, so your reference to the library is not required anymore.  It would bomb with someone's different version, until they added that version in the VBA Explorer...  Here's a little bit on binding with the Microsoft Outlook Library:  http://www.dicks-clicks.com/excel/olBinding.htm

Note, this app was an adaptation from original source (predominately the GetFolder() and BrowseForFolder() functions):  http://www.vbaexpress.com/kb/getarticle.php?kb_id=875

Attached, please find Solution using late binding (I commented it so you can change to early binding for debugging or future development to enable intellisense, etc.)

1.  The app prompts you for clearing the output database - if No, then it appends, and if you hit No then cancel selecting a folder you get a cleaned database
2.  The app will interrogate the folder you select (and subsequent subfolders), searching for subject, "Asept-Air - Register a Product"
3.  Once email is successfully found, the app generates output using the function, getDataFromBody(message body, "search string:").  This allows you to add additional strings to search, make modifications otherwise, and/or delete items.  If an item is not found, a "-- Not Found --" string will be pasted in the appropriate field.
4.  The getDataFromBody() function uses Instr and other string manipulation functions to get at the input beyond the search string, returning that input for processing
5.  The app lands you on the "Extract Output" sheet to examine results, and based on Sheet Activate event, does some AutoFit and Filter formatting for ease of use, going forward.

Here's the primary code:

 
Option Explicit
'Adapted from source: http://www.vbaexpress.com/kb/getarticle.php?kb_id=875
Const HEADER = "Email,First Name,Last Name,Phone,City,State,Postal,Country,Brand,Model,Serial,Date,Place,Ext Warranty"
Sub ExtractFromEmails_ProcessAllSubFolders()
     
    Dim i               As Long
    Dim strFrom         As String
    Dim strFromEmailAddr As String
    
    'Dim iNameSpace      As NameSpace 'early binding
    'Dim myOlApp         As Outlook.Application 'early binding
    'Dim SubFolder       As MAPIFolder 'early binding
    'Dim mItem           As MailItem 'early binding
    
    Dim iNameSpace      As Object 'late binding with Microsoft Outlook Runtime Library
    Dim myOlApp         As Object 'late binding with Microsoft Outlook Runtime Library
    Dim SubFolder       As Object 'late binding with Microsoft Outlook Runtime Library
    Dim mItem           As Object 'late binding with Microsoft Outlook Runtime Library
    
    Dim FSO             As Object 'late binding with Microsoft Scripting Runtime Library
    Dim ChosenFolder    As Object 'late binding with Microsoft Scripting Runtime Library
    Dim Folders         As New Collection
    Dim EntryID         As New Collection
    Dim StoreID         As New Collection
     
    Dim outWks          As Worksheet
    Dim outCursor       As Range
    Dim outHeader()     As String
    Dim headerCols      As Long
    
    Dim msgBody         As String
    
    Dim xMsg            As Long
    
    
    
    Set outWks = ThisWorkbook.Sheets("Extract Output")
    
    xMsg = MsgBox("Clear Output Data First?", vbYesNoCancel, "Hit Yes to Clear, No to Append, Cancel to Abort")
    
    If xMsg = vbYes Then
        outWks.Cells.ClearContents
        Set outCursor = outWks.Range("A1")
        outHeader = Split(HEADER, ",")
        
        headerCols = UBound(outHeader) + 1
        
        With outCursor.Resize(, headerCols)
            .Value = outHeader
            .Font.Bold = True
        End With
        
    ElseIf xMsg = vbNo Then
        Set outCursor = outWks.Range("A1", outWks.Range("A" & Rows.Count).End(xlUp))
    Else
        Exit Sub 'abort!  quit out
    End If
    
    Set outCursor = outCursor.Offset(1, 0) 'one row down from last row in output database
    
    Set FSO = CreateObject("Scripting.FileSystemObject") 'late binding
    Set myOlApp = CreateObject("Outlook.Application") 'late binding with Microsoft Outlook Runtime Library
    'Set myOlApp = Outlook.Application 'early binding
    Set iNameSpace = myOlApp.GetNamespace("MAPI")
    Set ChosenFolder = iNameSpace.PickFolder
    
    If ChosenFolder Is Nothing Then GoTo ExitSub
    
    Call GetFolder(Folders, EntryID, StoreID, ChosenFolder)
     
    For i = 1 To Folders.Count
         
        Set SubFolder = myOlApp.Session.GetFolderFromID(EntryID(i), StoreID(i))
        'On Error Resume Next 'Not needed if there aren't any User Defined Fields which can be accessed via mItem.UserProperties("Name of Field")
        For Each mItem In SubFolder.Items 'a better alternative than for j=1 to SubFolder.Items.Count, as SubFolders is a collection
            If InStr(mItem.Subject, "Asept-Air - Register a Product") > 0 Then
            
                strFrom = mItem.SenderName
                strFromEmailAddr = mItem.SenderEmailAddress
                msgBody = mItem.Body

                'now write it out
                outCursor.Offset.Value = getDataFromBody(mItem.Body, "email:")
                outCursor.Offset(0, 1).Value = getDataFromBody(mItem.Body, "First Name:")
                outCursor.Offset(0, 2).Value = getDataFromBody(mItem.Body, "Last Name:")
                outCursor.Offset(0, 3).Value = getDataFromBody(mItem.Body, "Phone:")
                outCursor.Offset(0, 4).Value = getDataFromBody(mItem.Body, "City:")
                outCursor.Offset(0, 5).Value = getDataFromBody(mItem.Body, "State:")
                outCursor.Offset(0, 6).Value = getDataFromBody(mItem.Body, "Postal:")
                outCursor.Offset(0, 7).Value = getDataFromBody(mItem.Body, "Country:")
                outCursor.Offset(0, 8).Value = getDataFromBody(mItem.Body, "Brand:")
                outCursor.Offset(0, 9).Value = getDataFromBody(mItem.Body, "Model:")
                outCursor.Offset(0, 10).Value = getDataFromBody(mItem.Body, "Serial:")
                outCursor.Offset(0, 11).Value = getDataFromBody(mItem.Body, "Date:")
                outCursor.Offset(0, 12).Value = getDataFromBody(mItem.Body, "Place:")
                outCursor.Offset(0, 13).Value = getDataFromBody(mItem.Body, "Ext Warranty:")
                
                Set outCursor = outCursor.Offset(1, 0)
            End If
        Next mItem
        
        'On Error GoTo 0
    Next i
     
    'cleanup
    
ExitSub:
     outWks.Activate
End Sub

Function getDataFromBody(msgBody As String, srchStr As String) As String
Dim startString As Integer, endString As Integer, lenString As Integer
Dim stringPart As String

    getDataFromBody = srchStr

    startString = InStr(msgBody, srchStr)
    
    If startString = 0 Then
        getDataFromBody = "-- Not Found --"
        Exit Function
    Else
        startString = startString + Len(srchStr)    'exclude the search string from the input data
        lenString = InStr(Mid(msgBody, startString, Len(msgBody) - startString), vbCrLf)
        getDataFromBody = Mid(msgBody, startString, lenString)
    End If
    
exitFunction:
    
    
End Function

Open in new window


Please see attached File.

Enjoy!

Dave  
GetEmailFormDataToExcel-r2.xlsm
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
By default Outlook 2016 displays only one time zone in the Calendar. The following article explains how to display two time zones in one calendar view.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

840 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