VBA Outlook SQL problem (1 Viewer)

[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?

 

[Pat Hartman]

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 :
45315.5381828704 is not = 45315.53818
But visually, they show the same time value.

Why are you relying on finding the message by using a specific date. Perhaps there are other options that might succeed.

 

[MsAccessNL]

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

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

 

[Pat Hartman]

For most data types you would never format your search field or criteria. However if you are using a time, you either need to format so you are comparing two strings or you need to use a range of one second.

Where Format(SomeDate, "yyyy/mm/dd hh/nn/ss") = Format(Forms!yourform!yourdate, "yyyy/mm/dd hh/nn/ss")

 

[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.

 

[Pat Hartman]

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.

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.

 

[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#

 

[Pat Hartman]

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.

It does work if you use seconds. What format did you use for the date? Did you include the seconds? When you turn a date into a string by formatting it, it works EXACTLY like any other string. It no longer works like a date. That is why you need to use the specific format I suggested.

However, it is rarely rational to use time as part of an exact date comparison the way you are trying to. Your new solution sounds better. Another solution would have been to look for mail with dates > the last date imported into Access if what you are looking for is stuff you haven't processed yet.

 

[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.