Read email body from Excel

[FractalWalk]

I am an experienced VBA user in Excel but I have never written anything for outlook. I have a local folder that contains individual email files that I need to open using Excel 2007 VBA

My problem is I don't know how to open the email files in a format to read them. For example, if it were plain text files I would use:

Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder("TargetFolder")
For Each fil In fld.Files
Open fil For Input As #1
Next fil

I assume I have to open an OUtlook object and reference the folder, but how do I open the file and read it? I assume it would be something like the following but with appropriate code.

Set appOutlook = CreateObject("Outlook.Application")
Set fld = appOutlook.GetFolder("TargetFolder")
For Each eml In ?
Code to open email
Next fil

I have reasearched the Outlook.Application object but none of what I read helps me reference it from Excel.

 

[fumei]

Hey there Fractal. You may want to take a look here fro a starter on Outlook programming.

The object you seem to be looking for is MailItem.

Gerry

 

[FractalWalk]

Thanks fumei.

I have already looked at that and it was no help to me. Those code examples do not seem to work in Excel which is what I am using.

I just want to reference a folder name on my local drive that contains email files (not an Outlook folder) and I was hoping that I could reference the Outlook.Application object to run some type of "open" command for the files. Similar to how I would reference Workbooks.Open commmand for the Excel.Application.

 

[fumei]

I have already looked at that and it was no help to me. Those code examples do not seem to work in Excel which is what I am using. "

Really. I just tested some examples from Excel, and they seem to work.

"I just want to reference a folder name on my local drive that contains email files (not an Outlook folder) and I was hoping that I could reference the Outlook.Application object to run some type of "open" command for the files. Similar to how I would reference Workbooks.Open commmand for the Excel.Application."

Ah, I see. Well - perhaps unfortunately, perhaps not - Outlook is a different kettle of fish from Excel. Probably for good reason. Its use of an application instance is different.

You should be able to do this. Let me see. In the meantime someone else may have the answer faster.

Gerry

 

[Gruuuu]

This is what I had to do:

Sub test()
Dim oapp As Object
Dim ns As Object
Dim fld As Object
Dim wfld As Object
Dim eml As MailItem

Set oapp = CreateObject("Outlook.Application")
Set ns = oapp.GetNamespace("MAPI")

For Each fld In ns.Folders
  If Left(fld.Name, 7) = "Target Folder" Then
    Set wfld = fld
  End If
Next fld

Set fld = wfld.Folders("Report Files")

For Each eml In fld.Items
  [i]MsgBox eml.Body[/i] //or whatever
Next eml

End Sub

Basically, you have to define which default folder your specific folder is in. The default folders are in the MAPI folder list. Your folder is likely a subfolder of that list. If your folder is in your main default folder area (i.e. same level as your Inbox), then you gotta specify your mailbox. My mailbox happens to be named "Mailbox, <Firstname> <M.> <Lastname>". Once you figure out how you're specifying the mailbox, you can reference the folder by name.

 

[Gruuuu]

If Left(fld.Name, 7) = "Target Folder" Then

Whoops, that was supposed to read

If Left(fld.Name, 7) = "Mailbox" Then

And the TargetFolder was supposed to be the Folder reference Sorry.

 

[Gruuuu]

Actually I'm glad you asked this. I've been thinking about creating a process that does something very similar (except I also need it to open attachments, if any, and gather the appropriate info from that.)

Now if I could only get my people to send information in a standard format...

 

[Gruuuu]

Wow, I didn't read your reply to fumei, I'm sorry
What format are the emails saved in?

 

[FractalWalk]

The files are standard email files but they are not in the Outlook folder heirarchy. They are saved into an external folder. So ns = oapp.GetNamespace("MAPI") does not help as it does not recognize the folder I care about.

When I try to assign the folder directly using the FileSystemObject then the files aren't recognized as MailItem objects. And when I try to assign it using the Outlook object it doesn't work either.

Both of these error out

Set oapp = CreateObject("Outlook.Application")
Set fld = oapp.GetFolder(TargetFolder)
For Each eml In fld.Items
MsgBox (eml.Body)
Next eml

Set fso = CreateObject("Scripting.FileSystemObject")
Set oapp = CreateObject("Outlook.Application")
Set fld = fso.GetFolder(TargetFolder)
For Each eml In fld.Items
MsgBox (eml.Body)
Next eml

 

[mintjulep]

Possible solution almost at the bottom here:

http://bytes.com/topic/visual-basic-net/answers/358054-reading-outlook-msg-files

 

[FractalWalk]

After some extensive searching I found a solution using the CreateItemFromTemplate method as opposed to some type of Open method.

Set fso = CreateObject("Scripting.FileSystemObject")
Set oapp = CreateObject("Outlook.Application")
Set fld = fso.GetFolder(TargetFolder)
For Each fil In fld.Files
Set eml = oapp.CreateItemFromTemplate(fil)
MsgBox (eml.Body)
Next fil