Use VBA to open Outlook messages stored in the file system

Last reviewed on December 25, 2013   —  17 comments

An Outlook user posted a question in Outlook Forums:

I need to open Outlook messages stored in a specific folder, and then get the attachments from those Outlook items.

You can do this. You need to use Windows Scripting Host and Outlook's Application.CreateItemFromTemplate to open the messages. Once open, you can save the attachments or do whatever you need to do to the message.

To use this macro, paste the macro in a module, then set a reference to Microsoft Scripting Runtime in the VB Editor's Tools, References dialog box.

Set a reference to the scripting runtime

You'll need to enter the folder path where the MSG files are stored to the GetMSG macro. The folder where you want to save the attachments is stored in strFolderpath in the ListFilesInFolder macro.

Click in GetMSG and press F5 or Run to use the macro.

This code is not Outlook-specific (except for the code between the two Set openMsg lines) and can be used with Word or Excel.


Sub GetMSG()
' True includes subfolders
' False to check only listed folder
   ListFilesInFolder "E:\My Documents\", 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 = "E:\My Documents\attachments\"
    
    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 = 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

How to use the macro

First: You will need macro security set to low during testing.

To check your macro security in Outlook 2010 or 2013, go to File, Options, Trust Center and open Trust Center Settings, and change the Macro Settings. In Outlook 2007 and older, it’s at Tools, Macro Security.

After you test the macro and see that it works, you can either leave macro security set to low or sign the macro.

Open the VBA Editor by pressing Alt+F11 on your keyboard.

To put the code in a module:

  1. Right click on Project1 and choose Insert > Module
  2. Copy and paste the macro into the new module.

More information as well as screenshots are at How to use the VBA Editor

About Diane Poremsky

Diane Poremsky
A Microsoft Outlook Most Valuable Professional (MVP) since 1999, Diane is the author of several books, including Outlook 2013 Absolute Beginners Book. She also created video training CDs and online training classes for Microsoft Outlook. You can find her helping people online in Outlook Forums as well as in the Microsoft Answers and TechNet forums.

Please post long or more complicated questions at Outlook forums by Slipstick.com.

17 responses to “Use VBA to open Outlook messages stored in the file system”

  1. Julie Andersen

    Diane, Your knowledge is really in depth! I appreciate your articles so much. I'm looking for a way to print a custom calendar, with the day's calendar on the left, and task WITH START AND END DATES, sorted by end date, on the right. Can you help?

    1. Diane Poremsky

      What version of Outlook? The Calendar Printing Assistant should be able to do that.

  2. Pradeep Kumar

    Hello Diana,

    I want to ask you something. I have a folder on my computer with email from outlook with attachments. i want to open these emails one by one save email body as pdf with a specific name from a excell file then save attachments either pdf or word or html into pdf with file name from excell again (to a new folder with name from excell), then move to next mail folder. there is a need to open every converted file to pdf.

    1. Diane Poremsky

      it's possible, but i don't have any code samples that use an excel file to provide the file names. See http://www.slipstick.com/developer/code-samples/save-outlook-email-pdf/ to save the messages as pdf.

  3. Pradeep Kumar

    thanks a lot. ok can you give me code leaving excell aside

    1. Diane Poremsky

      I don't have any code samples, other than for the url I posted earlier, sorry.

  4. Pradeep Kumar

    Hello Diane,

    can you give me code to extract files from a password protected zip file stored in hard drive folder to same folder. i have password with with, but i want to automate the process from click of button

    1. Diane Poremsky

      Unfortunately, I haven't worked with password protected zip files and don't have any code samples. Does your zip program support passing the password in a command line? That would be the first requirement.

  5. Charles Andrew

    When I run the code I get the follow error.
    Type run-error '13" Type Mismatch.

    The line of code is:
    Set openMsg = Application.CreateItemFromTemplate(FileItem.Path)

    This appears to happen when the folder getting parsed has a delivery confirmation attached to the .msg file.

    Any suggestion as to what might be causing this?

    Thank you

    1. Diane Poremsky

      it's a delivery confirmation message? Reports are not mailitems - if you want to import them, use Dim openMsg As object or use an if statement to test the item type. Something like
      If TypeOf obj Is Outlook.mailItem Then
      'do the stuff
      end if

    2. Charles Andrew

      Typo, sorry. It's a delivery confirmations.

    3. Diane Poremsky

      Dang, i can't spell tonight. :) change the object that reference the mailitem to object or use the if statement to get only mail.

  6. Eric Bussen

    Greetings Diane,

    I am trying to use this macro and have followed all your instructions. I get an error message that says Run-time error '438' Object doesn't support this property or method and the debugger highlights this line,

    Set openMsg = Application.CreateItemFromTemplate(FileItem.Path)

    I am trying to run this using Excel 2010 and the folders I am using for the .msg files and for the attachments are both on my C drive. I have spent considerable time trying to find the answer on my own, but to no avail. Any suggestions for cause and solution would be greatly appreciated. Thanks!!

    1. Diane Poremsky

      Is the message an email message? If it's a meeting request or a report (read receipt, NDR etc) it won't work.

      Or, if you are using this with Excel, you don't have Outlook properly referenced. It's not quite the right error for a reference problem though.

    2. Marcus

      Since you're running from Excel, add "Outlook.":

      Set openMsg = Outlook.Application.CreateItemFromTemplate(FileItem.Path)

  7. Eric Bussen

    Diane,

    They are .msg files not meeting requests. They have .pdf attachments. The .msg files are however custom forms and perhaps this is why the script is failing.

    You mention that I might not have Outlook properly referenced. How would this be done. I set the reference you stated above regarding Microsoft scripting runtime. I even checked all the other ones you have checked in the example above thinking that might help. Still a fail. I appreciate the assistance and hope you have a good morning. Thanks,

    1. Diane Poremsky

      Are you running the macro from Excel? You need to dim and set Outlook.Application - I'm sure you've done that because you'd get an error about an undefined object otherwise.

      it's possible the custom form is the problem. I'll try to look into it in the morning.

Leave a Reply

If the Post Comment button disappears, press your Tab key.

This site uses XenWord.