Extract emails, names and other data from body of emails in Outlook to csv MailChimp

It depends on the format, but you may be able to simply use Split() to break the body into individual lines and then use InStr() to Identify the line you are dealing with (First Name, Last Name, ...) and then parse the individual lines you are look for using Mid() with InStr().

You can either push the info to a text file directly or use Excel automation to generate a CSV.

Do you mean you have a form on your website they fill in, and when they submit it it generates an email to your mailbox?  If so then I expect the format of each incoming email is very well defined and a consistent structure.  If so then you could use a rule in Outlook that monitors that mailbox, and when a matching email comes in the rule runs a small VBA procedure that extracts the data and appends to a file.  Since the format should be consistent that would be a pretty easy VBA procedure to create.  I'm making a number of assumptions though, so let me know if that might work if you want help.

---

»bp

Daniel - shall I paste the code of the macro I found?

Bill - all your assumptions are right: website form/enquiry, always same layout email. Please provide more on how to do VBA

Daniel - here it is:
https://www.datanumen.com/blogs/quickly-extract-email-addresses-bodies-multiple-outlook-emails/

Best if you attach a copy of an entire message, headers + all.

Well, it's easy enough to add a rule in Outlook that runs a VBA macro once it is created, and the rule can process new emails as they are received.  If you aren't familiar with setting up rules we can guide on that.

But just to see if this is something you want to explore, here's an example of a small VBA macro that I created for someone else as a rule target.  It would need a little modification, but what it does is log some info about the incoming email (sender, receiver, date, subject, ...) to a CSV file, appending to it.  You would do something similar but looking at the message BODY as well, and searching for the infomation you want, either by position, or by label text, etc.

See if this is something you could deal with, or if after looking at it you say, yikes, I don't want to go down that path :).

Sub LogIncomingEmail(MyMail As MailItem)
    Dim intFile As Integer
    Dim strID As String
    Dim strFile As String
    Dim strText As String
    Dim objMail As Outlook.MailItem
    Dim objParent As Outlook.MAPIFolder
    
    Const LogDelim = ","
    
    strFile = "c:\temp\email.csv"
    
    strID = MyMail.EntryID
    
    Set objMail = Application.Session.GetItemFromID(strID)
    If objMail.Class = olMail Then
        Set objParent = objMail.Parent.Parent
        strText = Quote(objParent.Name) & LogDelim & _
                    Quote(objMail.ReceivedByName) & LogDelim & _
                    Quote(objMail.To) & LogDelim & _
                    Quote(objMail.SenderName) & LogDelim & _
                    Quote(objMail.SenderEmailAddress) & LogDelim & _
                    Quote(objMail.Subject) & LogDelim & _
                    objMail.ReceivedTime
        Set objParent = Nothing

        intFile = FreeFile
        Open strFile For Append As #intFile
        Print #intFile, strText
        Close #intFile
    End If
    
    Set objMail = Nothing
        
End Sub

Function Quote(s)
   Quote = Chr(34) & Replace(s, Chr(34), Chr(34) & Chr(34)) & Chr(34)
End Function

Select allOpen in new window

---

»bp

Thank you, Let me try and I'll be back tomorrow with results

Thank You all for Your replies and do apology for my late reply. We need something like it's shown in screenshot of Excel's table. I also attach a fragment of HTML body. Could You take a look at this?
html_body_fragment.png
html_body.txt
2019-02-27_12h04_13.png

Is it going to be okay to expert to a CSV text file, that could then manually be imported into Excel when needed?  I think that is a better approach than trying to add to an Excel workbook as each mail comes in.  First, the overhead of opening Excel and using automation to place the data on the next available row and then saving takes a significant amount of time, and doing that from an Outlook rule could be problematic when multiple emails are received quickly.  Also, if someone has the workbook open in Excel when the rule runs it will fail also as the file will be in use.  Appending to a CSV text file is much easier and faster, and likely less problematic.

If that works I can try and add logic to what I shared earlier to try and parse out the data from your emails based on the additional info you provided.  But I didn't want to go down that path if Excel output was a mandatory requirement.

---

»bp

CSV is fine.
I am sure that only the first run will take long and then probably will run it every month to include only the new emails.

Screenshot of the whole email attached.  You can see that the whole email (enquiry) we receive is structured into sections and is actually a table.  All emails look the same. When I say table, every section is its own table: ENQUIRY DETAILS, VEHICLE DETAILS, OPTIONAL EXTRAS, DEAL DETAILS (4 tables in total; tables can be better seen when you hit forward).

I know I mentioned getting emails addresses and names from these emails, but if we can do that, then let's create something that will extract all of the information into csv, i.e. from "First Name" to "Initial Rental".
screenshot_email1.jpg

Okay, so if you'er looking at running it in a periodic batch mode that's probably better than firing a realtime rule when emails arrive.  That might keep the door open to writing directly to an Excel sheet, but let's focus on the tricky part to start.

The hard part will be extracting the data from the email bodies.  I get a sense that the info on a "real" inquiry would be private and not shareable.  Is there any chance you could fill out a "test" inquiry and submit it with some fake or made up info.  Then when that email arrives save it as a MSG file and post it up here.  This will give me a clear look at how the data is stored in the email, and how to extract it effectively.  Any chance you could do that?

---

»bp

Thank You all for Your help, of course, especially Bill. Your macro works great.

Kindest regards,
Tom