VBA Lotus Notes View All Documents with Filter

Question

I am attempting to modify a code to find an email (with variable date in subject) in my Lotus Notes, extract the attachment, and save to desktop. I got it to work when the email is located in the inbox. However, since emails may be filed or not, to different folders, I would like the macro to look in all documents view. I've researched that having a view with a filter, or creating a notesdocumentcollection may suit my needs. But I've been unable to modify.

With the code below, Excel gives me a box saying 'Microsoft Office Excel is waiting for another application to complete an OLE action'

Your help is appreciated.

Thanks

Sub CheckMail ()
Dim Session As Object      
Dim MailDB As Object                
Dim Doc As Object          
Dim Collection As Object
Dim filter As String

filter = "[CreationDate]>=" & 6 / 9 / 15 & " AND [CreationDate]<=" & 6 / 10 / 15

Set Session = CreateObject("notes.notessession") 'creates notes session
Set MailDB = Session.getdatabase("ServerName/AAA", "XXX.nsf")

Set Collection = MailDB.FTSearch(filter, 0)

Set Doc = Collection.GETFIRSTDOCUMENT
Do Until Doc Is Nothing

'The rest of the code loops to find email with matching subject

End Sub

Answer 1

You don't need a view to do a ftsearch, this works on the database- object as well. This will work for all documents in the database:

Set collection = MailDB.FtSearch( filter, 0 )

To answer your comment: I just checked your search term in my client, and it returns the error Relational operators are not supported in text fields. That is because "CreationDate" is not a valid field in Lotus Notes.

All mails in a mailfile have an item "PostedDate" that shows, when the mail was sent. Incoming mails have an item "DeliveredDate" to show, when they were delivered. Your query has to use one of them instead of the non existing "CreationDate".

Additional remarks:

1. To get the mailfile that is configured in the client you do not need to hardcode the path, you can use three simple lines to get it:

2. a Search is always better than an FTSearch, as it is more accurate.

I would prefer to use the following code to get the right documents (different syntax of search term because of using search and not ftsearch

Set Session = CreateObject("notes.notessession") 'creates notes session
Set MailDB = Session.getdatabase("", "")
Call MailDB.OpenMail

filter = {Form = "Memo" : "Reply" & PostedDate >= [06/09/15] & PostedDate <= [6/10/15]}
Set Collection = MailDB.Search( filter, Nothing, 0 )

I added a filter for "Form" because otherwise you will get Calendar Invitations, Contacts, ToDos as well.

If you want to use "FTSearch", then the same filter would look like:

filter = {([Form] = "Memo" OR [Form] = "Reply") AND 
  [PostedDate] >= 6/09/15 AND [PostedDate] <= 6/10/15}