Outlook 2013 Extract Flag Completed dates to Excel Macro

Status
Not open for further replies.
J

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
 
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.)
 
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
 
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?
 
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.
 
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
 
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.
 
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
 
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 :)
 
JWil70 said:
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?
Click to expand...
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
 
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?
 
It doesn't look like i ever wrote an article on this specific macro / idea - but i have a sample at Macro to Export Outlook Fields to Excel - it gets some fields but not the flag complete time.
 
Diane,


I have thoroughly enjoyed the last day-and-a-half of trying to figure out what I am doing wrong. I am obviously missing an understanding of why the code above is not working for me. You introduced me to what MAPI’s are and I have spent hours going down rabbit holes reading through many very-technical documentations on the subject; thank you for that.

I have also found myself going through a lot of the articles that you have authored on this forum and have enjoyed trying to grasp the concepts that you discuss and teach about.

I am at a loss for why the code does not work for me; however, my grasp on the concept could admittedly be stronger.

I ultimately want to figure out how to merge Public Sub “ShowCompleteDate()”’s ability to capture the completed flag’s date (and time) with the functionality of “Public Sub CopyMailtoExcel()” (https://www.slipstick.com/developer/code-samples/macro-export-outlook-fields-excel/), so that I can calculate days between the received date and completed flag date to measure how long it is taking colleagues to complete emailed request/issues.




Description of Error

Block of Code:

Public Sub ShowCompleteDate()

Line of code:

Dim propertyAccessor As Outlook.propertyAccessor

Error Msg:

“Complier Error: Use-defined Type Not Defined”



Troubleshooting:
  • Turned on “Microsoft Outlook 16.0 Object Library” in references
  • Attempted declaring oPA As Outlook.propertyAccessor and updating references in code from propertyAcessor to oPA
Various other things related to changing the declarations (that I cannot recall due throwing everything at the wall to what sticks to arrive at a Run-Time error ‘438’: Object does not support this property method at:

  • Set oItem = Application.ActiveExplorer.Selection.Item(1)
  • Which lead me to read your article on Active Inspectors versus Active Explorers
The code for “Public Sub CopyMailtoExcel()” that you linked to works great but I also noticed that there you:

  • Set objOL = Outlook.Application
  • Set objFolder = objOL.ActiveExplorer.CurrentFolder
  • Set objItems = objFolder.Items


Am I supposed to set my dimensions to look at outlook in “ShowCompleteDate()” like you are doing “Public Sub CopyMailtoExcel()”?
Any guidance or input would be greatly appreciated.



Again, thanks for all of the great information.
 
Diane,

All of my attempts were at trying to make “ShowCompleteDate()” work in Excel. After trying it all day, I started to wonder if my trying to make it work like “Public Sub CopyMailtoExcel()” does was misguided because they are separate macros and “Public Sub CopyMailtoExcel()” was intended for Excel while “ShowCompleteDate()" was designed for Outlook.

Thanks for such a quick response by the way!
 
Status
Not open for further replies.
< List folders in a combo box + select folder + move emails from inbox to that folder + reply to that email | Put Day of Year into Outlook Calendar >
Similar threads
Thread starter Title Forum Replies Date
C Wishlist Extract or scan new email addresses from out of office replies. Leads from OOO replies Using Outlook 1
D ISOmacro to extract active mail senders name and email, CC, Subject line, and filename of attachments and import them into premade excel spread sheet Outlook VBA and Custom Forms 2
M Extract "Date sent" from emails (saved to folder using drag and drop) Outlook VBA and Custom Forms 1
T vba extract data from msg file as attachment file of mail message Outlook VBA and Custom Forms 1
S Unable to extract text from an Outlook email message Using Outlook 2
S Macro to extract and modify links from emails Outlook VBA and Custom Forms 3
S Macro to extract email addresses of recipients in current drafted email and put into clipboard Outlook VBA and Custom Forms 2
C Macro to extract sender name & subject line of incoming emails to single txt file Outlook VBA and Custom Forms 3
N Extract Outlook emails to excel Outlook VBA and Custom Forms 2
M Extract all links from Outlook email, send to Excel Using Outlook 2
T Extract Data From Outlook Tasks Using Outlook 0
T Extract Data From Outlook Tasks Using Outlook 0
V extract users of a particular department Outlook VBA and Custom Forms 1
S How to extract mail items from multiple folders and shared mailboxes? Outlook VBA and Custom Forms 0
K Extract email address from body and auto-reply outlook Using Outlook 1
R Trying to extract information between two symbols from outlook subject Using Outlook 2
K Extract email to excel from a specific sender Outlook VBA and Custom Forms 3
O VBA to extract email (fields and body) to Excel Outlook VBA and Custom Forms 14
P Recover / Extract Rules from standalone PST file creating RWZ file Using Outlook 2
B Extract Dates for Appointment Item in Body of email Outlook VBA and Custom Forms 10
D Need to extract a line from a word attachment, and add it to the subject line Outlook VBA and Custom Forms 3
E Extract excel files from outlook Outlook VBA and Custom Forms 2
K extract certain text from an Outlook Email Message Outlook VBA and Custom Forms 2
D VBA Script to extract text matching specific criteria Outlook VBA and Custom Forms 1
M Extract text in existing message body for use in newmail items Using Outlook 17
M HELP--Extract Data from 2003 outlook transfer to excel spreadsheet Using Outlook 1
M VBA Code to extract data from an Outlook Form Using Outlook 0
M Extract attachments with a script Using Outlook 0
M HELP - Can't open outlook... How can I extract my Emails that I had in folders Using Outlook 3
H Extract emails from Outlokk 2007 email body Using Outlook 0
K Extract Global Address List Using Outlook 1
R Saving Outlook Email As Text File Extract Outlook VBA and Custom Forms 2
N Programming to extract automatically extract attachments Outlook VBA and Custom Forms 3
S How to extract outlook calendar data. Outlook VBA and Custom Forms 3
? outlook attachment Extract File ??? Outlook VBA and Custom Forms 1
N How to extract date and time stamp from messsages Outlook VBA and Custom Forms 6
V Extract Subject,Sent From, Message from mailbox to Excel Outlook VBA and Custom Forms 5
S Automatically extract attachments? Outlook VBA and Custom Forms 1
I How to extract email addresses from TO or CC line of a particular email Outlook VBA and Custom Forms 2
L Help: set flag for sent mail to check if received an answer Outlook VBA and Custom Forms 2
E Remove flag automatically Using Outlook 4
justicefriends How to set a flag to follow up using VBA - for addressee in TO field Outlook VBA and Custom Forms 11
T Can't reposition "Flag Status" column Using Outlook 0
R Outlook 365 update sets delete from server flag Using Outlook 1
S Change "This Week" flag start date behavior Using Outlook 1
C-S-R How to clear an Outlook (To Do) Task Flag? Using Outlook 8
A Flag Message for Follow Up after sending Outlook VBA and Custom Forms 1
A Unflag Inbox and Flag Inbox with Orange Category After Item is send Outlook VBA and Custom Forms 3
N Outlook 2010 Flag blocked for Safe Senders List???? Using Outlook 7
R Call a Public Sub when a Flag is clicked on in the Message Preview pane Outlook VBA and Custom Forms 1

Similar threads

Back
Top