?
Solved

Parsing Information in an email message in outlook to excel

Posted on 2003-02-22
13
Medium Priority
?
325 Views
Last Modified: 2010-05-01
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
Comment
Question by:Charlemagne_
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
13 Comments
 
LVL 4

Expert Comment

by:CareyJ
ID: 8001179
'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
 

Author Comment

by:Charlemagne_
ID: 8001406
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 4

Accepted Solution

by:
CareyJ earned 200 total points
ID: 8001516
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
 
LVL 4

Expert Comment

by:CareyJ
ID: 8001522
Check the folder names aboves...insert your folder names.

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

Author Comment

by:Charlemagne_
ID: 8001623
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
 

Author Comment

by:Charlemagne_
ID: 8001627
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
 
LVL 2

Expert Comment

by:NPluis
ID: 8002402
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
 
LVL 4

Expert Comment

by:CareyJ
ID: 8005665
Uh....Charlemagne...Are you still having problems?
0
 

Author Comment

by:Charlemagne_
ID: 8013661
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
 

Author Comment

by:Charlemagne_
ID: 8077925
Carey thanks for your help!
0
 

Author Comment

by:Charlemagne_
ID: 8077928
Carey thanks for your help!
0
 
LVL 4

Expert Comment

by:CareyJ
ID: 8082694
It worked for you?  Did you accept my answer here?
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

777 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