Export Outlook Folders to the File System


 

I stumbled across a question on Experts-Exchange asking if there’s a way to copy/move an Outlook folder to a folder in the file system. The author would like to drag-and-drop the Outlook folder to the file system folder, but notes that while Outlook allows you to drag a message from Outlook to the file system it does not allow you to do the same with a folder. While I’m not aware of any way to do this via dragging and dropping, it’s easy enough to script a solution that allows a user to select a folder and have it copied/moved to the file system. Here’s my solution.

Using this solution the user starts by selecting an Outlook folder, then run one of two macros: one that copies the folder to the file system, or one that moves it to the file system (copies and then deletes the Outlook folder). Once triggered, the code begins by prompting the user for the file system folder to export to. If the user selects a folder, then the code creates a new folder under the target folder and gives it the same name as the Outlook folder. Next, the code copies the contents of the Outlook folder to the new file system folder saving each item in .msg format. Finally, if the user selected to move the Outlook folder, then the code deletes the selected folder from Outlook. The solution also handles multiple levels of folders. If a folder has sub-folders, then the code copies/moves them to the file system too. If the user opts to move a folder with sub-folders, then the code deletes them too when it deletes the parent folder. Please keep that in mind.

Instructions.

Follow these instructions to add the code to Outlook.

  1. Start Outlook
  2. Press ALT + F11 to open the Visual Basic Editor
  3. If not already expanded, expand Microsoft Office Outlook Objects
  4. If not already expanded, expand Modules
  5. Select an existing module (e.g. Module1) by double-clicking on it or create a new module by right-clicking Modules and selecting InsertModule.
  6. Copy the code from the code snippet box and paste it into the right-hand pane of Outlook’s VB Editor window
  7. Click the diskette icon on the toolbar to save the changes
  8. Close the VB Editor
'On the next line edit the starting folder as desired.  If you leave it blank, then the starting folder will be the local computer.
Const STARTING_FOLDER = ""
 
Dim objFSO As Object
 
Sub CopyOutlookFolderToFileSystem()
    ExportController "Copy"
End Sub
 
Sub MoveOutlookFolderToFileSystem()
    ExportController "Move"
End Sub
 
Sub ExportController(strAction As String)
    Dim olkFld As Outlook.MAPIFolder, strPath As String
    strPath = SelectFolder(STARTING_FOLDER)
    If strPath = "" Then
        MsgBox "You did not select a folder.  Export cancelled.", vbInformation + vbOKOnly, "Export Outlook Folder"
    Else
        Set objFSO = CreateObject("Scripting.FileSystemObject")
        Set olkFld = Application.ActiveExplorer.CurrentFolder
        ExportOutlookFolder olkFld, strPath
        If LCase(strAction) = "move" Then olkFld.Delete
    End If
    Set olkFld = Nothing
    Set objFSO = Nothing
End Sub
 
Sub ExportOutlookFolder(ByVal olkFld As Outlook.MAPIFolder, strStartingPath As String)
    Dim olkSub As Outlook.MAPIFolder, olkItm As Object, strPath As String, strMyPath As String, strSubejct As String, intCount As Integer
    strPath = strStartingPath & "\" & olkFld.Name
    objFSO.CreateFolder strPath
    For Each olkItm In olkFld.Items
        strSubject = RemoveIllegalCharacters(olkItm.Subject)
        strFilename = strSubject & ".msg"
        intCount = 0
        Do While True
            strMyPath = strPath & "\" & strFilename
            If objFSO.FileExists(strMyPath) Then
                intCount = intCount + 1
                strFilename = strSubject & " (" & intCount & ").msg"
            Else
                Exit Do
            End If
        Loop
        olkItm.SaveAs strMyPath, olMSG
    Next
    For Each olkSub In olkFld.Folders
        ExportOutlookFolder olkSub, strPath
    Next
    Set olkFld = Nothing
    Set olkItm = Nothing
End Sub
 
Function SelectFolder(varStartingFolder As Variant) As String
    ' This function is a modified version of the SelectFolder function written by Rob van der Woude (http://www.robvanderwoude.com/vbstech_ui_selectfolder.php)
 
    ' Standard housekeeping
    Dim objFolder As Object, objShell As Object
     
    ' Custom error handling
    On Error Resume Next
 
    ' Create a dialog object
    Set objShell = CreateObject("Shell.Application")
    Set objFolder = objShell.BrowseForFolder(0, "Select the folder you want to export to", 0, varStartingFolder)
 
    ' Return the path of the selected folder
    If TypeName(objFolder) <> "Nothing" Then SelectFolder = objFolder.self.Path
 
    ' Standard housekeeping
    Set objFolder = Nothing
    Set objShell = Nothing
    On Error GoTo 0
End Function
 
Function RemoveIllegalCharacters(strValue As String) As String
    ' Purpose: Remove characters that cannot be in a filename from a string.'
    ' Written: 4/24/2009'
    ' Author:  BlueDevilFan'
    ' Outlook: All versions'
    RemoveIllegalCharacters = strValue
    RemoveIllegalCharacters = Replace(RemoveIllegalCharacters, "<", "")
    RemoveIllegalCharacters = Replace(RemoveIllegalCharacters, ">", "")
    RemoveIllegalCharacters = Replace(RemoveIllegalCharacters, ":", "")
    RemoveIllegalCharacters = Replace(RemoveIllegalCharacters, Chr(34), "'")
    RemoveIllegalCharacters = Replace(RemoveIllegalCharacters, "/", "")
    RemoveIllegalCharacters = Replace(RemoveIllegalCharacters, "\", "")
    RemoveIllegalCharacters = Replace(RemoveIllegalCharacters, "|", "")
    RemoveIllegalCharacters = Replace(RemoveIllegalCharacters, "?", "")
    RemoveIllegalCharacters = Replace(RemoveIllegalCharacters, "*", "") End Function 

Using the Code.

  1. Select an Outlook folder.
  2. Run either the CopyOutlookFolderToFileSystem or the MoveOutlookFolderToFileSystem macro depending on whether you want to copy/move the folder to the file system.

Adding Buttons to Run the Macro with a Single Click

If you want to run the macro with a single click, then you’ll need to add toolbar buttons for each macro in Outlook 2007 or buttons on the Quick Access Toolbar (QAT) for Outlook 2010.

Outlook 2007. Follow these instructions to add toolbar buttons that runs each macro.

Outlook 2010. Follow these instructions to add the macros to the QAT.

Revisions.

Revision 1.

A reader named Chris Robinson suggested a couple of changes which this revision implements. Specifically, Chris suggested changing

  • The file date/time to match that of the original message instead of using the date/time the message was exported.
  • The file name to include the sender’s name

Using this version the file name format is now

[From] Sender’s Name [Subject] Subject of the Message

'On the next line edit the starting folder as desired.  If you leave it blank, then the starting folder will be the local computer.
Const STARTING_FOLDER = ""
 
Dim objFSO As Object
 
Sub CopyOutlookFolderToFileSystem()
    ExportController "Copy"
End Sub
 
Sub MoveOutlookFolderToFileSystem()
    ExportController "Move"
End Sub
 
Sub ExportController(strAction As String)
    Dim olkFld As Outlook.MAPIFolder, strPath As String
    strPath = SelectFolder(STARTING_FOLDER)
    If strPath = "" Then
        MsgBox "You did not select a folder.  Export cancelled.", vbInformation + vbOKOnly, "Export Outlook Folder"
    Else
        Set objFSO = CreateObject("Scripting.FileSystemObject")
        Set olkFld = Application.ActiveExplorer.CurrentFolder
        ExportOutlookFolder olkFld, strPath
        If LCase(strAction) = "move" Then olkFld.Delete
    End If
    Set olkFld = Nothing
    Set objFSO = Nothing
End Sub
 
Sub ExportOutlookFolder(ByVal olkFld As Outlook.MAPIFolder, strStartingPath As String)
    Dim olkSub As Outlook.MAPIFolder, olkItm As Object, strPath As String, strMyPath As String, strSubejct As String, intCount As Integer
    strPath = strStartingPath & "\" & olkFld.Name
    objFSO.CreateFolder strPath
    For Each olkItm In olkFld.Items
        strSubject = "[From] " & olkItm.SenderName & " [Subject] " & RemoveIllegalCharacters(olkItm.Subject)
        strFilename = strSubject & ".msg"
        intCount = 0
        Do While True
            strMyPath = strPath & "\" & strFilename
            If objFSO.FileExists(strMyPath) Then
                intCount = intCount + 1
                strFilename = strSubject & " (" & intCount & ").msg"
            Else
                Exit Do
            End If
        Loop
        olkItm.SaveAs strMyPath, olMSG
        ChangeTimeStamp strMyPath, olkItm.ReceivedTime
    Next
    For Each olkSub In olkFld.Folders
        ExportOutlookFolder olkSub, strPath
    Next
    Set olkFld = Nothing
    Set olkItm = Nothing
End Sub
 
Function SelectFolder(varStartingFolder As Variant) As String
    ' This function is a modified version of the SelectFolder function written by Rob van der Woude (http://www.robvanderwoude.com/vbstech_ui_selectfolder.php)
 
    ' Standard housekeeping
    Dim objFolder As Object, objShell As Object
     
    ' Custom error handling
    On Error Resume Next
 
    ' Create a dialog object
    Set objShell = CreateObject("Shell.Application")
    Set objFolder = objShell.BrowseForFolder(0, "Select the folder you want to export to", 0, varStartingFolder)
 
    ' Return the path of the selected folder
    If TypeName(objFolder) <> "Nothing" Then SelectFolder = objFolder.self.Path
 
    ' Standard housekeeping
    Set objFolder = Nothing
    Set objShell = Nothing
    On Error GoTo 0
End Function
 
Function RemoveIllegalCharacters(strValue As String) As String
    ' Purpose: Remove characters that cannot be in a filename from a string.'
    ' Written: 4/24/2009'
    ' Author:  BlueDevilFan'
    ' Outlook: All versions'
    RemoveIllegalCharacters = strValue
    RemoveIllegalCharacters = Replace(RemoveIllegalCharacters, "<", "")
    RemoveIllegalCharacters = Replace(RemoveIllegalCharacters, ">", "")
    RemoveIllegalCharacters = Replace(RemoveIllegalCharacters, ":", "")
    RemoveIllegalCharacters = Replace(RemoveIllegalCharacters, Chr(34), "'")
    RemoveIllegalCharacters = Replace(RemoveIllegalCharacters, "/", "")
    RemoveIllegalCharacters = Replace(RemoveIllegalCharacters, "\", "")
    RemoveIllegalCharacters = Replace(RemoveIllegalCharacters, "|", "")
    RemoveIllegalCharacters = Replace(RemoveIllegalCharacters, "?", "")
    RemoveIllegalCharacters = Replace(RemoveIllegalCharacters, "*", "")
End Function

Sub ChangeTimeStamp(strFile As String, datStamp As Date)
    Dim objShell As Object, objFolder As Object, objFolderItem As Object, varPath As Variant, varName As Variant
    varName = Mid(strFile, InStrRev(strFile, "\") + 1)
    varPath = Mid(strFile, 1, InStrRev(strFile, "\"))
    Set objShell = CreateObject("Shell.Application")
    Set objFolder = objShell.NameSpace(varPath)
    Set objFolderItem = objFolder.ParseName(varName)
    objFolderItem.ModifyDate = CStr(datStamp)
    Set objShell = Nothing
    Set objFolder = Nothing
    Set objFolderItem = Nothing
End Sub
About these ads

22 comments on “Export Outlook Folders to the File System

  1. Hi There, Not sure if you’re still following this thread, but I’m trying this script with Exchange 2003 and Outlook 2010 (using the first set of code). When I try to export a folder I often get a failures on this line:
    ChangeTimeStamp strMyPath, olkItm.ReceivedTime

    This prevents me from getting through the entire folder tree. I assume this is because it hits a message that it doesn’t like and then craps out. Before it fails it usually gets through a number of messages, folders, and subfolders, so i’m not sure I’ve discovered a pattern.

    FWIW, when I try the revision 1 code, which would be much better for us as it preserves the dates, it fails every time on the first message with an error that says
    Run-time error ‘438’:
    Object doesn’t support this property or method

    And debug takes me to this line of code:
    strSubject = “[From] ” & olkItm.SenderName & ” [Subject] ” & RemoveIllegalCharacters(olkItm.Subject)

    I’m no vb expert, so wondering if you’ve ever seen this before or if an answer to the problem pops quickly to mind.

    If not, any recommendations on other tools that would dump a public folder tree and content to file folders would be very helpful. We’re struggling here.

    Many thanks!
    Adam

    • Hi, Adam.

      Yes, I’m still following this thread.

      I’m a little confused. If I understand correctly, you said that you’re using the original version of the code and are encountering an error on the line

      ChangeTimeStamp strMyPath, olkItm.ReceivedTime
      

      What confuses me is that the original version doesn’t contain that line of code. That line is only in the revision.

      As for the error on this line

      strSubject = “[From] ” & olkItm.SenderName & ” [Subject] ” & RemoveIllegalCharacters(olkItm.Subject)
      

      what type of items are in the folder you’re trying to export?

    • First, thanks for your super-fast reply. You’re right about the original script thing – I meant to say the original version stops here:
      olkItm.SaveAs strMyPath, olMSG

      Sorry about that, I’ve been switching back and forth quite a bit.

      The directory I’m trying to export has read and unread items that include

      · Messages

      · Posts

      · Meeting Requests

      · Meeting Cancellations

      I also got this error when the script hit a conflict message – removing the conflict message seemed to fix that one.

    • Hey, Adam.

      No worries.

      When you say “conflict message” do you mean a sync error message?

      Rev 1 would fail when processing any type of item other than an email. That’s because emails are the only type of item that has a “SenderName” property. We can fix that by changing this line

      strSubject = “[From] ” & olkItm.SenderName & ” [Subject] ” & RemoveIllegalCharacters(olkItm.Subject)
      

      We’d either remove the “SenderName” portion leaving just the subject or remove “SenderName” and use some other piece of data to construct the filename the item will be saved to.

    • Hi David,
      I removed the “[From] ” & olkItm.SenderName from the revised version and it now dumps at the same spot the original does ( at olkItm.SaveAs strMyPath, olMSG) after about 180 items. The next item it would have looked at appears to be a normal message, so I’m not sure why it’s failing there. Any other ideas before I give up?
      Either way, I really appreciate the help!
      Adam

    • Ha, still night time here (I’m in New Caledonia) I think it was a just a normal mail message, though I’m not 100% sure which one it failed on- it looks like it works it’s way sequentially by date so I’m assuming it failed on the first message that’s not in the export.

      Sent from my iPhone

    • Adam,

      Wow, the other side of the planet. Well, it’s morning here on the east coast of the U.S.

      We need to isolate the item that’s failing to understand what’s happening. The two most logical potential causes are that the item is something other than a message or that there’s some character in the subject that’s causing the problem. Here’s the simplest way t find out which item it is.

      1. When the error message appears click Debug.
      2. The code editor should open and the line causing the error will be highlighted in yellow.
      3. Hover your mouse pointer over olkItm.Subject which is a few lines above the line causing the error. The value of that item will appear in a rectangle next tot he mouse pointer.
      4. Jot down the subject, then go find the item with that subject in the folder you’re exporting from.
      5. Note what type of item it is and what the item’s subject is.
      6. Send me those two pieces of information and we’ll take it from there.
  2. Hello David,
    This macro is exactly what I was looking for. Thank you.
    I have one issue though. When saving the mails I sometimes get an error code from the macro. This only happens when the from name contains illigal characters (example: “John Doe (RY/ETM)” ).

    Regards, Bas

    • Hi, Bas.

      You’re welcome! I’m glad the macro is useful.

      What line is the error occurring on and what is the exact error message? The sender’s name isn’t used in the file name, so I don’t think that’s the problem.

  3. Thanks for the scripts – it works great. A couple of enhancements that would make it more useful, 1) store the sender as the author of the email 2) either have an option to copy individual emails (or select multiple) and send to a folder or ignore the fact that the destination folder already exists. With the current implementation, if you copy rather than move, you can’t update again later because the folder already exists – Thank you for your excellent work!

    • Hi, Jason.

      You’re welcome. I’m glad you found the solution useful.

      Thanks for the suggestions. I’m not sure I understand your first suggestion though. When you say “store the sender as the author of the email”. Store it where? Are you saying that you’d like the sender added to the file name of the saved message? I like the idea of enabling the solution to save new messages to an existing folder. When I have time, I’ll modify the code to do that.

  4. All working, thanks David. For my own peace of mind I have commented out the Move options in my copy. Setting the starting folder in line 2 doesn’t seem to do anything – the dialog box opens at Desktop.
    I’ve started to think that if I have time, I might try to add to this code to get user control over the filename – at present the exported folder will be hard to work with, especially as all the files acquire the same date and time of creation so you can’t sort the .msg files by date and time of sending (I don’t know if it’s possible to specify the creation date when saving a file from VBA, but preserving the original date and time would be useful). It should be fairly easy to let the user create the filename format using up to three parameters selected by dropdowns, eg “from Chris Robinson to David Lee 05/10/2013 14:57.msg” or “0001 Re: Export Outlook Folders to the File System from Chris Robinson.msg”. I can see that having applications in creating an integrated folder with other forms of document to create an electronic equivalent of a paper file for a person or transaction. I already have a Word macro that compiles a contents list for a folder as a Word document based on the filenames (hyperlinking the files), so adding emails with sensible filenames would be a big bonus.
    Best, Chris

    • Chris,

      Thanks for pointing that out. When I converted the function from VBscript to VBA I changed the parameter that gets passed into the SelectFolder function from Variant to String. Apparently it has to be Variant to work properly. I’ve corrected the code. Please grab it again or at least replace that one function with the updated version.

      How would you prefer the file name read? I can change it to almost anything.

    • The default folder is working now, thanks.
      On filenames, the main problem for me will be that with all the emails being exported to new files all with the same date and time, and many emails with similar filenames, it will be very hard to know what each one is or to sort them sequentially. So I would probably go for something complicated like “0001[sequential number, mainly to keep them in order, assumes that the export/copy function exports them in ascending date order - does it?] Email from [truncated to 10 characters] to [truncated to 10 characters] “. Very long winded but easier for the user to edit down than to add to the code.
      Best, Chris

    • You’re welcome, Chris.

      How about if I date stamp the exported files with the date the message was received and set the file name to a truncated version of the message subject?

    • Yes, if the datestamp of the file (by which I understand the date shown as the file creation date) was the date and time of sending the email, that would help a lot. It shouldn’t be necessary to truncate the heading, but if there’s an easy was including sender and recipient it would be useful. In essence the problem is that all the emails in a folder are very likely to look very similar (dozens of emails between the same people with the same headings), and anything that helps the user search, automatically or manually, will help, and keeping them in the right order is essential..

  5. David, this is fantastic (why doens’t Oullook have this functionality as standard?) but I had a problem when I tried it. A folder with about 20 messages in it exported as a folder with only four. Fortunately i was using “copy” and not “move”! My best guess at what is happening is that the macro is using the message heading to create the filename for the .msg file, so if the heading is identical on a second message it will just overwrite the first one. there were only four unique headings on messages in the folder so I ended up with only four messages, with the rest discarded.
    For my own use I think I will discard the “move” code, as I want to be sure not to lose anything accidentally, and I can easily delete from Outlook manually if required.
    Best, Chris

    • Chris,

      Thanks! That’s a good question.

      Good catch on the file name. I’ll modify the code to make each file name unique. What do you think would be the best solution to that, adding a number to file name (e.g. Copy 1 of – some subject) or adding the date/time the message was received (e.g. Some Subject – Received on 2013/5/9 09:59 AM)?

    • Just adding a number to the filename, I would think – if you drag and drop multiple messages from Outlook to the file system (I didn’t know you could do this till I read your post, so thanks for that) it appends (1), (2) etc. But the date and time would work well too and might be more useful – especially if the (1), (2) were not in date/time order – but there should then be a trap in case two messages had the same time, which they often would if to the nearest minute.

    • Chris,

      I just updated the code to add a number to the file name if a file with that name already exists. Please replace the code you have now with the updated code and let me know if that works for you.

      Cheers!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s