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

Posted on 2008-10-02
Last Modified: 2012-05-05
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.
Question by:lll7883
  • 3
  • 2
LVL 76

Expert Comment

by:David Lee
ID: 22667357
Hi, lll7883.

Extract those two fields to what?

Author Comment

ID: 22675769
csv or excel file
LVL 76

Expert Comment

by:David Lee
ID: 22676869
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.

Author Comment

ID: 22693054
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
LVL 76

Accepted Solution

David Lee earned 250 total points
ID: 22694856
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


Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Check out this infographic on what you need to make a good email signature that will work perfectly for your organization.
Outlook Free & Paid Tools
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now