C.Wimberley – Custom Excel Export


I’ve gotten quite a few comments to my Exporting Outlook Messages to Excel post. One of those comments came from a gent named Chris Wimberley. Chris is looking for a way to export a specific set of Outlook folders from a group of mailboxes to Excel on a scheduled basis. He says he needs to do this for “around 30 resource mailboxes” where each mailbox has “between 30 – 50 folders” nested as many as “4 levels” deep. In addition to some basic message information (e.g. subject, sender) Chris also needs to perform two age calculations: the age, in days, since the message arrived and since it was last modified. For each run the process needs to create a new workbook and all the data needs to be written to a single sheet. Chris goes on to say that he’s currently using several different Excel files to pull the information.

My solution is to use VBScript to automate the process entirely. All Chris will need to do is create a recurring task using Windows Task Scheduler that runs the script at whatever interval he needs. The script itself is really very simple. Create a few variables, connect to Outlook, open Excel and create a new workbook, write headers to the first sheet in the new book, process the list of folders using a recursive procedure call, save the workbook, and finally close everything down. For the two calculations I’ve used VBScript’s built-in DateDiff function to get the number of days between the message’s arrival date/modified date and the date the script is being run on.

Hopefully this will take of Chris’ issue. Chris, and anyone else who uses this, needs to be sure and test the script to make sure it works properly before attempting to use it in a production setting. I tested the code prior to posting it here and I believe it’s working properly, but that does not mean it will work properly for you. Test it first!

Instructions.

  1. Open Notepad.
  2. Copy the code below and paste it into Notepad.
  3. Edit the code. I placed comments where things can/should change.
  4. Save the file. You can name it anything you want. The file extension must be .vbs
'Create some constants
'On the next line edit the path and root name of the workbook the process is to create. The root filename will have -dd.mmm.yyyy.xlsx appended to it.
Const BASE_FILENAME = "c:\Users\<username>\Documents\Outlook-"

'Create some variables
Dim olkApp, olkSes, excApp, excWkb, excWks, lngRow

'Initialize variables
lngRow = 2

'Connect to Outlook
Set olkApp = CreateObject("Outlook.Application")
Set olkSes = olkApp.GetNamespace("MAPI")
olkSes.Logon olkApp.DefaultProfileName

'Connect to Excel
Set excApp = CreateObject("Excel.Application")
Set excWkb = excApp.Workbooks.Add()
Set excWks = excWkb.WorkSheets(1)

'Write the column headers in Excel
'Edit the headers as desired but do not rearrange the columns.
With excWks
    .Cells(1,1) = "Folder"
    .Cells(1,2) = "Received"
    .Cells(1,3) = "Age"
    .Cells(1,4) = "Last Modified"
    .Cells(1,5) = "Age Since Modified"
    .Cells(1,6) = "Sender"
    .Cells(1,7) = "Subject"
End With

'Run through the folders.
'On the next line edit the path to the root folder you want to export. That folder and all its sub-folders will be processed.  Add an additional line for each top-level folder you want to process.
ProcessFolder "Mailbox - Doe, John\Inbox"

'Close Excel
excWkb.SaveAs BASE_FILENAME & Day(Date) & "." & MonthName(Month(Date),True) & "." & Year(Date) & ".xlsx"
Set excWks = Nothing
Set excWkb = Nothing
excApp.Quit
Set excApp = Nothing

'Close Outlook
olkSes.Logoff
Set olkSes = Nothing
olkApp.Quit
Set olkApp = Nothing

'End Processing
WScript.Quit

Sub ProcessFolder(strFolderPath)
    Dim olkFld, olkItm, olkSub
    Set olkFld = OpenOutlookFolder(strFolderPath)
    If TypeName(olkFld) <> "Nothing" Then
        For Each olkItm In olkFld.Items
            excWks.Cells(lngRow,1) = olkItm.Parent.FolderPath
            excWks.Cells(lngRow,2) = olkItm.ReceivedTime
            excWks.Cells(lngRow,3) = DateDiff("d", olkItm.ReceivedTime, Now)
            excWks.Cells(lngRow,4) = olkItm.LastModificationTime
            excWks.Cells(lngRow,5) = DateDiff("d", olkItm.LastModificationTime, Now)
            'Change SenderEmailAddress to Sender if you want the name instead of address
            excWks.Cells(lngRow,6) = olkItm.SenderEmailAddress
            excWks.Cells(lngRow,7) = olkItm.Subject
            lngRow = lngRow + 1
        Next
        For Each olkSub In olkFld.Folders
            ProcessFolder olkSub.FolderPath
        Next
    End If
    Set olkItm = Nothing
    Set olkSub = Nothing
End Sub

Function OpenOutlookFolder(strFolderPath)
    Dim arrFolders, varFolder, bolBeyondRoot
    On Error Resume Next
    If strFolderPath = "" Then
        Set OpenOutlookFolder = Nothing
    Else
        Do While Left(strFolderPath, 1) = "\"
            strFolderPath = Right(strFolderPath, Len(strFolderPath) - 1)
        Loop
        arrFolders = Split(strFolderPath, "\")
        For Each varFolder In arrFolders
            Select Case bolBeyondRoot
                Case False
                    Set OpenOutlookFolder = olkSes.Folders(varFolder)
                    bolBeyondRoot = True
                Case True
                    Set OpenOutlookFolder = OpenOutlookFolder.Folders(varFolder)
            End Select
            If Err.Number <> 0 Then
                Set OpenOutlookFolder = Nothing
                Exit For
            End If
        Next
    End If
    On Error GoTo 0
End Function

Useage.

To use this solution

  1. You can run it manually by double-clicking the saved .vbs file.
  2. To automate running it
    • Launch Windows Task Scheduler.
    • Create a new task.
    • Set the task to run the script
    • Set the task to run however often you need. Note that the workbook the script creates has the day in the filename. If you set the task to run more than once a day, then it will overwrite the file each time after the first run of the day.
Advertisements

24 comments on “C.Wimberley – Custom Excel Export

  1. Hi David and thanks again, although…I have a few issues getting this to run – perhaps you could advise where I’m going wrong?

    I’m trying with just 3 of the mailboxes to begin with – mailbox names are formatted along the lines of “Department, SubBusiness-Site (Parent Business)” – these are the display names, and some do contain special characters like “~” and/or “/”.

    If I use either ProcessFolder “Department, SubBusiness-Site (Parent Business)” or ProcessFolder “Mailbox – Department, SubBusiness-Site (Parent Business)”, then the Excel sheet is created with the column headings, but no data. Whereas if I use ProcessFolder “Department, SubBusiness-Site (Parent Business)\Inbox”, the script throws the following error…

    Line: 64
    Char: 13
    Error: One or more items in the folder you synchronized do not match. To resolve the conflicts, open the items and then try this operation again.
    Code: 80020009
    Source: Microsoft Outlook

    Any thoughts much appreciated, as ever!
    Chris

    • Those don’t look like valid Outlook paths. Paths in Outlook are just like paths in the file system with the exception that an Outlook path doesn’t include a drive letter. In short, a path is list of all the folder names from the root folder to the target folder with each level separated by a “\”. Here’s an example


      Mailbox - Doe, John
      - Calendar
      - Inbox
      - Tasks
      Personal Folders
      + Marketing
      + Proposals
      + Reviews
      + Projects
      + Project 1
      + Project 2

      The path to "Inbox" is "Mailbox - Doe, John\Inbox".
      The path to "Reviews" is "Personal Folders\Marketing\Reviews".
      The path to "Project 1" is "Personal Folders\Projects\Project 1".

    • Still plugging away!

      What I listed above are the mailbox names. If I run it as “\\Department, SubBusiness-Site (Parent Business)\Inbox” (including “\\” at the front of the mailbox name) then it retrieves whatever’s in the Inbox beautifully, it just doesn’t recurse through the subfolders of the Inbox.

      However, If I manually add the subfolders by name, then it retreives the emails – the paths look like this: “\\Department, SubBusiness-Site (Parent Business)\Inbox\Level1\Level2\Level3”.

      Is there away to make it go through everything that is present in outlook, rather specifying specific folders?

      Sorry to be a pain – really appreciate your help!

      Chris

    • I see the problem. When I copied the code over I cut off part of a line. Line 69 should read

      ProcessFolder olkSub.FolderPath
      [\code]

      instead of

      ProcessFolder olkSub
      [\code]

      Change that line and you’re in business. I’ll fix the code in the post.

    • That’s got it! You’re a star, thank you David!

      So, if I point it directly to, say, a level 3 folder then it goes through that and all the level 4 subfolders. However, if I point it straight to the Inbox, it throws an error.

      I’ll work my way up through the levels later to try and identify where it’s falling over – could it be something to do with empty folders or ‘non’ email items?

    • You’re welcome, Chris.

      Yes, it will start at whatever level you point it to and work from there down. The error is probably being caused by a non-email. I can change the code by adding a test and ignoring non-email items. Let me know.

    • Ah-ha!

      I just threw in a cheeky “on error resume next” here:
      Set olkFld = OpenOutlookFolder(strFolderPath)
      on error resume next
      If TypeName(olkFld) “Nothing” Then…

      That seems to have done it – looking at the resultant Excel file, it seems to be an issue with resolving the sender for one email in the mailbox.

      This is great – I know I sound like a stuck record (if such things still exist), but you’ve been a great help! David for MVP!

    • An “On Error Resume Next” statement is the quick and easy way around the problem. Is the sender in question an internal sender? If so, are you on an Exchange system?

    • Hi David,

      Yes, we’re on Exchange (2010 I think).

      It seems that it was tripping up on a conflict message. I also have an issue with a mailbox that has “/” in it’s name – the script will read the inbox on this one but not subfolders (the “/” is being replaced by “%2”) when it reads the inbox.

    • Sure – the mailbox name is “~CONS/IND Sales Insp Tech DE_FILM”.

      So I’m using ProcessFolder “~CONS/IND Sales Insp Tech DE_FILM\Inbox” (the “\\” isn’t required to be hard coded since the code update – it’s pulled in as below).

      It’s reading it in as “\\~CONS%2FIND GEIT RVI Service\Inbox”

    • It appears that it’s Outlook that’s changing the “/” character to “%2”. Right click on the Inbox folder under that mailbox or any of the sub-folders under Inbox and select Properties. Now, look at “Location”. Does it contain the “%2”? It does on my system when I create a folder with that name.

    • Hi David,

      Yes, it’s the same on my system. I’ve played with this for a couple of hors and arrived at the follow – it seems to work:

      If InStr(strFolderPath, “%2F”) Then
      strFolderPath = Left(strFolderPath, InStr(strFolderPath, “%2F”)-1) & “/” & Right(strFolderPath, Len(strFolderPath)-InStr(strFolderPath, “%2F”)-2)

      Loving this and will continue to tweak – thank you!
      Chris

    • Well, the script is working great!

      Unfortunately, when I try to run it with all 30 mailboxes mapped in outlook I hit memory issues (“insufficient memory to complete the operation”). I’ve experienced this using the old excel reports too and also via the outlook client itself, so it’s nothing to do with the script.

      Now I’m at the mercy of our IT department to try and figure this out… 😦

    • Glad the script is working. You could try setting up different profiles and splitting the mailboxes across them. I could modify the script to connect to the different profiles.

    • I did wonder if it’s possible to connect to a different profile on the line: olkSes.Logon olkApp.DefaultProfileName, but haven’t got around to investigating.

      I’m having to get my system rebuilt to see if it will improve the memory issues!

    • If the rebuild doesn’t help, then consider splitting the mailboxes up into two or three profiles. Yes, the Logon command takes a profile name. We could alter the script to take the profile name as a parameter.

    • Thanks David, I will.

      I’d be very interested to learn how to specify the profile name if you could advise?

    • To specify a specific profile in the code change this line

      olkSes.Logon olkApp.DefaultProfileName
      

      to

      olkSes.Logon "Some Profile Name"
      

      Passing the profile name to the script as a parameter requires using the Arguments collection of the WScript object Like this

      If WScript.Arguments.Count = 1 Then
          strProfileName = WScript.Arguments(0)
      Else
          strProfileName = olkApp.DefaultProfileName
      End If
      olkSes.Logon strProfileName
      

      The above code checks for a profile name passed on the command line. If one is found, then it’s used. Otherwise, the default profile name is used.

    • Thanks David.

      If I were to set up, say, 3 profiles in outlook (10 mailboxes each), is it possible to then have the script step through each of these?

      I’m thinking this would be the future-proof way of doing things, because I will actually be needing to add even more mailboxes to the list as time goes on.

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