Outlook 2007 .receivedtime

[dhosi439]

Can someone assist me with extracting the received time so that when pasted it appears as it does in the Inbox.

I realize that pulling the .receivedtime is pulling the system time, however this doesn't always match what appears in the inbox. For instance, the time could be 12:35:32, which may show as 12:35 when I paste, since I do not use seconds. But for certain times, the time can show as 12:34. It seems as though there is some form of rounding on the inbox time.

The code is fairly simple, I just set the .receivedtime to a variable and copy it to the clipboard.

If anyone can inform me of how Outlook rounds these times, as it seems to be fairly random, or assist me with pulling the time that is represented in the inbox, I would really appreciate the assistance.

 

[dhosi439]

Okay so I've determined that the issue is only with times that end in 30 seconds. For some it rounds up to the next minute, others it stays the same. Any suggestions?

 

[Derek Brown]

Yes, it is rounding to the nearest minute for the display with the 30-second point being the 'decider' as to whether to round up or down.
The following is an example of how the Outlook details can be written to an Excel worksheet and the ReceivedTime rounded to the nearest minute to match the details displayed:

Option Explicit
' You must set Tools | References for the following item:
' Microsoft Outlook 12.0 Object Library - this is for Excel 2007
Sub Get_Email_Details()
Dim wb As Workbook
Dim ws As Worksheet
Dim lngKount As Long
'
Dim objOutlook As Outlook.Application
Dim objNS As Outlook.Namespace
Dim objFolder1 As Outlook.Folder
Dim objItem As Outlook.MailItem
'
Const cMinute As Double = 6.94444444444444E-04  ' equals one minute
    '
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Email Details")
    ws.Activate
    ws.Cells(1, 1) = "Sender"
    ws.Cells(1, 2) = "Sender Email Address"
    ws.Cells(1, 3) = "Received Time"
    ws.Cells(1, 4) = "Rounded Received Time"
    lngKount = 2
    '
    Set objOutlook = CreateObject("Outlook.Application")
    Set objNS = objOutlook.GetNamespace("MAPI")
    Set objFolder1 = objNS.Folders("Personal Folders").Folders("Inbox")
    '
    For Each objItem In objFolder1.Items
 '       If objItem.UnRead = True Then
            If objItem.Class = olmail Then
                With objItem
                    ws.Cells(lngKount, 1) = .SenderName
                    ws.Cells(lngKount, 2) = .SenderEmailAddress
                    ws.Cells(lngKount, 3) = .ReceivedTime
' Excel formula equivalent:    = Round(.ReceivedTime / "0:01:00", 0) * "0:01:00"
                    ws.Cells(lngKount, 4) = Round(.ReceivedTime / cMinute, 0) * cMinute
                    lngKount = lngKount + 1
                End With
            End If
 '       End If
    Next objItem
'
End_Para:
    Set objOutlook = Nothing
    Set objNS = Nothing
    Set objFolder1 = Nothing
    Set objItem = Nothing
    Set ws = Nothing
    Set wb = Nothing
End Sub

Dates and times are held as a decimal number so if the result shows as a number, just format it as dd/mm/yyyy hh:mm

 

[dhosi439]

Thank you so much! That works for me.

Here is the code I am using to do this:

Sub getDateTest()
Dim mySelection As Outlook.Selection
Dim myItem As Outlook.MailItem
Dim theDate As String
Dim copyClip As String

Const cMinute As Double = 6.94444444444444E-04  ' equals one minute

Set mySelection = Application.ActiveExplorer.Selection
    If mySelection.Count = 0 Then
        Set mySelection = Nothing
        Exit Sub
    End If
    Set myItem = mySelection.Item(1)
    
    theDate = Format(Round(myItem.ReceivedTime / cMinute, 0) * cMinute, "mm/dd/yy hh:mm")
    
    copyClip = theDate
    
    Call ClipBoard_SetData(copyClip)

End Sub