Parsing Information in an email message in outlook to excel

You should use CDO check this link and it should get you on the way :

http://search.microsoft.com/gomsuri.asp?n=1&c=rp_BestBets&siteid=us/dev&target=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdo/html/_olemsg_overview_of_cdo.asp?frame=true

'Add the Microsoft MapiSession and MapiMessage
'Component to your project
'The Sub below Retrieves a sorted collection of unread
'messages from your Outlook Inbox and if an email has
'been received from 'the sender', then it will save the email into the output file.

Private Sub Save_EMail()
    Dim MIndex As Integer
    Dim MsgDate As Date
    Dim MsgSubject As String
    Dim MsgBody As String
    Dim OutputFile As String
    OutputFile = "C:\Saved_EMail.csv"
    If Dir(OutputFile) <> "" Then
        Open OutputFile For Output As #1
    Else
        Open OutputFile For Output As #1
    End If
    If MAPISession1.SessionID = 0 Then MAPISession1.SignOn
    With MAPIMessages1
        .SessionID = MAPISession1.SessionID
        .FetchSorted = True
        .FetchUnreadOnly = True
        .Fetch
        For MIndex = 0 To .MsgCount - 1
            If .MsgOrigAddress = "<Sender's email address>" Then
                MsgDate = .MsgDateReceived
                MsgSubject = .MsgSubject
                MsgBody = .MsgNoteText
                Print #1, MsgSubject & "," & MsgDate & "," & MsgBody
                .MsgIndex = .MsgIndex + 1
            End If
        Next MIndex
    End With
    Close #1
    MAPISession1.SignOff
End Sub

Hi!
I got a pretty dumb question here. Right now I am working in the VBA in outlook. Hence, I am not able to find the Microsoft MapiSession and MapiMessage Component you were talking about.

I was trying out with some code and i managed to read the body of the message of an email in the mailbox. However, I am not sure how can i parse the information within the email into an excel spreadsheet. Any help on this will be greatly appreciated.

here is what i got till now... its still in the experimental stage so i didnt really declare any variables... Please give me some comments on this though =)

Set Myolapp = CreateObject("Outlook.Application")
Set myNameSpace = Myolapp.GetNamespace("MAPI")
Set myFolder = myNameSpace.Folders("Hotmail")
Set myNewFolder = myFolder.Folders("Confirmed")
Set Myolapp.ActiveExplorer.CurrentFolder = myNewFolder
a = myNewFolder.Items(1).Body

Check the folder names aboves...insert your folder names.

That will save all unread messages in that folder to the output file.

Hi!
I got a pretty dumb question here. Right now I am working in the VBA in outlook. Hence, I am not able to find the Microsoft MapiSession and MapiMessage Component you were talking about.

I was trying out with some code and i managed to read the body of the message of an email in the mailbox. However, I am not sure how can i parse the information within the email into an excel spreadsheet. Any help on this will be greatly appreciated.

here is what i got till now... its still in the experimental stage so i didnt really declare any variables... Please give me some comments on this though =)

Set Myolapp = CreateObject("Outlook.Application")
Set myNameSpace = Myolapp.GetNamespace("MAPI")
Set myFolder = myNameSpace.Folders("Hotmail")
Set myNewFolder = myFolder.Folders("Confirmed")
Set Myolapp.ActiveExplorer.CurrentFolder = myNewFolder
a = myNewFolder.Items(1).Body

Hi!
I got a pretty dumb question here. Right now I am working in the VBA in outlook. Hence, I am not able to find the Microsoft MapiSession and MapiMessage Component you were talking about.

I was trying out with some code and i managed to read the body of the message of an email in the mailbox. However, I am not sure how can i parse the information within the email into an excel spreadsheet. Any help on this will be greatly appreciated.

here is what i got till now... its still in the experimental stage so i didnt really declare any variables... Please give me some comments on this though =)

Set Myolapp = CreateObject("Outlook.Application")
Set myNameSpace = Myolapp.GetNamespace("MAPI")
Set myFolder = myNameSpace.Folders("Hotmail")
Set myNewFolder = myFolder.Folders("Confirmed")
Set Myolapp.ActiveExplorer.CurrentFolder = myNewFolder
a = myNewFolder.Items(1).Body

try something like this and make sure you reference the excel lib. (tools->references)


Dim MyolApp As Outlook.Application
Dim myNameSpace As NameSpace
Dim myFolder As MAPIFolder
Dim a As String
Dim myNewFolder As MAPIFolder

Set MyolApp = CreateObject("Outlook.Application")
Set myNameSpace = MyolApp.GetNamespace("MAPI")
Set myFolder = myNameSpace.Folders("Stephen Fegel")
Set myNewFolder = myFolder.Folders("Inbox")
Set MyolApp.ActiveExplorer.CurrentFolder = myNewFolder
a = myNewFolder.Items(1).Body

Dim objExcel As Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objSheet As Excel.Worksheet

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Add
Set objSheet = objWorkbook.Sheets.Add
objSheet.Cells(1, 1) = a

objExcel.Visible = True


afterwards clean everything like:
objWorkbook.Close
objExcel.Quit
Set objWorkbook = Nothing
Set objExcel = Nothing
Set myNewFolder = Nothing
Set myFolder = Nothing
Set myNameSpace = Nothing
Set myolapp = Nothing

is that what you want?

Uh....Charlemagne...Are you still having problems?

Hi Carey! Sorry for the delay, I was out for the entire day for yesterday and today. I will work on my code now right away. Thanks for your help! I will get back to you tomorrow if i face any difficulties.

Carey thanks for your help!

Carey thanks for your help!

It worked for you?  Did you accept my answer here?