Received Date/Time troubling me

R

Raos

Joined
Apr 28, 2012
Messages
1
Reaction score
0
Hello Outlook and VBA masters,

Scenario:
We have a mailbox at work which contains more than a 100k mail in a few of the folders. It takes ages to sort the mail inside the folder on outlook. Don't ask me to archive it :D not in my control. I need to get the number of mail received yesterday and also their sender, subject and received time. I have written code in excel which does this.

My troubles begin now. When I look at the count of mail, I realise that mail received at 12:00 AM were not being counted in. Upon further analysis, I see that Outlook rounds up the seconds in date/time when it "displays" the folder on front end. Thus effectively including any mail received say at 4/27/2012 11:59:31 PM into the next day's mail count. I used the round function in VBA but that didn't help. I have now written the code as below:

dtReceivedDate = olMailItem.ReceivedTime

' If the mail item being processed has been recieved after 23:59:30,
' increment the date by 1 thus rounding the time and counting it to the next day's count.
If Hour(dtReceivedDate) = 23 And Minute(dtReceivedDate) = 59 And Second(dtReceivedDate) >= 30 Then
dtReceivedDate = DateSerial(Year(dtReceivedDate), Month(dtReceivedDate), Day(dtReceivedDate))
dtReceivedDate = dtReceivedDate + 1
​
Else
dtReceivedDate = DateSerial(Year(dtReceivedDate), Month(dtReceivedDate), Day(dtReceivedDate))
​
End If

If dtReceivedDate = dtYesterdaysDate Then start counting...
​
Click to expand...
Please let me know if there is a better way of handling this. If there is any way of getting "Yesterday's mail" as Outlook displays, it will be a great joy for me.. I haven't got a chance to test the above code in that mailbox as it is a work thingy and i wont be in till Monday. I'm trying to get this sorted out before i go in to work on Monday.

I would appreciate all assistance in this. Thank You and have a great weekend!!
 
Back
Top