VBA Outlook SQL problem (1 Viewer)

olxx

Registered User.
Local time
Yesterday, 20:57
Joined
Oct 2, 2009
Messages
55
Hi,

Need a little help here if somebody have experience with manipulating Outlook via VBA in Access. What i need is sub that gets date and time from a form and opens email message received at that specific date and time. Problem is, my sub works, but not always. In Some cases it opens the right message, other cases it opens message that was received right after the needed message. My guess is that something is wrong with time format or how items.restrict method handles it.

Code:
Private Sub Command5_Click()
On Error GoTo Err_trap

        
Dim myOlApp As New Outlook.Application
Dim objNamespace As Outlook.NameSpace
Dim objFolder As Outlook.MAPIFolder
Dim filteredItems As Outlook.items
Dim itm As Object

Dim strFilter As String
Dim searchTime As String
searchTime = Format(Me.Received.Value, "dd-mm-yyyy hh:nn:ss", vbMonday)
'searchTime = DateAdd("n", 1, searchTime)
Set objNamespace = myOlApp.GetNamespace("MAPI")
If TempVars("BoxType").Value = "Recieved Emails" Then

Set objFolder = objNamespace.GetDefaultFolder(olFolderInbox)

strFilter = "@SQL=" & Chr(34) & "urn:schemas:httpmail:datereceived" & Chr(34) & " <= '" & searchTime & "'"

    

MsgBox strFilter
Else
Set objFolder = objNamespace.GetDefaultFolder(olFolderSentMail)
'strFilter = "@SQL=" & Chr(34) & "urn:schemas:httpmail:sent" & Chr(34) & " like  '" & Me.Received.Value & "'"
End If


Set filteredItems = objFolder.items.Restrict(strFilter)

If filteredItems.Count = 0 Then
    MsgBox "No emails found"
Else
Debug.Print "itemcount " & filteredItems.Count
    
    
     Set itm = filteredItems.GetLast
    
     itm.Display

End If


Set myOlApp = Nothing
Exit Sub
Err_trap:
MsgBox Err.Description
End Sub
 
Solution
Access stores dates as double precision numbers. So, it depends on how a field was populated as to whether you can find an exact match. Visually, you see hh:mm:ss. Behind the scenes is a long decimal value. Here is an example of the problem:
Print cdbl(Now()) ---- this shows you the actual value that now produced
45315.5381828704
print cdate(45315.5381828704) ---- this converts it back to a date
1/24/2024 12:54:59 PM
print cdate(45315.5381828) ---- this takes off a few of the decimal places but the visible date doesn't change
1/24/2024 12:54:59 PM
print cdate(45315.53818) ---- this takes off a few more of the decimal places but the visible date doesn't change
1/24/2024 12:54:59 PM

So, the problem becomes ...

olxx

Registered User.
Local time
Yesterday, 20:57
Joined
Oct 2, 2009
Messages
55
i change the code earlier, to add seconds..
I really don' t need it anymore but i still have genuine interest so i made demo, let's call it for educational purposes. See if you can make it to work because i sure can' t. I think the items.restrict method is a culprit. Thanks for your time and effort.
 

Attachments

  • Outlook test.accdb
    428 KB · Views: 51

Users who are viewing this thread

Top Bottom