Terry – Custom Excel Export


Yesterday a reader named Terry added this comment to my post Exporting Outlook Messages to Excel:

Hi David. I need to export the following fields:

• Date email received
• Time email received (can this be done separately)?
• From email address
• Subject line of query message
• Mailbox (there are a few, so if we can identify which inbox it was received into, it would be a big help).

Thanks.

Terry

After exchanging a few comments to collect more details about the solution Terry needs I realized that what Terry needs is more along the lines of the code in yesterday’s post J.Santos – Custom Excel Export. Although yesterday’s post is closer, the solution Terry needs is still sufficiently different to warrant a new post instead of a revision. What makes Terry’s solution different is that

  • It needs to monitor multiple folders, Jeferson’s only needed to monitor one.
  • Jeferson needed to extract lines from the body, Terry doesn’t.
  • Jeferson wants to write to the same spreadsheet all the time, Terry may need to write to different ones based on the folder being monitored.


This solution is more general and therefore more flexible. Using it Terry can monitor as many different mailboxes/folders as he needs. For each mailbox/folder Terry can specify which Excel workbook and sheet the data will be written to. He can set it to write to different workbooks, different sheets within a single workbook, or any combination of the two. I noticed that Terry is in the UK so I also included the ability to alter the date and time format the solution uses. While the solution currently exports just five pieces of data (i.e. date the message was received, time the messages was received, the sender’s email address, the message subject, and the name of the mailbox the message was sent to) you could easily expand it to handle other fields. The solution does expect that the workbook(s) already exist. It does not create them if they don’t. It could quite easily though with a simple modification. Neither does the code check to make sure the workbook exists. If you pass the solution a bad workbook path or file name, then the code will fail and you’ll get a standard VB error.

Conceptually the solution is simple. It uses the ItemAdd event of a folder’s Items collection as the triggering mechanism. Each time a message arrives in a monitored folder the code fires. The first step is to check to make sure the new item is a message. If it is, then the code launches Excel opens the appropriate workbook and writes the data to the specified sheet.

Note. Depending on the number of mailboxes/folders being monitored and the volume of messages in each there may be a noticeable impact on Outlook’s performance. The solution does have to create an instance of Excel each time a message is received and that is relatively slow. As the number of mailboxes/folders and/or the number of messages coming into each increases performance is going to go down.

This solution will work with Office 2007 and 2010. With modifications it could work in older versions of Office.

The code for this solution comes in two parts.

Part 1.

Follow these instructions to add this part to Outlook.

  1. Start Outlook
  2. Press ALT+F11 to open the VB editor
  3. If not already expanded, expand Microsoft Office Outlook Objects
  4. Right-click on Class Modules, select Insert > Class Module
  5. In the Properties panel click on Name and enter clsSendDataToExcel
  6. Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook’s VB Editor window
  7. Edit the code as needed. I included comments wherever something needs to or can change
  8. Click the diskette icon on the toolbar to save the changes
'On the next line edit the date format as desired.  mm=months, dd=days, yy or yyyy=2 or 4 digit year
Const DATE_FORMAT = "mm/dd/yyyy"
'On the next line edit the time format as desired.  h or hh=hours, n or nn=minutes, s or ss=seconds  Double characters means zero filled.
Const TIME_FORMAT = "hh:nn:ss"

Private WithEvents olkFld As Outlook.Items
Private strMailboxName As String
Private strMailboxPath As String
Private strWorkbookPath As String
Private strWorkbookSheet As String

Private Sub Class_Terminate()
    Set olkFld = Nothing
End Sub

Private Sub olkFld_ItemAdd(ByVal Item As Object)
    Dim excApp As Object, _
        excWkb As Object, _
        excWks As Object, _
        lngRow As Long, _
        arrLines As Variant, _
        varLine As Variant
    If Item.Class = olMail Then
        Set excApp = CreateObject("Excel.Application")
        Set excWkb = excApp.Workbooks.Open(strWorkbookPath)
        Set excWks = excWkb.Worksheets(strWorkbookSheet)
        With Item
            lngRow = excWks.UsedRange.Rows.Count + 1
            excWks.Cells(lngRow, 1) = Format(.ReceivedTime, DATE_FORMAT)
            excWks.Cells(lngRow, 2) = Format(.ReceivedTime, TIME_FORMAT)
            excWks.Cells(lngRow, 3) = .SenderEmailAddress
            excWks.Cells(lngRow, 4) = .Subject
            excWks.Cells(lngRow, 5) = strMailboxName
        End With
        excWkb.Close True
        Set excWks = Nothing
        Set excWkb = Nothing
        excApp.Quit
        Set excApp = Nothing
    End If
End Sub

Private Function OpenOutlookFolder(strFolderPath As String) As Outlook.MAPIFolder
    ' Purpose: Opens an Outlook folder from a folder path.'
    ' Written: 4/24/2009'
    ' Author:  BlueDevilFan'
    ' Outlook: All versions'
    Dim arrFolders As Variant, _
        varFolder As Variant, _
        bolBeyondRoot As Boolean
    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 = Outlook.Session.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

Public Sub Initialize(strMboxName As String, strMboxPath As String, strWkbPath As String, strWkbSheet As String)
    On Error Resume Next
    strMailboxName = strMboxName
    strMailboxPath = strMboxPath
    strWorkbookPath = strWkbPath
    strWorkbookSheet = strWkbSheet
    Set olkFld = OpenOutlookFolder(strMailboxPath).Items
    If TypeName(olkFld) = "Nothing" Then
        MsgBox "The folder " & Chr(34) & strMailboxPath & Chr(34) & " could not be found.  Monitoring not started.", vbCritical + vbOKOnly + vbApplicationModal, "Critical Error"
    End If
    On Error GoTo 0
End Sub

Part 2.

Follow these instructions to add this part to Outlook.

Outlook 2007.

  1. If not already expanded, expand Microsoft Office Outlook Objects and click on ThisOutlookSession
  2. Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook’s VB Editor window
  3. Edit the code as needed. I included comments wherever something needs to or can change
  4. Click the diskette icon on the toolbar to save the changes
  5. Close the VB Editor
  6. Click Tools > Trust Center
  7. Click Macro Security
  8. Set Macro Security to “Warnings for all macros”
  9. Click OK
  10. Close Outlook
  11. Start Outlook. Outlook will display a dialog-box warning that ThisOutlookSession contains macros and asking if you want to allow them to run. Say yes.

Outlook 2010.

  1. If not already expanded, expand Microsoft Office Outlook Objects and click on ThisOutlookSession
  2. Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook’s VB Editor window
  3. Click the diskette icon on the toolbar to save the changes
  4. Close the VB Editor
  5. Click File and select Options
  6. When the Outlook Options dialog appears click Trust Center then click the Trust Center Settings button
  7. Click Macro Settings
  8. Select either of the two bottom settings (i.e. “Notifications for all macros” or “Enable all macros (not recommended; potentially dangerous code can run)”. The choice of which to chose is up to you. If you select “Notifications”, then you’ll be prompted at times to enable macros. If you pick “Enable all” then there’s a chance that a malicious macro could run. It’s a question of how much risk you want to assume.
  9. Click Ok until the dialog-boxes have all closed
  10. Close Outlook
  11. Start Outlook. Outlook will display a dialog-box warning that ThisOutlookSession contains macros and asking if you want to allow them to run. Say yes.
'Repeat the next line once for each mailbox/folder being monitored
Dim objMbox1 As clsSendDataToExcel
Dim objMbox2 As clsSendDataToExcel

Private Sub Application_Quit()
    Set objMbox1 = Nothing
    Set objMbox2 = Nothing
End Sub

Private Sub Application_Startup()
    'Repeat the next two lines for each mailbox/folder being monitored.
    Set objMbox1 = New clsSendDataToExcel
    'The parameters are mailbox name, mailbox path, workbook path and name, sheet name
    objMbox1.Initialize "Mailbox1", "Mailbox - Doe, John\Inbox", "C:\Mailboxes.xlsx", "Sheet1"

    Set objMbox2 = New clsSendDataToExcel
    objMbox2.Initialize "Mailbox2", "Mailbox - Smith, Sally\Inbox", "C:\Mailboxes.xlsx", "Sheet2"
End Sub
Advertisements

6 comments on “Terry – Custom Excel Export

  1. Hi David.

    Thanks very much for this. I had set aside time this afternoon, but I see a couple of additional comments. Should I wait for you to revise the code?

    Thanks,

    Terry

    • Hi, Terry.

      The revisions that James and I were talking about don’t affect the core functionality. Go ahead and test and let me know if you need any adjustments or run into any problems.

  2. There is a reason why I shouldn’t code after 11:00 pm…

    You say you’d like to be able to SEE the workbook that the information was written to? Well, in that case instead of the two lines of code that read:

    End With
    excWkb.Close True

    Insert a line between them to make them read:

    End With
    excWkb.Windows(1).Visible = True
    excWkb.Close True

    Also, I don’t know if it has something to do with the closed workbook, but the target worksheet had to have a heading row – if the worksheet was blank, the first email’s info went on the second row, but subsequent emails overwrote the previous ones – there was never more than one row. It was acting like the UsedRange property was always returning a row count of 1. With a header row (or even just an entry in cell A1) things work as expected.

  3. Re the comments on speed:

    Note. Depending on the number of mailboxes/folders being monitored and the volume of messages in each there may be a noticeable impact on Outlooks performance. The solution does have to create an instance of Excel each time a message is received and that is relatively slow. As the number of mailboxes/folders and/or the number of messages coming into each increases performance is going to go down.

    While I havent extensively tested it, the following modification to the code in your Class Module seems to work:

    Private Sub olkFld_ItemAdd(ByVal Item As Object)
    Dim excApp As Object, _
    excWkb As Object, _
    excWks As Object, _
    lngRow As Long, _
    arrLines As Variant, _
    varLine As Variant
    If Item.Class = olMail Then
    ‘ Debug.Print Now()
    Set excWkb = GetObject(strWorkbookPath)
    Set excWks = excWkb.Worksheets(strWorkbookSheet)
    With Item
    lngRow = excWks.UsedRange.Rows.Count + 1
    excWks.Cells(lngRow, 1) = Format(.ReceivedTime, DATE_FORMAT)
    excWks.Cells(lngRow, 2) = Format(.ReceivedTime, TIME_FORMAT)
    excWks.Cells(lngRow, 3) = .SenderEmailAddress
    excWks.Cells(lngRow, 4) = .Subject
    excWks.Cells(lngRow, 5) = strMailboxName
    End With
    excWkb.Close True
    Set excWkb = Nothing
    ‘ Debug.Print Now()
    End If
    End Sub

    By not opening the Excel workbook, but instead writing to it while it is still closed, in my time trial the difference in the time printed to the Immediate pane dropped from 5 seconds using the original code to less than one second with the revised code (the times printed by the Debug.Print statements were the same I didnt bother to add a high resolution timer to get a better estimate of the elapsed time.

    • Hi, James.

      Thanks for this. I had thought about opening Excel and keeping it open, or writing to it through ADO, but had decided against those approaches. I hadn’t thought of doing it this way. I’ll look into revising the code.

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