none
Exporting Data from Outlook to Excel RRS feed

  • Question

  • Hi

    This is the first time I have used VBA in MS Outlook and have found some code that does most of what I need to do.  However I am struggling to find a line of code that tells me if an email has been replied to and the date and time the reply was sent.  can anyone help please.  The code I am using is copied below for information:

    Option Explicit
     Sub CopyToExcel()
     Dim xlApp As Object
     Dim xlWB As Object
     Dim xlSheet As Object
     Dim rCount As Long
     Dim bXStarted As Boolean
     Dim enviro As String
     Dim strPath As String
     
     Dim currentExplorer As Explorer
     Dim Selection As Selection
     Dim olItem As Outlook.MailItem
     Dim obj As Object
     Dim strColA, strColB, strColC, strColD, strColE, strColF, strColG, strColH, strColI, StrColJ As String
     Dim LDate As Date
     
    ' Get Excel set up
    enviro = CStr(Environ("USERPROFILE"))
    'the path of the workbook
     strPath = enviro & "\Outlook to Excel\test.xlsx"
         On Error Resume Next
         Set xlApp = GetObject(, "Excel.Application")
         If Err <> 0 Then
             Application.StatusBar = "Please wait while Excel source is opened ... "
             Set xlApp = CreateObject("Excel.Application")
             bXStarted = True
         End If
         On Error GoTo 0
         'Open the workbook to input the data
         Set xlWB = xlApp.Workbooks.Open(strPath)
         Set xlSheet = xlWB.Sheets("RawData")
        ' Process the message record
         
        On Error Resume Next
    'Find the next empty line of the worksheet
    rCount = xlSheet.Range("B" & xlSheet.Rows.Count).End(-14162).Row
     
     
    ' get the values from outlook
    Set currentExplorer = Application.ActiveExplorer
    Set Selection = currentExplorer.Selection
      For Each obj In Selection
     
        Set olItem = obj
    
    'collect the fields
        strColA = olItem.SenderName
        strColB = olItem.CreationTime
        strColC = olItem.To
        strColD = olItem.Recipients
        strColE = olItem.ReceivedByName
        strColF = olItem.SentOn
        strColG = olItem.ReceivedTime
        strColH = olItem.UnRead
        strColI = olItem.LastModificationTime
        StrColJ = olItem.UserProperties
        
    'Add column Headers to the Excel Extract
      xlSheet.Range("A" & 1) = "Sender Name"
      xlSheet.Range("B" & 1) = "Creation Time"
      xlSheet.Range("C" & 1) = "Sent To"
      xlSheet.Range("D" & 1) = "Recipients"
      xlSheet.Range("E" & 1) = "Received By Name"
      xlSheet.Range("F" & 1) = "Sent On"
      xlSheet.Range("G" & 1) = "Received Time"
      xlSheet.Range("H" & 1) = "UnRead"
      xlSheet.Range("I" & 1) = "Last Modification Time"
      xlSheet.Range("J" & 1) = "User Properties"
      
    'write them in the excel sheet
      xlSheet.Range("A" & rCount) = strColA
      xlSheet.Range("B" & rCount) = strColB
      xlSheet.Range("C" & rCount) = strColC
      xlSheet.Range("D" & rCount) = strColD
      xlSheet.Range("E" & rCount) = strColE
      xlSheet.Range("F" & rCount) = strColF
      xlSheet.Range("G" & rCount) = strColG
      xlSheet.Range("H" & rCount) = strColH
      xlSheet.Range("I" & rCount) = strColI
      xlSheet.Range("J" & rCount) = StrColJ
      
    'Next row
      rCount = rCount + 1
     
     Next
     
         xlWB.Close 1
         If bXStarted Then
             xlApp.Quit
         End If
         
         Set olItem = Nothing
         Set obj = Nothing
         Set currentExplorer = Nothing
         Set xlApp = Nothing
         Set xlWB = Nothing
         Set xlSheet = Nothing
     End Sub

    Many thanks in anticipation.

    Kind regards

    Tony


    TKHussar

    Saturday, November 21, 2015 10:52 AM

Answers

  • Hi

    Yeeeaaahhh...... It worked. 😀 Many thanks for your patience and perserverance.

    Kind regards

    Tony


    TKHussar

    • Marked as answer by TKHussar Thursday, November 26, 2015 9:57 AM
    Thursday, November 26, 2015 9:57 AM

All replies

  • Hi Tony,

    This is the forum to discuss questions and feedback for Microsoft Outlook, I'll move your question to the MSDN forum for Outlook
    https://social.msdn.microsoft.com/Forums/en-US/home?forum=outlookdev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Emi Zhang
    TechNet Community Support


    Please mark the reply as an answer if you find it is helpful.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.
    Monday, November 23, 2015 6:51 AM
  • Thanks Emi.

    TKHussar

    Monday, November 23, 2015 10:48 AM
  • Hello Tony,

    The Outlook object model doesn't provide any built-in property for that. You can use the PropertyAccessor object for that (see the corresponding property of Outlook items). You are interested in the following low-level properties:

    "PR_LAST_VERB_EXECUTED" 
    
    propertyAccessor.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x10810003")
    
    "PR_LAST_VERB_EXECUTION_TIME"
    
    propertyAccessor.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x10820040")

    See How to find out if Outlook mail is forwarded, replied? for more information.

    Monday, November 23, 2015 11:43 AM
  • Tony,

    You should be aware that the PR_LAST_VERB_EXECUTED property only saves the most recent action.  It is the same with the PR_LAST_VERB_EXECUTION_TIME property. Therefore they are not guaranteed to contain the information you desire.

    For example, if you reply to a message and then subsequently forward that message the information contained in the properties will relate to the forwarding action, not the reply.

    Monday, November 23, 2015 12:51 PM
  • Many thanks for the prompt response.  Would you mind advising where these lines of code need to go in my sample please.

    Many thanks in advance.

    Tony


    TKHussar

    Monday, November 23, 2015 3:32 PM
  • Hi

    I have inserted the two statements in my code but it is printing nothing from any of the emails in the immediate window!

    Also how do I capture the values of those two variables so I can output them to Excel along with the other fields.

    Thanks in advance.

    Tony


    TKHussar

    Monday, November 23, 2015 3:54 PM
  • You must assign the values returned from the PropertyAccessor.GetProperty calls to a variable that is defined in your code.  See https://msdn.microsoft.com/en-us/library/office/ff868350.aspx for an example of using the method.  Pay particular attention to the discussion of the PropertyAccessor.UTCtoLocaltime helper method since you might want to use it on the value returned from the PR_LAST_VERB_EXECUTION_TIME property.

    Monday, November 23, 2015 4:13 PM
  • When I now run the routine with PR_LAST_VERB_EXECUTED_TIME included it never returns any times. This is from over 3000 emails.  Is there anything obvious I may have missed? The code I am using is show below for information.

    Any help/advice would appreciated.

    Thanks in advance.

    Tony

    LVET ="http://schemas.microsoft.com/mapi/proptag/0x10820040"
    Set propertyAccessor = olItem.propertyAccessor
    LVET = propertyAccessor.GetProperty (LVET)


    TKHussar



    • Edited by TKHussar Tuesday, November 24, 2015 1:48 PM
    Tuesday, November 24, 2015 1:46 PM
  • See Forward Messages that were not Replied To for the sample code. 

    You may try to explore low-level properties using any explorer tool such as MFCMAPI or OutlookSpy.

    Tuesday, November 24, 2015 2:23 PM
  • Tony,

    Have a look at this  where m is a MailItem

    Dim dtUTC As Date
    Dim dtLocal As Date
    Dim LVET As String
    
    LVET = "http://schemas.microsoft.com/mapi/proptag/0x10820040"
    Set PA = m.PropertyAccessor
    
    dtUTC = PA.GetProperty(LVET)
    
    dtLocal = PA.UTCToLocalTime(dtUTC)
    
    Debug.Print dtUTC
    Debug.Print dtLocal
    

    Tuesday, November 24, 2015 2:40 PM
  • Hi,

    Many thanks but l cant get this code to work.

    Tony King


    TKHussar

    Wednesday, November 25, 2015 8:57 AM
  • Hi,

    Many thanks but l cant get this code to work.

    Tony King


    TKHussar


    Which code are you talking about?
    Wednesday, November 25, 2015 9:31 AM
  • Hi RLW

    It is the snippet of code you kindly provided above l cant get to work.

    Tony


    TKHussar

    Wednesday, November 25, 2015 11:23 AM
  • Hi RLW

    It is the snippet of code you kindly provided above l cant get to work.

    Tony


    TKHussar

    Please specify the exact error you are receiving and which line of code generates the error.

    Don't forget, if the message has not been forwarded or there has been no reply the PR_LAST_VERB_EXECUTED and PR_LAST_VERB_EXECUTION_TIME properties will not exist on the message

    • Edited by RLWA32 Wednesday, November 25, 2015 11:34 AM
    Wednesday, November 25, 2015 11:27 AM
  • Hi

    It is failing on this line of code:

    Set PA = m.propertyAccessor

    The error is:

    Run time error '424':

    Object required.

    I am happy that if the message has not been forwarded or replied to then there will be no properties attached.  My aim is to be able to highlight mails that have not been responded to within 2 hours of receipt in the Mailbox.

    Thanks

    Tony


    TKHussar



    • Edited by TKHussar Wednesday, November 25, 2015 3:13 PM
    Wednesday, November 25, 2015 3:12 PM
  • As I clearly stated in the post that contained the code, it assumed that m was a MailItem.

    All you need to do is properly assign a MailItem to m.

    If the properties do not exist on a MailItem the code will generate a runtime error that you should be prepared to handle.

    Wednesday, November 25, 2015 3:34 PM
  • Hi

    I am no VBA expert by any means :-).  The code I am using is as follows:

    Sub Test1()
    Dim dtUTC As Date
    Dim dtLocal As Date
    Dim LVET As String
    Dim m As Outlook.mailItem
    LVET = "http://schemas.microsoft.com/mapi/proptag/0x10820040"
    Set PA = m.propertyAccessor
    dtUTC = PA.GetProperty(LVET)
    dtLocal = PA.UTCToLocalTime(dtUTC)
    Debug.Print dtUTC
    Debug.Print dtLocal
    End Sub
    

    I have added the line "Dim m As Outlook.mailItem" and now get the following error message:

    "Object variable or With block not set"

    This is happening on the same line of code: Set PA = m.propertyAccessor

    I am running this on over 3000 emails so can't believe that none of them have been replied to.

    Hope this helps.

    Tony


    TKHussar

    Wednesday, November 25, 2015 3:41 PM
  • You must assign a MailItem to m! 

    For example, the following will assign the selected mail item in the active explorer window to the m variable.

    Set m = ActiveExplorer.Selection.Item(1)

    Wednesday, November 25, 2015 3:48 PM
  • Tony,

    Have a look at this --

    Sub Test1()
    Dim dtUTC As Date
    Dim dtLocal As Date
    Dim LVET As String
    Dim obj As Object
    Dim m As MailItem
    Dim v As Variant
    Dim strType As String

    Set obj = ActiveExplorer.Selection.Item(1)

    strType = TypeName(obj)
    Debug.Print strType

    If strType = "MailItem" Then
        Set m = obj
        LVET = "http://schemas.microsoft.com/mapi/proptag/0x10820040"
        Set pa = m.PropertyAccessor
        v = pa.GetProperty(LVET)
        If Not IsNull(v) Then
            dtUTC = v
            dtLocal = pa.UTCToLocalTime(dtUTC)
            Debug.Print dtUTC
            Debug.Print dtLocal
        End If
    End If
    End Sub



    Wednesday, November 25, 2015 3:57 PM
  • Hi

    Yeeeaaahhh...... It worked. 😀 Many thanks for your patience and perserverance.

    Kind regards

    Tony


    TKHussar

    • Marked as answer by TKHussar Thursday, November 26, 2015 9:57 AM
    Thursday, November 26, 2015 9:57 AM
  • Tony,

    I'm glad that your question has been resolved.  But you should be aware of some points of forum etiquette.

    It is OK to mark your own post as the answer if you have arrived at a solution on your own.

    However, when other posts have either provided you with substantial contributions or directly answered your questions it is proper that you should mark them as answers to your question. 

    Thursday, November 26, 2015 1:04 PM
  • Thanks for the guidance.  Duly noted.

    TKHussar

    Thursday, November 26, 2015 1:34 PM