Extract outlook message body text with VBA from Excel

[jamsta1972]

Hi all,

I have a number of Outlook .msg files saved to a shared folder (ie outside of Outlook). I am trying to write some VBA in Excel that extracts the message body text from each in turn (as a string)...and then does some other stuff with the contents of the string.

I've done a similar thing with word documents but I'm struggling to 'get at' the text in the .msg files.

So far I have the code below. I like to think I'm on the right track at least, but I'm stuck at the line where I'm trying to set up a reference to the msg file. Any advice will be appreciated...

    Dim MyOutlook As Outlook.Application
    Dim MyMail As Outlook.MailItem
    
    Set MyOutlook = New Outlook.Application
    
    
    Set MyMail = '? stuck here!
    
    Dim FileContents As String
    
    FileContents = MyMail.Body

Thanks

[snb]

Sub email_ontvangen_lezen() 
  for each bt in CreateObject("Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(6).Items 
    c01 = c01 & "_" &  .body
  next
  sheets(1).cells(1)=c01
End Sub

[snb]

Sub email_ontvangen_lezen() 
  for each bt in CreateObject("Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(6).Items 
    c01 = c01 & "_" &  bt.body
  next
  sheets(1).cells(1)=c01
End Sub

[jamsta1972]

Thanks snb, but I have to admit I don't understand what this code is doing, so I'm not sure how I can make use of it!

[snb]

It looks in evey post in the PostIN folder and reads the bodytext; puts it into the variable c01 and eventually writes all bodytexts in cell A1.

[jamsta1972]

Sorry snb I think I haven't been clear about my task. The email messages I need to extract from are in a windows folder on a shared drive, so they are not in an email folder within Outlook. These are emails from various sources that different users have dragged into the folder from outlook.

[OnErrorGoto0]

I am not sure but you may be able to use the CreateItemFromTemplate method in outlook to create an email based on the saved .msg file, then simply grab the Body property of that item and discard the item.

[snb]

In line with @onerrors's idea: why don't you let those users send their emails to you. With the code I provided you can read the bodytext of those mails.

But if you insist in using the msg files:
You can open them simply using:

sub snb()
   Shell "explorer.exe G:\OF\message.msg"
End Sub

[OnErrorGoto0]

In line with @onerrors's idea: why don't you let those users send their emails to you.

I do not to be honest see how that relates at all to what I was suggesting?
I am suggesting automating Outlook and using the stored .msg files as templates to create a copy of the stored emails so that the asker can read the body in code. It has the advantage over Shell that you get a direct reference to that email - I am not sure how you would do that with Shell?

[jamsta1972]

Hello both,

I used "Set MyMail = MyOutlook.CreateItemFromTemplate(fileName)" as suggested and this worked perfectly. Thanks very much for this advice.

Yes I realise it's strange I'm accessing these in a shared folder. It's because I'm getting information in a number of forms, sometimes in the body of an email, sometimes in text files, and sometimes in word documents. So I wanted to be able to dump all the different file types in one folder and then automate the text extraction for them all at once. So this why I'm not doing it from the body of emails within outlook.

thanks again.