How to open an Outlook .msg file in Excel VBA?

[ncarty97]

Hello everyone and thank you in advance for any assistance you can render!

I am trying to write a macro to take the subject and a few lines out of the body from an email, and dump it into an excel file. I can't write the marco directly in Outlook's VBA, because my company has that blocked. So what I have done is created a folder on my desktop and dragged the appropriate emails into that folder, which creates a seperate .msg file for each folder.

What I'd like to do is then go into excel and have a macro that opens each file in that folder, grabs the subject, then searches through the text of the subject for the information I need and grabs that.

I tried using this method:

http://help.lockergnome.com/office/Open-msg-File-Attachment-details-ftopict708121.html

Which partially worked. I could get any information I wanted, except the subject! The messages that I am trying to import are in plain text. I did a test with an RTF message, and I could see the text under the textbody property, and with an html message, under the htmlbody. But for plain text, it didn't seem to work.

So, anyone have any better ideas for this? Thanks again.

Nate

[rory]

Are you after the subject or the body text? Also, how automated does this have to be - will you be able to answer the Outlook security prompts?

[ncarty97]

Well, the goal is to automate it as much as possible because we are dealing with about 50+ emails each day that we need to pull this information from.

Security prompts have not been a problem (we are using Outlook 2000 if that means anything), which might be because we are dragging the messages into a temp folder on the hard drive before I run my code to grab the data.

I need both the subject (which I have had no problem getting) and the body (which is what I can't seem to get).

Thanks

[ncarty97]

" Which partially worked. I could get any information I wanted, except the subject!"

Ok, in my first post, I messed this up. I meant to say that I could get any information I wanted except the body, not the subject.

[m62.aryan]

it seems the primary challenge is in Excel VBA. Below is a simplified example of how one might approach this task using the Outlook object model:



Sub ExtractEmailData()
Dim objOutlook As Object
Dim objNamespace As Object
Dim objFolder As Object
Dim objItem As Object
Dim msgFileName As String
Dim excelApp As Object
Dim excelWorkbook As Object
Dim excelWorksheet As Object
Dim emailSubject As String
Dim emailBody As String

' Set the path to the folder containing .msg files
' Replace "C:\Your\Folder\Path" with the actual path
Const folderPath As String = "C:\Your\Folder\Path"

' Create Outlook and Excel objects
Set objOutlook = CreateObject("Outlook.Application")
Set objNamespace = objOutlook.GetNamespace("MAPI")

' Set the Outlook folder
' You may need to adjust the folder path
Set objFolder = objNamespace.GetNamespace("MAPI").GetFolderFromID("&H0AAAAFAE5B76CF11A86800AA00B9A0E8", "&H1EA369A")

' Create Excel objects
Set excelApp = CreateObject("Excel.Application")
excelApp.Visible = True ' Show Excel application

' Create a new workbook
Set excelWorkbook = excelApp.Workbooks.Add
' Assuming data will be placed in the first worksheet
Set excelWorksheet = excelWorkbook.Worksheets(1)

' Loop through .msg files in the folder
msgFileName = Dir(folderPath & "\*.msg")
Do While msgFileName <> ""
' Open the .msg file
Set objItem = objOutlook.CreateItemFromTemplate(folderPath & "" & msgFileName)

' Extract subject and body
emailSubject = objItem.Subject
emailBody = objItem.Body

' Place data in Excel worksheet
' You may need to adjust the column numbers
excelWorksheet.Cells(excelWorksheet.Rows.Count, 1).End(-4162).Offset(1, 0).Value = emailSubject
excelWorksheet.Cells(excelWorksheet.Rows.Count, 2).End(-4162).Offset(1, 0).Value = emailBody

' Move to the next .msg file
msgFileName = Dir
Loop

' Clean up objects
Set objItem = Nothing
Set objFolder = Nothing
Set objNamespace = Nothing
Set objOutlook = Nothing

Set excelWorksheet = Nothing
Set excelWorkbook = Nothing
Set excelApp = Nothing
End Sub



Please note:


1. You need to replace "C:\Your\Folder\Path" with the actual path where your .msg files are located.
2. The script assumes that the Outlook folder is already known. You may need to adjust the folder path in the `Set objFolder` line.
3. This script places the extracted subject and body in the first two columns of the first worksheet in the newly created Excel workbook. You can modify the column numbers and Excel-related code based on your specific requirements.


Remember to test this script on a small set of files first to ensure it meets your needs and adjust it accordingly.

[georgiboy]

This thread is 5833 days old

[Aussiebear]

You are right Georgiboy. A thread this old, has little value. Mind you m62ayran made a serious mistake in adding a spam link with their post. Hence they are missing in future posts.