Extract data from email append to Excel doc

Hey Experts,

I have an email that comes in from a web form and need to pull data from the rows that contain information.  I'm trying to create a VB Macro that I can run on selected emails to have it pull the information from the form itself.  I have a string of code that works for identifying the data using RegEx, but I'm having some issues in getting it to export the fields to an Excel document on the next empty row.  

The RegEx part is listed here, it pulls the information from the 4 rows in the form.

Sub GetValueUsingRegEx()
    Dim olMail As Outlook.MailItem
    Dim Reg1 As RegExp
    Dim M1 As MatchCollection
    Dim M As Match
    Dim strSubject As String
    Dim testSubject As String
          
    Set olMail = Application.ActiveExplorer().Selection(1)
      
    Set Reg1 = New RegExp
     
For i = 1 To 4
 
With Reg1
    Select Case i
    Case 1
        .Pattern = "(First Name[:]([\w-\s]*)\s*)\n"
        .Global = False
         
    Case 2
       .Pattern = "(Last Name[:]([\w-\s]*)\s*)\n"
       .Global = False
        
    Case 3
        .Pattern = "(Firm Name[:]([\w-\s]*)\s*)\n"
        .Global = False
        
    Case 4
        .Pattern = "(Email Address[:]([\w-\s]*)\s*)\n"
        .Global = False
        
    End Select
     
End With
 
Set Reg1 = Nothing
      
End Sub

Open in new window


I'd like to also be able to define a path to the Excel document.
LVL 6
Uptime Legal SystemsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewCommented:
I assume you have Excel installed on the PC this will run on?

For testing, can you provide:

1.

A sample or two of the email you are processing

2.

What you want the output Excel file to look like~bp
0
Bill PrewCommented:
Also, the code you posed does not actually extract or match any regex strings, and isn't syntactically correct, since the FOR loop isn't ended, etc.  So was the purpose of this just to show what the regex expressions would be that you want to extract?

~bp
0
Uptime Legal SystemsAuthor Commented:
Hey Bill, thanks for your response.  The emails come with the body of the email looking exactly like this:

New Marketing Lead:

First Name: John
Last Name: Sample
Firm Name: John Sample Co.
Email Address: john@noone.com

In Excel, I'd want the data to be added to the next available Row in 4 columns;  Column A = First Name, Column B = Last Name, Column C = Firm Name, Column D = Email Address.

Yes-- I added the code so you can see the RegExp.  I actually had some code between 'End With' and 'Set Reg1 = Nothing' but it didn't work properly (I could print results out but I couldn't figure out how to get it in to Excel).

Any help is greatly appreciated.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Bill PrewCommented:
Okay, this is working for me here, give it a try there.  I opened up the regular expression patterns a bit to work better, but you can certainly fine tune those.  Edit the path to your worksheet, and make sure it exists before running.

You will need to add references to the following as well in VBA for this to work:

Microsoft Excel 15.0 Object Model
Microsoft VBScript Regular Expressions 5.5

I also had the following references when I ran this, but not sure if all of these are needed:

Visual Basic For Applications
Microsoft Outlook 15.0 Object Library
OLE Automation
Microsoft Office 15.0 Object Library
Windows Script Host Object Model

Sub GetValueUsingRegEx()
    Const xlUp = -4162
    Const ExcelFile = "B:\EE\EE28677765\master.xls"

    Dim objMail As Outlook.MailItem
    Dim objExcel As Object
    Dim objMaster As Object
    Dim objSheet As Object
    Dim objRegExp As RegExp
    Dim i As Integer

    ' Define patterns to extract from email, and Excel columns to place data into
    Dim arrPatterns As Variant
    arrPatterns = Array("First Name\: (.*)", "Last Name\: (.*)", "Firm Name\: (.*)", "Email Address\: (.*)")
    Dim arrColumns As Variant
    arrColumns = Array("A", "B", "C", "D")
    
    ' Access the current email
    Set objMail = Application.ActiveExplorer().Selection(1)

    ' Open Excel, hide it
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = False

    ' Load data from first sheet in first workbook
    Set objMaster = objExcel.Workbooks.Open(ExcelFile, False, False)
    Set objSheet = objMaster.Sheets(1)

    ' Calculate next row to add after existing used rows
    intRow = objSheet.Cells(65536, "A").End(xlUp).Row + 1

    ' Create RegEx object for searching
    Set objRegExp = New RegExp

    ' Search for each defined pattern and if found add to Excel
    For i = 0 To UBound(arrPatterns)
        objRegExp.Pattern = arrPatterns(i)
        objRegExp.Global = False
        Set colMatches = objRegExp.Execute(objMail.Body)
        If colMatches.Count > 0 Then
            objSheet.Cells(intRow, arrColumns(i)).Value = colMatches(0).SubMatches(0)
        End If
    Next
     
    ' Save updated Excel file
    objMaster.Save
    objMaster.Close
    objExcel.Quit

    ' Release objects
    Set objRegExp = Nothing
    Set objMail = Nothing
    Set objSheet = Nothing
    Set objMaster = Nothing
    Set objExcel = Nothing
      
End Sub

Open in new window

~bp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Uptime Legal SystemsAuthor Commented:
Thanks Bill-- I was OOTO over the holiday weekend but will test this out shortly.
0
Uptime Legal SystemsAuthor Commented:
Works beautifully!  I also see the mistake I was making, I appreciate your efforts.
0
Bill PrewCommented:
Great, glad that was helpful, thanks for the feedback.

~bp
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.