Outlook 2013 Extract Flag Completed dates to Excel Macro

JWil70

New Member
Outlook version
Outlook 2013 32 bit
Email Account
Exchange Server 2013
Hi Everyone

I work in a contact centre that has started to deal with some inbound Emails and I need to count the inbound traffic by inbox/day and check whether the email was marked as complete (Flag completed Date:Time) within a given period. To achieve this I am copying and pasting from the inbox to a sheet in excel and from there I can compare the Received and Completed values.
There are a number of Folders to process and doing it manually is taking time.
I have modified some code I found on Office Tricks so I now have a macro that sits in excel which will cycle through the items in the Folder and extract to excel the Senders Name, Email address, received time etc.
My problem is that I cannot find the right object for the Flag Completed date (must include time of day).
Can someone help?
I am happy to change my approach if you think I am completely off track
Many Thanks
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
did you try the field name .TaskCompletedDate ? I'm not sure off hand if it gets the time though. If not, you could check the the modified field - if the date is the same, the modified time could be the completed time. (Other actions can change the modified time so its 100% accurate.)
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
I just double checked - it does not get the time, only the date.

my test messages showed the date they were moved - which is one of the things that will affect the date. If you don't move/archive, the last mod time should be the complete time, especially if you run the macro fairly often.
10/21/2015 3/16/2017 9:38:35 PM

Code:
Sub Sample()
    Dim Messages As Selection
    Dim Msg As MailItem
    Dim NamSpace As NameSpace

    Set NamSpace = Application.GetNamespace("MAPI")
    Set Messages = ActiveExplorer.Selection

    If Messages.count = 0 Then Exit Sub

    For Each Msg In Messages
        Debug.Print Msg.TaskCompletedDate, Msg.LastModificationTime
    Next
End Sub
 

JWil70

New Member
Outlook version
Outlook 2013 32 bit
Email Account
Exchange Server 2013
Hi Diane
thanks for taking the time to look at this, i have been successful in getting the Date from FlagCompletedDate but not the time, which is crucial. i don't think i can use Last Modified date as this is to report on a KPI with a financial penalty if failed and I just don't think it is rigorous enough for the client.
any other Ideas?
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
Outlook doesn't save the time AFAIK, but I will see if we can get it from mapi properties - I'm pretty sure the answer is no. Beyond that, you'd need to record the flagged time (using a macro) to a custom field.
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
Well, its nice to be wrong...

Using property accessors gets me these results - The time is in UTC, so it needs adjusted for the time zone. I'm 4 hours off (ET) - i flagged it approx 10:16 so it is correct.
From a newly flagged item for testing:
Received: 10/6/2017 9:17:41 AM
PR_FLAG_STATUS 1
PR_FLAG_COMPLETE_TIME 10/6/2017 2:16:00 PM

This is for the message i tested with the last mod time example above - this would be during daylight time, so -4 hr - obviously no idea when i flagged it complete - it seems like a tight span, but it was a confirmation email and at that time i was in the habit of flagging everything complete when i read/was finished with and a utility moved everything marked complete to the archive every few hours.
Received: 10/21/2015 11:42:37 AM
PR_FLAG_STATUS 1
PR_FLAG_COMPLETE_TIME 10/21/2015 3:44:00 PM



Code:
Public Sub ShowDate()
Dim oItem As Object
Dim propertyAccessor As Outlook.propertyAccessor
Set oItem = Application.ActiveExplorer.Selection.Item(1)
Set propertyAccessor = oItem.propertyAccessor
Debug.Print "Sender Display name: " & oItem.Sender
Debug.Print "Received: " & oItem.ReceivedTime
Debug.Print "PR_FLAG_STATUS", propertyAccessor.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x10900003")
Debug.Print "PR_FLAG_COMPLETE_TIME", propertyAccessor.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x10910040")
Set oItem = Nothing
End Sub
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
Looking at the message some more using MFCMAPI (which is how i found the value to begin with, outlook saves the taskdatecompleted as only the date (in UTC) but it also records the actual time it was completed in a mapi property.

task-completed.png


Macros for getting the system time zone is here - Untitled Page (they are Excel macros but would work in outlook if someone were making the conversion in outlook) - near the bottom is a macro to convert gmt to local time. i have not looked at it closely to see which of the functions at the top of the page you need to go with it.
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
This worked in a quickie test... (Thanks for the article idea. :))

Received: 10/21/2015 11:42:37 AM
PR_FLAG_STATUS 1
PR_FLAG_COMPLETE_TIME 10/21/2015 3:44:00 PM
Local time 10/21/2015 11:44:00 AM

Code:
 Option Explicit
   
    Private Type SYSTEMTIME
        wYear As Integer
        wMonth As Integer
        wDayOfWeek As Integer
        wDay As Integer
        wHour As Integer
        wMinute As Integer
        wSecond As Integer
        wMilliseconds As Integer
    End Type
   
   
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' NOTE: If you are using the Windows WinAPI Viewer Add-In to get
    ' function declarations, not that there is an error in the
    ' TIME_ZONE_INFORMATION structure. It defines StandardName and
    ' DaylightName As 32. This is fine if you have an Option Base
    ' directive to set the lower bound of arrays to 1. However, if
    ' your Option Base directive is set to 0 or you have no
    ' Option Base diretive, the code won't work. Instead,
    ' change the (32) to (0 To 31).
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
   
    Private Type TIME_ZONE_INFORMATION
        Bias As Long
        StandardName(0 To 31) As Integer
        StandardDate As SYSTEMTIME
        StandardBias As Long
        DaylightName(0 To 31) As Integer
        DaylightDate As SYSTEMTIME
        DaylightBias As Long
    End Type
   
   
    ''''''''''''''''''''''''''''''''''''''''''''''
    ' These give symbolic names to the time zone
    ' values returned by GetTimeZoneInformation .
    ''''''''''''''''''''''''''''''''''''''''''''''
   
    Private Enum TIME_ZONE
        TIME_ZONE_ID_INVALID = 0        ' Cannot determine DST
        TIME_ZONE_STANDARD = 1          ' Standard Time, not Daylight
        TIME_ZONE_DAYLIGHT = 2          ' Daylight Time, not Standard
    End Enum
   

    Private Declare Function GetTimeZoneInformation Lib "kernel32" _
        (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long
   
    Private Declare Sub GetSystemTime Lib "kernel32" _
        (lpSystemTime As SYSTEMTIME)

Public Sub ShowCompleteDate()
Dim oItem As Object
Dim flagTime As Date
Dim propertyAccessor As Outlook.propertyAccessor
Set oItem = Application.ActiveExplorer.Selection.Item(1)
Set propertyAccessor = oItem.propertyAccessor
Debug.Print "Received: " & oItem.ReceivedTime
Debug.Print "PR_FLAG_STATUS", propertyAccessor.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x10900003")
Debug.Print "PR_FLAG_COMPLETE_TIME", propertyAccessor.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x10910040")
flagTime = propertyAccessor.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x10910040")

    Dim GMT As Date
    Dim TZI As TIME_ZONE_INFORMATION
    Dim DST As TIME_ZONE
    Dim LocalTime As Date
   
    GMT = flagTime
    DST = GetTimeZoneInformation(TZI)
    LocalTime = GMT - TimeSerial(0, TZI.Bias, 0) + IIf(DST = TIME_ZONE_DAYLIGHT, TimeSerial(1, 0, 0), 0)

Debug.Print "Local time", LocalTime

Set oItem = Nothing
End Sub
 

JWil70

New Member
Outlook version
Outlook 2013 32 bit
Email Account
Exchange Server 2013
what can I say Diane? I am blown away by your kindness. I am certainly on my way to coming up with a working solution.

I just need it to loop round all the "Mail" items in the Target folder and extract "Sender", "Received" and "Flag Complete Time" to an excel sheet. any pointers on that will be gratefully received.
In your experience when an organisation wants to track Volume of emails and Response times or task complete times, also some sort of handling time measure to help with resource planning, is there a "Go To" tool? I am very keen not to reinvent the wheel as my coding skills are not really up to it :)
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
In your experience when an organisation wants to track Volume of emails and Response times or task complete times, also some sort of handling time measure to help with resource planning, is there a "Go To" tool?
there are some reporting tools - Reporter - VBOffice is one, and there are some made specifically for exchange. Whether they will meet your needs, i can't say... you'll need take a look at them.
Outlook Reporting Tools and Utilities
 

Nat Wright

New Member
Outlook version
Outlook 2010 64 bit
Email Account
Exchange Server
hi both, this is exactly what i'm trying to achieve except i lack the skills required. Would you share the spreadsheet you created (with data removed) so i can borrow?
 

Similar threads

Top