?
Solved

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

Posted on 2012-08-22
3
Medium Priority
?
530 Views
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:

PWSID #
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.

Thanks,

Amreska
SAMPLE-EMAIL-.msg
0
Comment
Question by:amriska
  • 2
3 Comments
 
LVL 13

Expert Comment

by:Jesus Rodriguez
ID: 38322395
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
                    ParseTheEmail(TxtEmail)
          End If
        Next
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
     Next
     
    '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
        xlWorkBook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
0
 

Accepted Solution

by:
amriska earned 0 total points
ID: 38324700
I don't have visual studio
0
 

Author Closing Comment

by:amriska
ID: 38340149
Good Effort
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

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.
Today as you open your Outlook, you witness an error message: “Outlook is using an old copy of your Outlook Data File…”. Probably, Outlook is accessing an old OST file.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Suggested Courses

850 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