asked on

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.

Bill Prew

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

Bill Prew

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

Uptime Legal Systems

ASKER

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.

ASKER CERTIFIED SOLUTION

Bill Prew

membership

This solution is only available to members.

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

Start Free Trial

Uptime Legal Systems

ASKER

Thanks Bill-- I was OOTO over the holiday weekend but will test this out shortly.

Uptime Legal Systems

ASKER

Works beautifully! I also see the mistake I was making, I appreciate your efforts.

Bill Prew

Great, glad that was helpful, thanks for the feedback.

~bp