How to use vba to export Outlook 2003 replied and flag complete date/time?

I need to develop some performance metrics from Outlook 2003 to track the time a email is replied/forwarded and completed. I am currently using cfg to display the fields and copy all selected items to clipboard. This method has to be done manually and one folder at a time. I need a macro to go thru all folders and subfolders to extract the two fields.
Who is Participating?
David LeeCommented:
Here's the code for doing this.  Follow these instructions to use it.

1.  Start Outlook
2.  Click Tools->Macro->Visual Basic Editor
3.  If not already expanded, expand Microsoft Office Outlook Objects and click on Module1
4.  Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook's VB Editor window
5.  Edit the code as needed.  I included comment lines wherever something needs to or can change
6.  Click the diskette icon on the toolbar to save the changes
7.  Close the VB Editor
8.  Click Tools->Macro->Security
9.  Set the Security Level to Medium
10. Close Outlook
11. Start Outlook

Run the main macro, RetrieveMetrics.  It should process every Outlook folder and extract the information you're looking for.  It uses the following logic when processing folders/items.

1.  If the folder isn't a mail folder (e.g. calendar, contacts, tasks), then skip it.
2.  If the item in a folder isn't a mail item (e.g. a receipt, task request, etc.), then skip it.
3.  If the item hasn't been replied to or forwarded, then skip it.
4.  If the date cannot be retrieved for either field, then write "Unk" in its place.

I tested the code against a single folder and it appears to work okay.  I did not test against a folder tree.  Currently the macro only writes out the two dates.  If you need it to write out more information, then you can either mod the code yourself or let me know what other information you want and I'll make the change and post an update.

CDO must be isntalled on the computer you run this from.  If it's not, the code will error out immediately.
Dim mapSession As Object, objFile As Object
Sub RetrieveMetrics()
    'Declare some variables'
    Dim olkStore As Outlook.MAPIFolder, objFSO As Object
    'Create a CDO session and login to it using the existing Outlook session'
    Set mapSession = CreateObject("MAPI.Session")
    mapSession.Logon , , False, False, 0
    'Create the output file'
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    'Change the file name and path on the following line as needed'
    Set objFile = objFSO.CreateTextFile("C:\eeTesting\Metrics.csv", True)
    'Launch the main checking process'
    For Each olkStore In Session.Folders
        RMProcessFolder olkStore
    'Logout of the CDO session'
    Set mapSession = Nothing
    Set objFSO = Nothing
    Set objFile = Nothing
    MsgBox "Completed", vbInformation + vbOKOnly, "Retrieve Metrics"
End Sub
Sub RMProcessFolder(olkFolder As Outlook.MAPIFolder)
    'Declare a few variables'
    Dim olkItem As Object, olkSubFolder As Outlook.MAPIFolder, arrMetrics As Variant
    'Process the items in the folder'
    If olkFolder.DefaultItemType = olMailItem Then
        For Each olkItem In olkFolder.Items
            If olkItem.Class = olMail Then
                Debug.Print olkItem.Subject
                arrMetrics = Split(GetMAPIData(olkItem), ",")
                If arrMetrics(0) <> "NA" Then
                    objFile.WriteLine Chr(34) & arrMetrics(0) & """,""" & arrMetrics(1) & Chr(34)
                End If
            End If
    End If
    'Process all the sub-folders under this folder'
    For Each olkSubFolder In olkFolder.Folders
        RMProcessFolder olkSubFolder
    Set olkItem = Nothing
    Set olkSubFolder = Nothing
End Sub
Function GetMAPIData(olkItem As Outlook.MailItem) As String
    'Set the error handler to allow us to handle errors in code
    On Error Resume Next
    'Declare some constants
    Const CdoPR_FLAG_COMPLETE = &H10910040
    Const CdoPR_ACTION = &H10800003
    Const CdoPR_ACTION_DATE = &H10820040
    'Declare a few variables
    Dim mapMessage As Object, _
        mapFields As Object, _
        strReplyTime As String, _
        strCompleted As String, _
        intValue As Integer
    'Get the selected message's ID
    Set mapMessage = mapSession.GetMessage(olkItem.EntryID, olkItem.Parent.StoreID)
    'Get message fields
    Set mapFields = mapMessage.Fields
    'Get the time the item was replied to or forwarded
    intValue = mapFields.Item(CdoPR_ACTION).Value
    If Err.Number = 0 Then
        If (intValue = 261) Or (intValue = 262) Then
            strReplyTime = mapFields.Item(CdoPR_ACTION_DATE).Value
            If Err.Number <> 0 Then
                strReplyTime = "Unk"
            End If
            'Get the date the item was flagged as completed
            strCompleted = mapFields(CdoPR_FLAG_COMPLETE).Value
            If Err.Number <> 0 Then
                strCompleted = "Unk"
            End If
            GetMAPIData = strReplyTime & "," & strCompleted
            GetMAPIData = ","
        End If
        GetMAPIData = "NA,NA"
    End If
    'Clean up
    Set mapMessage = Nothing
    Set mapFields = Nothing
End Function

Open in new window

David LeeCommented:
Hi, lll7883.

Extract those two fields to what?
lll7883Author Commented:
csv or excel file
David LeeCommented:
This is possible, but before depending on this data you should understand how Outlook calculates the time an email was sent or forwarded.  Outlook does not actually store this information.  Instead, it uses a MAPI property to store what it calls the "last verb executed" and the date/time of that execution.  When you reply to an item, the last verb field is set to a value indicating that a reply was the last action and the date/time is recorded.  The problem with this is that the reply doesn't have to be sent.  Open a message, select reply, then delete the reply.  Now, re-open the original item and you'll see that Outlook says you replied at some date/time even though you never actually sent the reply.  That makes the data useless.  If I knew that I was being tracked on this metric, then I could simply open the item shortly before flagging it as complete, select reply, then cancel the reply.  When you collected the metrics it'd look like I replied and then flagged the item complete a short time later.  I'd never fail at meeting my metrics.  If you really need to collect meaningful metrics on this, then you need a means of knowing when a reply or forward was actually sent.  Outlook does not store that information.  You'd have to design a means of collecting it.  

As a side note, Outlook's object model does not expose this information.  It's possible to get it, but it requires using MAPI calls.  I'm letting you know because that will affect your ability to continue using a solution should you eventually upgrade to Outlook 2007.
lll7883Author Commented:
I understand the data may be useless if one chooses to cancel the reply just to make the metrics look good. I have some other ways to control this potential problem.  You said it is possible.  Do you have any example of MAPI calls? Thanks
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.