Excel VBA code Check how many outlook emails are responded and how many are yet to be responded

S

sn152

New Member
Joined
Apr 30, 2016
Messages
2
Dear All,

I am looking for a VBA code that will check a particular Oulook Shared Mailbox and lists down how many emails have come for a particular date and for how many of them I have responded and for how many of them I am yet to respond and also the total number of emails in the shared mailbox.

I found the below mentioned code in one of the websites. But this doesnt actually serve what I am looking for. I will be using Office 2007, 2010 and 13. Please could you help me here. Thanks in advance!
Code: 
[COLOR=#E56717][FONT=Consolas][B]Option[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#E56717][FONT=Consolas][B]Explicit[/B][/FONT][/COLOR]

[COLOR=#E56717][FONT=Consolas][B]Public[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] ns [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Outlook.Namespace[/FONT][/COLOR]

[COLOR=#E56717][FONT=Consolas][B]Private[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Const EXCHIVERB_REPLYTOSENDER = 102[/FONT][/COLOR]
[COLOR=#E56717][FONT=Consolas][B]Private[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Const EXCHIVERB_REPLYTOALL = 103[/FONT][/COLOR]
[COLOR=#E56717][FONT=Consolas][B]Private[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Const EXCHIVERB_FORWARD = 104[/FONT][/COLOR]

[COLOR=#E56717][FONT=Consolas][B]Private[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Const PR_LAST_VERB_EXECUTED = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"http://schemas.microsoft.com/mapi/proptag/0x10810003"[/FONT][/COLOR]
[COLOR=#E56717][FONT=Consolas][B]Private[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Const PR_LAST_VERB_EXECUTION_TIME = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"http://schemas.microsoft.com/mapi/proptag/0x10820040"[/FONT][/COLOR]
[COLOR=#E56717][FONT=Consolas][B]Private[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Const PR_SMTP_ADDRESS = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"http://schemas.microsoft.com/mapi/proptag/0x39FE001E"[/FONT][/COLOR]
[COLOR=#E56717][FONT=Consolas][B]Private[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Const PR_RECEIVED_BY_ENTRYID [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#F660AB][FONT=Consolas][B]String[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"http://schemas.microsoft.com/mapi/proptag/0x003F0102"[/FONT][/COLOR]

[COLOR=#008000][FONT=Consolas]' Locates best matching reply in related conversation to the given mail message passed in as oMailItem
[/FONT][/COLOR][COLOR=#E56717][FONT=Consolas][B]Private[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#E56717][FONT=Consolas][B]Function[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] GetReply(oMailItem [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] MailItem) [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] MailItem[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]Dim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] conItem [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Outlook.Conversation[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]Dim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] ConTable [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Outlook.Table[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]Dim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] ConArray() [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#F660AB][FONT=Consolas][B]Variant[/B][/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]Dim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] MsgItem [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] MailItem[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]Dim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] lp [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#F660AB][FONT=Consolas][B]Long[/B][/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]Dim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] LastVerb [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#F660AB][FONT=Consolas][B]Long[/B][/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]Dim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] VerbTime [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#F660AB][FONT=Consolas][B]Date[/B][/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]Dim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Clockdrift [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#F660AB][FONT=Consolas][B]Long[/B][/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]Dim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] OriginatorID [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#F660AB][FONT=Consolas][B]String[/B][/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]   [/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]Set[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] conItem = oMailItem.GetConversation [/FONT][/COLOR][COLOR=#008000][FONT=Consolas]' Let Outlook and Exchange do the hard lifting to get entire converstion for email being checked.
[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]   OriginatorID = oMailItem.PropertyAccessor.BinaryToString(oMailItem.PropertyAccessor.GetProperty(PR_RECEIVED_BY_ENTRYID))[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]   [/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]If[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Not[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] conItem [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Is[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#00C2FF][FONT=Consolas][B]Nothing[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Then[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#008000][FONT=Consolas]' we have a conversation in which we should be able to match the reply
[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]       [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]Set[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] ConTable = conItem.GetTable[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        ConArray = ConTable.GetArray(ConTable.GetRowCount)[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        LastVerb = oMailItem.PropertyAccessor.GetProperty(PR_LAST_VERB_EXECUTED)[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Select[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Case[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] LastVerb[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]            [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Case[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] EXCHIVERB_REPLYTOSENDER, EXCHIVERB_REPLYTOALL [/FONT][/COLOR][COLOR=#008000][FONT=Consolas]', EXCHIVERB_FORWARD ' not interested in forwarded messages
[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]               VerbTime = oMailItem.PropertyAccessor.GetProperty(PR_LAST_VERB_EXECUTION_TIME)[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]                VerbTime = oMailItem.PropertyAccessor.UTCToLocalTime(VerbTime) [/FONT][/COLOR][COLOR=#008000][FONT=Consolas]' convert to local time
[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]               [/FONT][/COLOR][COLOR=#008000][FONT=Consolas]' Debug.Print "Reply to " & oMailItem.Subject & " sent on (local time): " & VerbTime
[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]               [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]For[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] lp = 0 [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]To[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]UBound[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas](ConArray)[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]                    [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]If[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] ConArray(lp, 4) = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"IPM.Note"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Then[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#008000][FONT=Consolas]' it is a mailitem
[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]                       [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]Set[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] MsgItem = ns.GetItemFromID(ConArray(lp, 0)) [/FONT][/COLOR][COLOR=#008000][FONT=Consolas]'mail item to check against
[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]                       [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]If[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Not[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] MsgItem.Sender [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Is[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#00C2FF][FONT=Consolas][B]Nothing[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Then[/B][/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]                            [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]If[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] OriginatorID = MsgItem.Sender.ID [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Then[/B][/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]                                Clockdrift = DateDiff([/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"s"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas], VerbTime, MsgItem.SentOn)[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]                                [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]If[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Clockdrift >= 0 [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]And[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Clockdrift < 300 [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Then[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#008000][FONT=Consolas]' Allow for a clock drift of up to 300 seconds. This may be overgenerous
[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]                                   [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]Set[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] GetReply = MsgItem[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]                                    [/FONT][/COLOR][COLOR=#E56717][FONT=Consolas][B]Exit[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]For[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#008000][FONT=Consolas]' only interested in first matching reply
[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]                               [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]If[/B][/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]                            [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]If[/B][/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]                        [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]If[/B][/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]                    [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]If[/B][/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]                [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Next[/B][/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]            [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Case[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Else[/B][/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Select[/B][/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]If[/B][/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    [/FONT][/COLOR][COLOR=#008000][FONT=Consolas]' as we exit function GetMsg is either Nothing or the reply we are interested in
[/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#E56717][FONT=Consolas][B]Function[/B][/FONT][/COLOR]

[COLOR=#E56717][FONT=Consolas][B]Public[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#E56717][FONT=Consolas][B]Sub[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] ListIt()[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]Dim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] myOlApp [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#E56717][FONT=Consolas][B]New[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Outlook.Application[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]Dim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] myItem [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#F660AB][FONT=Consolas][B]Object[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#008000][FONT=Consolas]' item may not necessarily be a mailitem
[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]   [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]Dim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] myReplyItem [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Outlook.MailItem[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]Dim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] myFolder [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Folder[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]Dim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] xlRow [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#F660AB][FONT=Consolas][B]Long[/B][/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]     [/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]Set[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] ns = myOlApp.GetNamespace([/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"MAPI"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]) [/FONT][/COLOR][COLOR=#008000][FONT=Consolas]' Initialise Outlook access
[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]   [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]Set[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] myFolder = ns.PickFolder() [/FONT][/COLOR][COLOR=#008000][FONT=Consolas]' for the sake of this example we just pick a folder.
[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]  [/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    InitSheet ActiveSheet [/FONT][/COLOR][COLOR=#008000][FONT=Consolas]' initialise the spreadsheet
[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]  [/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    xlRow = 3[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]For[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Each[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] myItem [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]In[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] myFolder.Items[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]If[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] myItem.Class = olMail [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Then[/B][/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]            [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]Set[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] myReplyItem = GetReply(myItem) [/FONT][/COLOR][COLOR=#008000][FONT=Consolas]' this example only deals with mailitems
[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]           [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]If[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Not[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] myReplyItem [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Is[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#00C2FF][FONT=Consolas][B]Nothing[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Then[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#008000][FONT=Consolas]' we found a reply
[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]               PopulateSheet ActiveSheet, myItem, myReplyItem, xlRow[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]                xlRow = xlRow + 1[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]            [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]If[/B][/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]If[/B][/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]DoEvents[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#008000][FONT=Consolas]' cheap and nasty way to allow other things to happen
[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]   [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Next[/B][/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    MsgBox [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"Done"[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]   [/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#E56717][FONT=Consolas][B]Sub[/B][/FONT][/COLOR]

[COLOR=#E56717][FONT=Consolas][B]Private[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#E56717][FONT=Consolas][B]Sub[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] InitSheet(mySheet [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Worksheet)[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]With[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] mySheet[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .Cells.Clear[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .Cells(1, 1).FormulaR1C1 = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"Received"[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .Cells(2, 1).FormulaR1C1 = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"From"[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .Cells(2, 2).FormulaR1C1 = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"Subject"[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .Cells(2, 3).FormulaR1C1 = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"Date/Time"[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .Cells(1, 4).FormulaR1C1 = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"Replied"[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .Cells(2, 4).FormulaR1C1 = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"From"[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .Cells(2, 5).FormulaR1C1 = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"To"[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .Cells(2, 6).FormulaR1C1 = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"Subject"[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .Cells(2, 7).FormulaR1C1 = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"Date/Time"[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .Cells(2, 8).FormulaR1C1 = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"Response Time"[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]With[/B][/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#E56717][FONT=Consolas][B]Sub[/B][/FONT][/COLOR]

[COLOR=#E56717][FONT=Consolas][B]Private[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#E56717][FONT=Consolas][B]Sub[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] PopulateSheet(mySheet [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Worksheet, myItem [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] MailItem, myReplyItem [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] MailItem, xlRow [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#F660AB][FONT=Consolas][B]Long[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas])[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]Dim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] recips() [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#F660AB][FONT=Consolas][B]String[/B][/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]Dim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] myRecipient [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Outlook.Recipient[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]Dim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] lp [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#F660AB][FONT=Consolas][B]Long[/B][/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]   [/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]With[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] mySheet[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .Cells(xlRow, 1).FormulaR1C1 = myItem.SenderEmailAddress[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .Cells(xlRow, 2).FormulaR1C1 = myItem.Subject[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .Cells(xlRow, 3).FormulaR1C1 = myItem.ReceivedTime[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        [/FONT][/COLOR][COLOR=#008000][FONT=Consolas]'.Cells(xlRow, 4).FormulaR1C1 = myReplyItem.SenderEmailAddress
[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]       .Cells(xlRow, 4).FormulaR1C1 = myReplyItem.Sender.PropertyAccessor.GetProperty(PR_SMTP_ADDRESS) [/FONT][/COLOR][COLOR=#008000][FONT=Consolas]' I prefer to see the SMTP address
[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]       [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]For[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] lp = 0 [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]To[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] myReplyItem.Recipients.Count - 1[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]            [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]ReDim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]Preserve[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] recips(lp) [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#F660AB][FONT=Consolas][B]String[/B][/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]            recips(lp) = myReplyItem.Recipients(lp + 1).Address[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Next[/B][/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .Cells(xlRow, 5).FormulaR1C1 = Join(recips, vbCrLf)[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .Cells(xlRow, 6).FormulaR1C1 = myReplyItem.Subject[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .Cells(xlRow, 7).FormulaR1C1 = myReplyItem.SentOn[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .Cells(xlRow, 8).FormulaR1C1 = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"=RC[-1]-RC[-5]"[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .Cells(xlRow, 8).NumberFormat = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"[h]:mm:ss"[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]With[/B][/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [/FONT][/COLOR][COLOR=#E56717][FONT=Consolas][B]Sub[/B][/FONT][/COLOR]
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Hi All,

Please help me on this..

sn152 said:
Dear All,

I am looking for a VBA code that will check a particular Oulook Shared Mailbox and lists down how many emails have come for a particular date and for how many of them I have responded and for how many of them I am yet to respond and also the total number of emails in the shared mailbox.

I found the below mentioned code in one of the websites. But this doesnt actually serve what I am looking for. I will be using Office 2007, 2010 and 13. Please could you help me here. Thanks in advance!
Code: 
[COLOR=#E56717][FONT=Consolas][B]Option[/B][/FONT][/COLOR][COLOR=#E56717][FONT=Consolas][B]Explicit[/B][/FONT][/COLOR]

[COLOR=#E56717][FONT=Consolas][B]Public[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] ns [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Outlook.Namespace[/FONT][/COLOR]

[COLOR=#E56717][FONT=Consolas][B]Private[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Const EXCHIVERB_REPLYTOSENDER = 102[/FONT][/COLOR]
[COLOR=#E56717][FONT=Consolas][B]Private[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Const EXCHIVERB_REPLYTOALL = 103[/FONT][/COLOR]
[COLOR=#E56717][FONT=Consolas][B]Private[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Const EXCHIVERB_FORWARD = 104[/FONT][/COLOR]

[COLOR=#E56717][FONT=Consolas][B]Private[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Const PR_LAST_VERB_EXECUTED = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"http://schemas.microsoft.com/mapi/proptag/0x10810003"[/FONT][/COLOR]
[COLOR=#E56717][FONT=Consolas][B]Private[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Const PR_LAST_VERB_EXECUTION_TIME = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"http://schemas.microsoft.com/mapi/proptag/0x10820040"[/FONT][/COLOR]
[COLOR=#E56717][FONT=Consolas][B]Private[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Const PR_SMTP_ADDRESS = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"http://schemas.microsoft.com/mapi/proptag/0x39FE001E"[/FONT][/COLOR]
[COLOR=#E56717][FONT=Consolas][B]Private[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Const PR_RECEIVED_BY_ENTRYID [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#F660AB][FONT=Consolas][B]String[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"http://schemas.microsoft.com/mapi/proptag/0x003F0102"[/FONT][/COLOR]

[COLOR=#008000][FONT=Consolas]' Locates best matching reply in related conversation to the given mail message passed in as oMailItem
[/FONT][/COLOR][COLOR=#E56717][FONT=Consolas][B]Private[/B][/FONT][/COLOR][COLOR=#E56717][FONT=Consolas][B]Function[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] GetReply(oMailItem [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] MailItem) [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] MailItem[/FONT][/COLOR]
[COLOR=#151B8D][FONT=Consolas][B]Dim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] conItem [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Outlook.Conversation[/FONT][/COLOR]
[COLOR=#151B8D][FONT=Consolas][B]Dim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] ConTable [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Outlook.Table[/FONT][/COLOR]
[COLOR=#151B8D][FONT=Consolas][B]Dim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] ConArray() [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#F660AB][FONT=Consolas][B]Variant[/B][/FONT][/COLOR]
[COLOR=#151B8D][FONT=Consolas][B]Dim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] MsgItem [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] MailItem[/FONT][/COLOR]
[COLOR=#151B8D][FONT=Consolas][B]Dim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] lp [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#F660AB][FONT=Consolas][B]Long[/B][/FONT][/COLOR]
[COLOR=#151B8D][FONT=Consolas][B]Dim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] LastVerb [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#F660AB][FONT=Consolas][B]Long[/B][/FONT][/COLOR]
[COLOR=#151B8D][FONT=Consolas][B]Dim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] VerbTime [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#F660AB][FONT=Consolas][B]Date[/B][/FONT][/COLOR]
[COLOR=#151B8D][FONT=Consolas][B]Dim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Clockdrift [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#F660AB][FONT=Consolas][B]Long[/B][/FONT][/COLOR]
[COLOR=#151B8D][FONT=Consolas][B]Dim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] OriginatorID [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#F660AB][FONT=Consolas][B]String[/B][/FONT][/COLOR]

[COLOR=#151B8D][FONT=Consolas][B]Set[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] conItem = oMailItem.GetConversation [/FONT][/COLOR][COLOR=#008000][FONT=Consolas]' Let Outlook and Exchange do the hard lifting to get entire converstion for email being checked.
[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]   OriginatorID = oMailItem.PropertyAccessor.BinaryToString(oMailItem.PropertyAccessor.GetProperty(PR_RECEIVED_BY_ENTRYID))[/FONT][/COLOR]

[COLOR=#8D38C9][FONT=Consolas][B]If[/B][/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Not[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] conItem [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Is[/B][/FONT][/COLOR][COLOR=#00C2FF][FONT=Consolas][B]Nothing[/B][/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Then[/B][/FONT][/COLOR][COLOR=#008000][FONT=Consolas]' we have a conversation in which we should be able to match the reply
[/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]Set[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] ConTable = conItem.GetTable[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        ConArray = ConTable.GetArray(ConTable.GetRowCount)[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        LastVerb = oMailItem.PropertyAccessor.GetProperty(PR_LAST_VERB_EXECUTED)[/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]Select[/B][/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Case[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] LastVerb[/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]Case[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] EXCHIVERB_REPLYTOSENDER, EXCHIVERB_REPLYTOALL [/FONT][/COLOR][COLOR=#008000][FONT=Consolas]', EXCHIVERB_FORWARD ' not interested in forwarded messages
[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]               VerbTime = oMailItem.PropertyAccessor.GetProperty(PR_LAST_VERB_EXECUTION_TIME)[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]                VerbTime = oMailItem.PropertyAccessor.UTCToLocalTime(VerbTime) [/FONT][/COLOR][COLOR=#008000][FONT=Consolas]' convert to local time
[/FONT][/COLOR][COLOR=#008000][FONT=Consolas]' Debug.Print "Reply to " & oMailItem.Subject & " sent on (local time): " & VerbTime
[/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]For[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] lp = 0 [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]To[/B][/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]UBound[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas](ConArray)[/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]If[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] ConArray(lp, 4) = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"IPM.Note"[/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Then[/B][/FONT][/COLOR][COLOR=#008000][FONT=Consolas]' it is a mailitem
[/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]Set[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] MsgItem = ns.GetItemFromID(ConArray(lp, 0)) [/FONT][/COLOR][COLOR=#008000][FONT=Consolas]'mail item to check against
[/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]If[/B][/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Not[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] MsgItem.Sender [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Is[/B][/FONT][/COLOR][COLOR=#00C2FF][FONT=Consolas][B]Nothing[/B][/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Then[/B][/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]If[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] OriginatorID = MsgItem.Sender.ID [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Then[/B][/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]                                Clockdrift = DateDiff([/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"s"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas], VerbTime, MsgItem.SentOn)[/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]If[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Clockdrift >= 0 [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]And[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Clockdrift < 300 [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Then[/B][/FONT][/COLOR][COLOR=#008000][FONT=Consolas]' Allow for a clock drift of up to 300 seconds. This may be overgenerous
[/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]Set[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] GetReply = MsgItem[/FONT][/COLOR]
[COLOR=#E56717][FONT=Consolas][B]Exit[/B][/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]For[/B][/FONT][/COLOR][COLOR=#008000][FONT=Consolas]' only interested in first matching reply
[/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]If[/B][/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]If[/B][/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]If[/B][/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]If[/B][/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]Next[/B][/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]Case[/B][/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Else[/B][/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Select[/B][/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]If[/B][/FONT][/COLOR]
[COLOR=#008000][FONT=Consolas]' as we exit function GetMsg is either Nothing or the reply we are interested in
[/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#E56717][FONT=Consolas][B]Function[/B][/FONT][/COLOR]

[COLOR=#E56717][FONT=Consolas][B]Public[/B][/FONT][/COLOR][COLOR=#E56717][FONT=Consolas][B]Sub[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] ListIt()[/FONT][/COLOR]
[COLOR=#151B8D][FONT=Consolas][B]Dim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] myOlApp [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#E56717][FONT=Consolas][B]New[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Outlook.Application[/FONT][/COLOR]
[COLOR=#151B8D][FONT=Consolas][B]Dim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] myItem [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#F660AB][FONT=Consolas][B]Object[/B][/FONT][/COLOR][COLOR=#008000][FONT=Consolas]' item may not necessarily be a mailitem
[/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]Dim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] myReplyItem [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Outlook.MailItem[/FONT][/COLOR]
[COLOR=#151B8D][FONT=Consolas][B]Dim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] myFolder [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Folder[/FONT][/COLOR]
[COLOR=#151B8D][FONT=Consolas][B]Dim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] xlRow [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#F660AB][FONT=Consolas][B]Long[/B][/FONT][/COLOR]

[COLOR=#151B8D][FONT=Consolas][B]Set[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] ns = myOlApp.GetNamespace([/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"MAPI"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]) [/FONT][/COLOR][COLOR=#008000][FONT=Consolas]' Initialise Outlook access
[/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]Set[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] myFolder = ns.PickFolder() [/FONT][/COLOR][COLOR=#008000][FONT=Consolas]' for the sake of this example we just pick a folder.
[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    InitSheet ActiveSheet [/FONT][/COLOR][COLOR=#008000][FONT=Consolas]' initialise the spreadsheet
[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    xlRow = 3[/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]For[/B][/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Each[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] myItem [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]In[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] myFolder.Items[/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]If[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] myItem.Class = olMail [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Then[/B][/FONT][/COLOR]
[COLOR=#151B8D][FONT=Consolas][B]Set[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] myReplyItem = GetReply(myItem) [/FONT][/COLOR][COLOR=#008000][FONT=Consolas]' this example only deals with mailitems
[/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]If[/B][/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Not[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] myReplyItem [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Is[/B][/FONT][/COLOR][COLOR=#00C2FF][FONT=Consolas][B]Nothing[/B][/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Then[/B][/FONT][/COLOR][COLOR=#008000][FONT=Consolas]' we found a reply
[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]               PopulateSheet ActiveSheet, myItem, myReplyItem, xlRow[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]                xlRow = xlRow + 1[/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]If[/B][/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]If[/B][/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]DoEvents[/B][/FONT][/COLOR][COLOR=#008000][FONT=Consolas]' cheap and nasty way to allow other things to happen
[/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Next[/B][/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    MsgBox [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"Done"[/FONT][/COLOR]

[COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#E56717][FONT=Consolas][B]Sub[/B][/FONT][/COLOR]

[COLOR=#E56717][FONT=Consolas][B]Private[/B][/FONT][/COLOR][COLOR=#E56717][FONT=Consolas][B]Sub[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] InitSheet(mySheet [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Worksheet)[/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]With[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] mySheet[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .Cells.Clear[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .Cells(1, 1).FormulaR1C1 = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"Received"[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .Cells(2, 1).FormulaR1C1 = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"From"[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .Cells(2, 2).FormulaR1C1 = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"Subject"[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .Cells(2, 3).FormulaR1C1 = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"Date/Time"[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .Cells(1, 4).FormulaR1C1 = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"Replied"[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .Cells(2, 4).FormulaR1C1 = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"From"[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .Cells(2, 5).FormulaR1C1 = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"To"[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .Cells(2, 6).FormulaR1C1 = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"Subject"[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .Cells(2, 7).FormulaR1C1 = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"Date/Time"[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .Cells(2, 8).FormulaR1C1 = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"Response Time"[/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]With[/B][/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#E56717][FONT=Consolas][B]Sub[/B][/FONT][/COLOR]

[COLOR=#E56717][FONT=Consolas][B]Private[/B][/FONT][/COLOR][COLOR=#E56717][FONT=Consolas][B]Sub[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] PopulateSheet(mySheet [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Worksheet, myItem [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] MailItem, myReplyItem [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] MailItem, xlRow [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#F660AB][FONT=Consolas][B]Long[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas])[/FONT][/COLOR]
[COLOR=#151B8D][FONT=Consolas][B]Dim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] recips() [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#F660AB][FONT=Consolas][B]String[/B][/FONT][/COLOR]
[COLOR=#151B8D][FONT=Consolas][B]Dim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] myRecipient [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Outlook.Recipient[/FONT][/COLOR]
[COLOR=#151B8D][FONT=Consolas][B]Dim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] lp [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#F660AB][FONT=Consolas][B]Long[/B][/FONT][/COLOR]

[COLOR=#8D38C9][FONT=Consolas][B]With[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] mySheet[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .Cells(xlRow, 1).FormulaR1C1 = myItem.SenderEmailAddress[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .Cells(xlRow, 2).FormulaR1C1 = myItem.Subject[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .Cells(xlRow, 3).FormulaR1C1 = myItem.ReceivedTime[/FONT][/COLOR]
[COLOR=#008000][FONT=Consolas]'.Cells(xlRow, 4).FormulaR1C1 = myReplyItem.SenderEmailAddress
[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]       .Cells(xlRow, 4).FormulaR1C1 = myReplyItem.Sender.PropertyAccessor.GetProperty(PR_SMTP_ADDRESS) [/FONT][/COLOR][COLOR=#008000][FONT=Consolas]' I prefer to see the SMTP address
[/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]For[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] lp = 0 [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]To[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] myReplyItem.Recipients.Count - 1[/FONT][/COLOR]
[COLOR=#151B8D][FONT=Consolas][B]ReDim[/B][/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]Preserve[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] recips(lp) [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#F660AB][FONT=Consolas][B]String[/B][/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]            recips(lp) = myReplyItem.Recipients(lp + 1).Address[/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]Next[/B][/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .Cells(xlRow, 5).FormulaR1C1 = Join(recips, vbCrLf)[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .Cells(xlRow, 6).FormulaR1C1 = myReplyItem.Subject[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .Cells(xlRow, 7).FormulaR1C1 = myReplyItem.SentOn[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .Cells(xlRow, 8).FormulaR1C1 = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"=RC[-1]-RC[-5]"[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        .Cells(xlRow, 8).NumberFormat = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"[h]:mm:ss"[/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]With[/B][/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#E56717][FONT=Consolas][B]Sub[/B][/FONT][/COLOR]
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question
Export Email from Outlook for today emails only
Replies
0
Views
535
wasimchhipa1
W
R
  • Question
Capture the Email Respose Time in Outlook
Replies
1
Views
3K
12anonymouse
1
P
  • Question
Email Encryption code vba
Replies
4
Views
649
Patriot2879
P
F
  • Question
VBA correction required to Extract Emails outlook to excel
Replies
1
Views
744
faraz502
F
D
  • Solved
Adding images in table with formatting into Outlook using a file picker
Replies
1
Views
2K
DeMoNloK
D

Forum statistics

Threads
1,214,908
Messages
6,122,187
Members
449,071
Latest member
cdnMech

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top