Lotus Notes Database Search

Question

i am trying to write a code to open VBA and do search based on the cell value in A1 (integer). i managed to write a code up to point where i can open the lotus notes and go to specific database. I tried many online codes but couldn't manage to find the code to search in that database. "Lotus.NotesSession" doesn't work the excel version i use. Could you please help me to finish this code. Code is below:

Sub macro4()

Dim uiWs As Object
Dim dbname As String
Dim serverName As String
Dim db As NotesDatabase
Dim doccol As NotesDocumentCollection
Dim varA As Integer



dbname = "***"

serverName = "***"


Set uiWs = CreateObject("Notes.NotesUIWorkSpace")

Call uiWs.OpenDatabase(serverName, dbname) 

Set db = uiWs.GetDatabase(serverName, dbname) ---->where i get the error

varA.Value = Sheets("sheet1").Range("A1").Value

Set doccol = db.FTSearch(varA, Nothing, 0)

End Sub

Your fulltextsearch db.ftsearch() requires different parameters, see ibm.com/support/knowledgecenter/en/SSVRGU_9.0.1/basic/… . The first should be a string. — umeli

Torsten Link Torsten Link · Accepted Answer · 2019-01-28T08:30:00

In Notes there are two "parent"- classes to derive everything from. The NotesUIWorkspace is the class for the "frontend": It contains everything that you SEE in the client. The NotesSession is the class for the backend. NotesDatabase is a backend- class. To correctly get your database, you need to use NotesSession:

Set ses = CreateObject("Notes.NotesSession")
Set db = ses.GetDatabase(serverName, dbname)

You mixed up COM and OLE Integration. The thing you tried to use (Lotus.NotesSession) is for COM only and you need to include Notes in your project to use this.

For your example to work you need to use the OLE integration: Notes.NotesSession

Now to your "Search"- Code:

There are two different ways to search a NotesDatabase:

There is the Fulltextsearch and the "normal" search.

The Fulltextsearch just searches for your value everywhere in all documents and returns a collection. A search for "Tom" in a mailfile will find all mails / calendar entries that where:

sent by Tom
received by Tom
have the word "Tom" in subject or body or an attachment of the mail.

The syntax for FTSearch is:

Set doccol = db.FTSearch( YourSearchValue )

You can restrict the search to one certain field by using a special syntax for your search. e.G. to only search in the "From" field you could write

[From] = "YourSearchValue"

In FTSearch the "=" always means "contains"

The normal search uses a Formula (in @Formula- syntax) to search for a document. It needs the right syntax, otherwise it will not find anything. A formula to search all documents that come from "Tom" would be:

@Contains( From ; "Tom" )

The syntax for search is:

Set doccol = db.Search( YourQueryAsExampleAbove, Nothing, 0 )

With Nothing = Cutoffdate (if given only return documents created or modified after the date) and 0 = max. number of documents to return (0 = return everything).

So your example code for the could be something like:

strQuery = "FieldToSearch = " & Sheets("sheet1").Range("A1").Value
Set doccol = db.Search( strQuery, Nothing, 0 )

Lotus Notes Database Search

2 Answers