VBA Outlook SQL problem

[olxx]

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.

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

 

[arnelgp]

try changing the format to:

"yyyy-mm-dd hh:mm:ss"

 

[olxx]

try changing the format to:

"yyyy-mm-dd hh:mm:ss"

Tried that. Actually tried all possible formats that i found, and Cdate function as well. The "yyyy-mm-dd hh:mm:ss" format gives same results.

 

[Gasman]

If you are using <= how do you expect to get just the correct message?
I do not get to see seconds in my outlook display, so can only go by hours and minutes, so if I ask for 18:53, the message I see as 18:53 is actually 18:53:nn, so I get the next one.

So how do you know the seconds for the emails?

 

[MsAccessNL]

LastModificationtime works with a pound sign, i only tried it with date not with time..

SearchTime = #..........#
strFilter = "[LastModificationTime] >= """ & searchtime & """"
'or try [CreationTime]

 

[olxx]

Thank you all for your responses. Pat Hartman was right, the problem is how Outlook stores date and time. It may display it correctly but it remains unclear what´s the actual value stored in the internal database. Some even say that it is stored as string. So you end up comparing apples and potatoes. As I found from vba docs quote: If you specify seconds in the date-time comparison string, the filter will not operate as expected. So i have to find some other approach. Thanks again guys.

 

[Gasman]

Can't you use the time functions to just compare with what you need?

VBA Functions: Hour, Minute, and Second

The VBA Hour, Minute, and Second functions return an integer corresponding to the hour, minute, or second of the specified date or time...

www.excel-pratique.com

 

[MsAccessNL]

Thanx for sharing the info, good to know. Also curious to your solution.

 

[olxx]

I gave you the approach. You turn the dates into strings. You just need to do the dates as yyyy/mm/dd because year is more important than month and month is more important than day, etc. Just because you format the dates for comparison, that doesn't mean you can't have an unformatted version of the column in the query that you can allow to default to the system date format.

I already tried that earlier. It does not work. The problem is that items.restrict method just won' t work correctly if you use seconds. By changing approach, i meant that i won't try to find messages by date/time anymore. But thanks for the reply.

 

[arnelgp]

try this code:

Private Sub Command5_Click()
On Error GoTo Err_Trap
    Dim olApp As Object
    Dim olNamespace As Object
    Dim olFolder As Object
    Dim olItems As Object
    Dim FilteredItems As Object
    Dim filter As String
    Dim itm As Object
    Dim searchTime As String
   

    ' Set the Outlook application object
    Set olApp = CreateObject("Outlook.Application")
    ' Set the Outlook Namespace
    Set olNamespace = olApp.GetNamespace("MAPI")
   
    ' Create a filter based on the date and time range
    If TempVars("BoxType").Value = "Recieved Emails" Then
        searchTime = Format(Me.Received.Value, "yyyy-mm-dd hh:mm:ss AM/PM")
        filter = "[ReceivedTime] <= '" & searchTime & "'"
        Set olFolder = olNamespace.GetDefaultFolder(6) ' 6 corresponds to the Inbox folder
    Else
        searchTime = Format(Me.Received.Value, "yyyy-mm-dd hh:mm:ss AM/PM")
        filter = "[SentOn] <= '" & searchTime & "'"
        Set olFolder = olNamespace.GetDefaultFolder(5) ' 5 corresponds to the sent items
   
    End If
    ' Set the Outlook folder to Inbox (you can change this to other folders)
   
    ' Get the items in the specified folder based on the filter
    Set FilteredItems = olFolder.Items.Restrict(filter)
   
    If FilteredItems.Count = 0 Then
        MsgBox "No emails found"
    Else
        Debug.Print "itemcount " & FilteredItems.Count
        Set itm = FilteredItems.GetLast
   
        itm.Display

    End If
   
    ' Clean up objects
    Set itm = Nothing
    Set FilteredItems = Nothing
    Set olFolder = Nothing
    Set olNamespace = Nothing
    Set olApp = Nothing
    Exit Sub
Err_Trap:
MsgBox Err.Description
End Sub

 

[olxx]

Thanx for sharing the info, good to know. Also curious to your solution.

I reworked my solution entirely. It's task management app what we are talking about here. Actually a Access template, that i' m now modifying to integrate Outlook to it (importing new tasks form e-mail messages, managing replys etc.). I now store Outlook EntryID in access database and tag the Outlook message subject field with corresponding access table ID field. Now i can find the last sent or received messages quickly by items.getlast method. To find and display all messages related to specific task, I now won' t use Access but Outlook itself (ActiveExplorer.Search method). My first approach didn't work because i tried to link Outlook data file directly to Access. Linking works, but Outlook won't give you EntryID field, so your only option to find and open message is by date/time. Downside of using EntryID is that its' not completely unique, it changes it's value if message is moved to another folder.

 

[olxx]

try this code:

Private Sub Command5_Click()
On Error GoTo Err_Trap
    Dim olApp As Object
    Dim olNamespace As Object
    Dim olFolder As Object
    Dim olItems As Object
    Dim FilteredItems As Object
    Dim filter As String
    Dim itm As Object
    Dim searchTime As String
   

    ' Set the Outlook application object
    Set olApp = CreateObject("Outlook.Application")
    ' Set the Outlook Namespace
    Set olNamespace = olApp.GetNamespace("MAPI")
   
    ' Create a filter based on the date and time range
    If TempVars("BoxType").Value = "Recieved Emails" Then
        searchTime = Format(Me.Received.Value, "yyyy-mm-dd hh:mm AM/PM")
        filter = "[ReceivedTime] <= '" & searchTime & "'"
        Set olFolder = olNamespace.GetDefaultFolder(6) ' 6 corresponds to the Inbox folder
    Else
        searchTime = Format(Me.Received.Value, "yyyy-mm-dd hh:mm AM/PM")
        filter = "[SentOn] <= '" & searchTime & "'"
        Set olFolder = olNamespace.GetDefaultFolder(5) ' 5 corresponds to the sent items
   
    End If
    ' Set the Outlook folder to Inbox (you can change this to other folders)
   
    ' Get the items in the specified folder based on the filter
    Set FilteredItems = olFolder.Items.Restrict(filter)
   
    If FilteredItems.Count = 0 Then
        MsgBox "No emails found"
    Else
        Debug.Print "itemcount " & FilteredItems.Count
        Set itm = FilteredItems.GetLast
   
        itm.Display

    End If
   
    ' Clean up objects
    Set itm = Nothing
    Set FilteredItems = Nothing
    Set olFolder = Nothing
    Set olNamespace = Nothing
    Set olApp = Nothing
    Exit Sub
Err_Trap:
MsgBox Err.Description
End Sub

I tried that earlier. If two or more messages are received at the same minute, it will not open correct one. But i already changed my solution completely so i don't need that time finding approach anymore. But thank you for your reply anyway.

 

[arnelgp]

i change the code earlier, to add seconds..

 

[ebs17]

Regardless of the method used to equalize the unknown decimal places in the time by formatting the date and time string, it is very ambitious to find something that is accurate to the second.

Here you should work with ranges, adapting the boundaries of the ranges to your own requirements.

EventTimestamp BETWEEN #2024-01-20 08:00:00# AND #2024-01-20 08:05:00#

 

[MsAccessNL]

I reworked my solution entirely. It's task management app what we are talking about here. Actually a Access template, that i' m now modifying to integrate Outlook to it (importing new tasks form e-mail messages, managing replys etc.). I now store Outlook EntryID in access database and tag the Outlook message subject field with corresponding access table ID field. Now i can find the last sent or received messages quickly by items.getlast method. To find and display all messages related to specific task, I now won' t use Access but Outlook itself (ActiveExplorer.Search method). My first approach didn't work because i tried to link Outlook data file directly to Access. Linking works, but Outlook won't give you EntryID field, so your only option to find and open message is by date/time. Downside of using EntryID is that its' not completely unique, it changes it's value if message is moved to another folder.

I am also working on a Project-TaskMailer app and I encountered the same problem with the EntryId. I read somewhere that it changes when you move the email to another folder (i didn’t check it yet). I was thinking about putting an id from access into one of the custom email properties fields.

I also have an app where you drag drop the email you want to attach to a task. The mail is saved as msg in a folder (no entryid problem).

Make sure you have a back up of all your imap folders. Outlook 2013 recently removed my folders, permanently gone !!!

 

[olxx]

I am also working on a Project-TaskMailer app and I encountered the same problem with the EntryId. I read somewhere that it changes when you move the email to another folder (i didn’t check it yet). I was thinking about putting an id from access into one of the custom email properties fields.

I also have an app where you drag drop the email you want to attach to a task. The mail is saved as msg in a folder (no entryid problem).

Make sure you have a back up of all your imap folders. Outlook 2013 recently removed my folders, permanently gone !!!

I use pop3 not imap. All messages are saved locally in pst file. Unless i don' t move messages myself, the entryID will remain intact. The reason for using subject field to add ID tag, instead of custom field, is that i'm not sure how the custom fields behave in other computers/mail clients. Right now i store only one record per task in access and add id tag to subject of a message in outlook. Now all the replies and further messages carry that id tag along, no matter what email client someone is using. This way you don't have to store all the messages of the same topic/task, you only have to find them in outlook. And if someone deletes the tag or changes subject in reply, you can easily re-tag it with VBA. In outlook, the already received message seems read-only, but in fact it is not. I have button in access which can add or remove my ID tag of the currently selected message's subject field.

 

[olxx]

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.