• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • Last Modified:

Parsing Information in an email message in outlook to excel

Hi!
I am currently using Office XP.
I have been receiving emails that follows a certain format regularly through a POP mail system. In the emails there are certain fields which i would like to extract and place into an excel spreadsheet. I would like to write a program that will check the mailbox for unread emails, and parse the required fields from new emails into the spreadsheet.

I have a little experience in programming in excel VBA but i am totally unfamiliar with the outlook object library. Hence, I would appreciate it if someone can provide me with the directions i should take to reach my objective. I do not need a sample code but rather, i need to have assistance on how do i approach the problem and the keywords (ie Outlook methods or properties as well as how to insert the fields into excel from outlook) that i should look up on.

Many thanks!
Charlemagne
0
Charlemagne_
Asked:
Charlemagne_
  • 6
  • 5
  • 2
1 Solution
 
CareyJCommented:
'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
0
 
Charlemagne_Author Commented:
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
CareyJCommented:
MapiSession and MapiMessages are Mapi controls for VB.
Forget that....try this:

    Dim MsgSender As String
    Dim MsgDate As String
    Dim MsgSubject As String
    Dim MsgBody As String
    Dim MIndex As Integer
    Set Myolapp = CreateObject("Outlook.Application")
    Set myNameSpace = Myolapp.GetNamespace("MAPI")
    Set myFolder = myNameSpace.Folders(1)
    Set myNewFolder = myFolder.Folders("Carey")
    Dim OutputFile As String
    OutputFile = "C:\Saved_EMail.csv"
    If Dir(OutputFile) <> "" Then
        Open OutputFile For Output As #1
    Else
        Open OutputFile For Append As #1
    End If
    For MIndex = 1 To myNewFolder.Items.Count
        If myNewFolder.Items(MIndex).UnRead = True Then
            MsgSender = myNewFolder.Items(MIndex).SenderName
            MsgDate = myNewFolder.Items(MIndex).SentOn
            MsgSubject = myNewFolder.Items(MIndex).Subject
            MsgBody = myNewFolder.Items(MIndex).Body
            Print #1, MsgSender & "," & MsgDate & "," & MsgSubject & "," & MsgBody
        End If
    Next i
    Close #1

0
 
CareyJCommented:
Check the folder names aboves...insert your folder names.

That will save all unread messages in that folder to the output file.
0
 
Charlemagne_Author Commented:
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
0
 
Charlemagne_Author Commented:
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
0
 
NPluisCommented:
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?


0
 
CareyJCommented:
Uh....Charlemagne...Are you still having problems?
0
 
Charlemagne_Author Commented:
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.
0
 
Charlemagne_Author Commented:
Carey thanks for your help!
0
 
Charlemagne_Author Commented:
Carey thanks for your help!
0
 
CareyJCommented:
It worked for you?  Did you accept my answer here?
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 6
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now