Open Outlook Mail .msg file using VBA from Excel

Question

I'm trying to open .msg files from a specified directory using VBA but I keep getting a runtime error.

The code i have:

Sub bla()
    Dim objOL As Object
    Dim Msg As Object
    Set objOL = CreateObject("Outlook.Application")
    inPath = "C:\Users\SiliconPlus\Desktop\Si+ Contact Lists\Contact_Si+"
    thisFile = Dir(inPath & "\*.msg")
    Set Msg = objOL.CreateItemFromTemplate(thisFile)
    ' now use msg to get at the email parts
    MsgBox Msg.Subject
    Set objOL = Nothing
    Set Msg = Nothing
End Sub

Here is the runtime error:

Run-time error '-2147287038 (80030002)':

Cannot open file: AUTO Andy Low Yong Cheng is out of the office (returning 22 09 2014).msg.

The file may not exist, you may not have permission to open it, or it may be open in another program. Right-click the folder that contains the file, and then click properties to check your permissions for the folder.

Have you actually debugged the code to see what the value of `thisFile` is before continuing? — SierraOscar, Jun 18 '15 at 07:44
Click in the grey vertical bar on the left of the code, it'll highlight the line on which the exection will enter debug mode. Then place your mouse on the variable that you wish to see! ;) — R3uK, Jun 18 '15 at 08:31
possible duplicate of [How do I open an outlook .msg file from my harddrive that is NOT in outlook?](http://stackoverflow.com/questions/19383290/how-do-i-open-an-outlook-msg-file-from-my-harddrive-that-is-not-in-outlook) — R3uK, Jun 18 '15 at 08:43

Miguel · Answer 1 · 2017-04-23T21:19:39.217

Kenneth Li You didn't had the full path when opening the file. Try this:

Sub bla_OK()
Dim objOL As Object
Dim Msg As Object
Set objOL = CreateObject("Outlook.Application")
inPath = "C:\Users\SiliconPlus\Desktop\Si+ Contact Lists\Contact_Si+"
thisFile = Dir(inPath & "\*.msg")
'Set Msg = objOL.CreateItemFromTemplate(thisFile)
Set Msg = objOL.Session.OpenSharedItem(inPath & "\" & thisFile)
' now use msg to get at the email parts
MsgBox Msg.Subject
Set objOL = Nothing
Set Msg = Nothing
End Sub

R3uK · Answer 2 · 2015-11-13T08:49:55.243

3

If you get an error, try the Late Biding (Dim Msg As Object) right under the MsgBox (need to be uncommented) :

Sub Kenneth_Li()
    Dim objOL As Outlook.Application
    Dim Msg As Outlook.MailItem
    Msgbox "If you get an error, try the Late Biding right under this (need to be uncommented)"
    'Dim objOL As Object
    'Dim Msg As Object

    Set objOL = CreateObject("Outlook.Application")
    inPath = "C:\Users\SiliconPlus\Desktop\Si+ Contact Lists\Contact_Si+"

    thisFile = LCase(Dir(inPath & "\*.msg"))
    Do While thisFile <> ""

        'Set Msg = objOL.CreateItemFromTemplate(thisFile)
        'Or
        'Set Msg = objOL.OpenSharedItem(thisFile)
        'Set Msg = GetNameSpace("MAPI").OpenSharedItem(thisFile)

        'Eventually with Shell command (here for notepad)
        'Shell "notepad " & thisFile
        Set Msg = objOL.Session.OpenSharedItem(thisFile)


        Msg.display

        MsgBox Msg.Subject
        thisFile = Dir
    Loop


    Set objOL = Nothing
    Set Msg = Nothing
End Sub

Or you can find a nice VB solution there : http://www.mrexcel.com/forum/excel-questions/551148-open-msg-file-using-visual-basic-applications.html#post2721847

And here for more details on Shell method : http://p2p.wrox.com/access-vba/27776-how-open-msg-file-vbulletin.html#post138411

edited Nov 13 '15 at 08:49

answered Jun 18 '15 at 08:26

R3uK

14,417
7
43
77

thx alot R3uk. im currently not able to access outlook library as i am on excel 2007 and it does not contain this library in the references section in vba – Kenneth Li Jun 18 '15 at 09:10
Oh yes of course you can on Office 2007, just Search for `Microsoft Outlook` in the References and if it's not enough : http://stackoverflow.com/questions/24630378/i-need-to-code-for-the-correct-reference-to-ms-outlook-from-excel – R3uK Jun 18 '15 at 09:17
1

im still getting an error Cannot open file: AUTO Andy Low Yong Cheng is out of the office (returning 22 09 2014).msg. The file may not exist, you may not have permission to open it, or it may be open in another program. Right-click the folder that contains the file, and then click properties to check your permissions for the folder. @R3uK it detects the file but its not able to open it. – Kenneth Li Aug 04 '15 at 05:42
Try to rename your file without capitals and retry the code? http://stackoverflow.com/questions/25829685/trying-to-open-an-outlook-email-template-with-excel-vba If this works, rename it with capitals and change code, to include `thisFile = LCase(Dir(inPath & "\*.msg"))` instead of `thisFile = Dir(inPath & "\*.msg")` – R3uK Aug 04 '15 at 09:05
Not working i still get the same error. It just wouldnt open the outlook template from excel. – Kenneth Li Aug 05 '15 at 03:29
When i use OpenSharedItem instead it gives a run-time error 438 Object doesnt support this property or method – Kenneth Li Aug 05 '15 at 04:05
Ok, give it a try now, I think it might be because it is supposed to be used with a NameSpace Object – R3uK Aug 05 '15 at 07:10
Now im getting this same error. Cannot open file: AUTO Andy Low Yong Cheng is out of the office (returning 22 09 2014).msg. The file may not exist, you may not have permission to open it, or it may be open in another program. Right-click the folder that contains the file, and then click properties to check your permissions for the folder. – Kenneth Li Aug 05 '15 at 07:29
has it got anything to do with permissions? – Kenneth Li Aug 05 '15 at 07:29
Maybe, but as it is on your local disk, I think there is something else. See the edited answer (added `.Session` and other solution to open) and let me know! – R3uK Aug 05 '15 at 07:56
same error man. its still telling me that the file may not exist or i need permission to open or it may be open in another program etcetc. this is really sad. – Kenneth Li Aug 05 '15 at 08:00
Ok, so take a look at `Shell` or VB methods that I linked, you might be able to make it work! ;) – R3uK Aug 05 '15 at 08:05
1

I was able to successfully modify this code to use this for my application. However, my code will be used by multiple users who may have different references, so I used Late Binding instead: `Dim objOL As Object` & `Dim Msg As Object`. Worked like a charm! – TheEngineer Nov 12 '15 at 17:07
@TheEngineer : Glad this old post was useful! – R3uK Nov 12 '15 at 17:08
@KennethLi : Can you test Late Binding too? – R3uK Nov 12 '15 at 17:08
@R3uK I've tried using both binding but is not working, Is there any way to surpass this? Thanks. – Eem Jee Nov 16 '16 at 05:17
@ramj : Post the link to your question (with your code and error) here, I'll take a look! ;) – R3uK Nov 16 '16 at 10:20
@R3uK I have same issue with Kenneth Li. Anyways, I've already found some heads up and I've just forgot the link :) Thank you anyway .. :) – Eem Jee Nov 16 '16 at 11:06
@Sauron : How about asking a question or at the very least, try to be a bit more descriptive, rather than just "not working" or downvote? – R3uK May 04 '17 at 07:58

score 1 · Answer 3 · answered Jun 18 '15 at 13:17

1

Another way is to run the file programmatically (in VBA use the Shell command). It will be opened in Outlook where you can get an active inspector window with the item opened.

answered Jun 18 '15 at 13:17

Eugene Astafiev

47,483
3
24
45

keong kenshih · Answer 4 · 2015-06-18T08:30:31.387

0

Try this

Sub GetMSG()
' True includes subfolders
' False to check only listed folder
   ListFilesInFolder "C:\Users\lengkgan\Desktop\Testing", True
End Sub


Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean)
    Dim FSO As Scripting.FileSystemObject
    Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
    Dim FileItem As Scripting.File
    Dim strFile, strFileType, strAttach As String
    Dim openMsg As MailItem

Dim objAttachments As Outlook.Attachments
Dim i As Long
Dim lngCount As Long
Dim strFolderpath As String

'where to save attachments
strFolderpath = "C:\Users\lengkgan\Desktop\Testing"

    Set FSO = New Scripting.FileSystemObject
    Set SourceFolder = FSO.GetFolder(SourceFolderName)

    For Each FileItem In SourceFolder.Files

    strFile = FileItem.Name

' This code looks at the last 4 characters in a filename
' If we wanted more than .msg, we'd use Case Select statement
strFileType = LCase$(Right$(strFile, 4))
  If strFileType = ".msg" Then
    Debug.Print FileItem.Path

Set openMsg = Outlook.Application.CreateItemFromTemplate(FileItem.Path)
openMsg.Display
    'do whatever

Set objAttachments = openMsg.Attachments
    lngCount = objAttachments.Count

    If lngCount > 0 Then

    For i = lngCount To 1 Step -1

    ' Get the file name.
    strAttach = objAttachments.Item(i).Filename

    ' Combine with the path to the Temp folder.
    strAttach = strFolderpath & strAttach

    ' Save the attachment as a file.
    objAttachments.Item(i).SaveAsFile strAttach

    Next i
    End If
  openMsg.Close olDiscard

Set objAttachments = Nothing
Set openMsg = Nothing

' end do whatever
      End If
    Next FileItem
    If IncludeSubfolders Then
        For Each SubFolder In SourceFolder.SubFolders
            ListFilesInFolder SubFolder.Path, True
      Next SubFolder
    End If

    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set FSO = Nothing

End Sub

Edited : How to add the reference
Click Tools > Reference. Check the needed reference enter image description here

edited Jun 18 '15 at 08:30

answered Jun 18 '15 at 07:45

keong kenshih

524
3
8

i get a Compile error: User-defined type not defined with FSO As Scripting.FileSystemObject highlighted, do i need to import anything/libraries etc. – Kenneth Li Jun 18 '15 at 08:04
Sorry my bad, you need add 2 reference : Microsoft Outlook 15.0 Object Library and Microsoft Scripting Runtime. – keong kenshih Jun 18 '15 at 08:11
Click the Tools > References ... at VBA Editor there. I added the snap shot for you to refer. – keong kenshih Jun 18 '15 at 08:31
@keongkenshih Why use a FSO (`Dir` is far lighter) and save all the attachments, when it wasn't asked? – R3uK Jun 18 '15 at 08:42
i only have Microsoft excel 12.0 object library, and Microsoft Office 12.0 Object Library. Where do i download Microsoft Outlook 15.0 Object Library? Sorry > – Kenneth Li Jun 18 '15 at 08:43
I am using Excel 2013, perhaps you could try to include that reference to check whether it could work or not. By right, it should not be a problem. – keong kenshih Jun 18 '15 at 08:46
@R3uK I just gave the example that FSO could work on that, of course Dir is far lighter. – keong kenshih Jun 18 '15 at 08:51
@KennethLi could i know did your problem solve after you add the reference? – keong kenshih Jun 18 '15 at 08:52
im currently on excel 2007 and i couldnt find that reference microsoft outlook 15.0 object library in the references section. is there a place where i can download it or implement it into my my 2007 – Kenneth Li Jun 18 '15 at 09:04
@KennethLi : http://stackoverflow.com/questions/24630378/i-need-to-code-for-the-correct-reference-to-ms-outlook-from-excel – R3uK Jun 18 '15 at 09:17
Perhaps you need using late binding method :http://www.mrexcel.com/forum/excel-questions/396267-microsoft-outlook-12-0-object-library.html – keong kenshih Jun 18 '15 at 09:26

score 0 · Answer 5 · answered Jun 18 '15 at 08:18

0

You should check follow code and can modify your code

Sub CreateFromTemplate() 
Dim MyItem As Outlook.MailItem 
Set MyItem = Application.CreateItemFromTemplate("C:\temp\*.msg") 
MyItem.Display 
End Sub

answered Jun 18 '15 at 08:18

Khamill

51
7

Open Outlook Mail .msg file using VBA from Excel

5 Answers5

Linked