How to Extract information from multiple emails in Outlook into a spreadsheet?

Posted on 2012-08-22
Last Modified: 2012-08-28
I have multiple emails and I would like to automatically extract information from a table included in the body of the emails.   The sample email is attached.  I would like to use a Macro so that I can extract the following from the table in the body of the email:

For example, Excel column headings would be:

System Affiliation (Owner or AC)
Owner Email Address
Corrected Owner Email (if needed)
Administrative Contact (AC) Name
Corrected AC Name (if needed)
AC Email Address
Corrected AC Email Address (if needed)
Emergency Contact (EC) Name
Corrected EC Name (if needed)
EC Email Address
Corrected EC Email Address (if needed)

If information in the table are blank, information should be extracted blank.

I have outlook and Excel 2010.


Question by:amriska
    LVL 13

    Expert Comment

    by:Jesus Rodriguez
    The first that you have to do is parsing the email and the second is when match the content inserting into the exel

    This must be create as an Outlook Addin in Visual Studio

    'First Part

    Imports System.Net
    Imports System.ComponentModel
    Imports Excel = Microsoft.Office.Interop.Excel
    Imports System.IO

     Private Sub Application_NewMail() Handles Application.NewMail
            Dim tempApp As Outlook.Application
            Dim tempInbox As Outlook.MAPIFolder
            Dim InboxItems As Outlook.Items
            tempApp = CreateObject("Outlook.Application")
            tempInbox = tempApp.GetNamespace("Mapi").GetDefaultFolder(Outlook.OlDefaultFolders.olFolderInbox)
            InboxItems = tempInbox.Items
            Dim newMail As Outlook.MailItem

            Dim inBox As Outlook.MAPIFolder = Me.Application.ActiveExplorer().Session.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderInbox)
            Dim DestinationFolder As Outlook.MAPIFolder = inBox.Folders("Processed")

            For Each newMail In InboxItems
                If newMail.UnRead and If InStr(UCase(newMail.Subject), "Subject of the email that you want", CompareMethod.Text) > 0 Then
                        Dim TxtEmail As String = newMail.Body
              End If
    End Sub

    'Second Part
    Public Sub ParsetheEmail(Eml as string)
         Dim Txt As String() = Eml.Split(vbCrLf) 'Separated by Car return
         Dim STR as string=""
         Dim ValueForPWSID as string=""
         For Each STR In Txt
            'Check if exist the condition adn get into the value
               If InStr(UCase(STR), "PWSID #", CompareMethod.Text) > 0 Then
                     'More or less the idea to get the value
                     ValueForPWSID = Trim(Mid(STR, 10, Len(STR) - 9))
                End If
        'Put into excel
            Dim xlApp As Excel.Application
            Dim xlWorkBook As Excel.Workbook
            Dim xlWorkSheet As Excel.Worksheet
            Dim misValue As Object = System.Reflection.Missing.Value
            Dim i As Integer
            Dim j As Integer

            xlApp = New Excel.Application
            xlWorkBook = xlApp.Workbooks.Add(misValue)
            xlWorkSheet = xlWorkBook.Sheets("sheet1")
            xlWorkSheet.Cells(1, 3) = ValueForPWSID 'Write on Col 1 and Row 3


    End Sub

        Private Sub releaseObject(ByVal obj As Object)
                obj = Nothing
            Catch ex As Exception
                obj = Nothing
            End Try
        End Sub

    Accepted Solution

    I don't have visual studio

    Author Closing Comment

    Good Effort

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Suggested Solutions

    My experience with Windows 10 over a one year period and suggestions for smooth operation
    Check out this infographic on what you need to make a good email signature that will work perfectly for your organization.
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

    734 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

    17 Experts available now in Live!

    Get 1:1 Help Now