Lotus Notes and VBA Checking Email Address in Public Address Books

Question

I'm trying to figure out how to check whether an e-mail address is in the public address book by using VBA. I found some code on the Web, modified it but the code gives an error 91 at line, "Set doc = view.GetAllDocumentsByKey(ChkEmailAddr)." I think that the problem has to do with the declaration type of the variable "view" or the type of view in the line "Set view = b.GetView("People\By Internet Mail")."

I'm pretty certain that I have all of the proper references activated. "Lotus Notes Domino Objects" and "Lotus Notes Automation Classes" are chosen.

I tried to get a list of views but I couldn't figure out how to do it. Do you see the error in my code or have ideas as to what I could try to do some troubleshooting?

  Sub CheckEmailAddress()

 Dim books As Variant
 Dim view As lotus.NotesView
 'Dim view As Object
 Dim doc As NotesDocumentCollection
 Dim dc As NotesDocument
 Dim done As Variant
 Dim docarr(3, 50) As Variant
 Dim ChkEmailAddr As String

 ChkEmailAddr = "user@example.com"     

 Set Session = CreateObject("Notes.Notessession")

 books = Session.AddressBooks
 done = False

 For Each b In books
   ' check every public address book,
   ' unless we're already done
   If (b.IsPublicAddressBook) Then
   Debug.Print TypeName(b)

     Call b.Open("", "")
     Debug.Print b.Title
     ' look up person's last name
     ' in People view of address book


     Set view = b.GetView("People\By Internet Mail")
     Debug.Print TypeName(view)
     'Debug.Print view
     'Set view = b.GetView("main")
     'Debug.Print TypeName(view)
     Set doc = view.GetAllDocumentsByKey(ChkEmailAddr)
     ' if person is found, display the phone number item
     'from the Person document
     If Not (doc Is Nothing) Then

        For j = 0 To doc.Count
            docarr(0, j) = doc.GetNthDocument(j).Items(11).Text
             docarr(1, j) = doc.GetNthDocument(j).Items(93).Text
            docarr(2, j) = doc.GetNthDocument(j).Items(95).Text
            docarr(3, j) = doc.GetNthDocument(j).Items(14).Text
        Next j
     End If
   End If
Next b
 findEmailLotus = docarr

End Sub

Answer 1

I'm sorry to say this, but you have a lot of issues here. Whoever wrote that code that you started from did not know what he or she was doing.

First of all, unless you are requiring that the Notes client is running while your code is running, you should be using Lotus.NotesSession instead of Notes.NotesSession. (The former corresponds to the "Lotus Notes Domino Objects", and uses COM to talk to the Notes APIs, and the latter corresponds to the "Lotus Notes Automation Classes, and uses OLE to talk to the Notes client to talk to the APIs - hence the requirement that the client must be running.)

Secondly, you haven't mentioned what version of Lotus Notes you are dealing with, but more recent versions (8 and above) include the NotesDirectory class, which includes a LoookupNames method that would probably be a better solution for you than writing your own code to loop through address books.

Third, after doing your set view operation, you really ought to be doing an If Not view is Nothing test. That will tell you whether or not you actually have a problem opening the view.

Fourth, doc is a really bad variable name for the return value from GetAllDocumentsByKey. In 20+ years of writing Notes code, I can say that anybody reading Notes code expects the variable name doc to always refer to a single document. You are getting a NotesDocumentCollection, not a single document. Do yourself a favor and change it to docs or dc, or just about anything except doc.

Fifth, using GetNthDocument in this context is usually not recommended. It performs very badly in large collections. Even worse, however, is that you are calling it four times when you could be making only one call per iteration. Instead of a For loop, consider changing it to a call to GetFirstDocument followed by While Not doc is Nothing loop that retrieves your item values and stores them in your array, and then calls getNextDocument at the bottom of the loop.

Sixth, that code referencing .Items(11), .Items(93)... that's just plain wrong. The available items within any given document are variable because Notes is schemaless. Those item numbers will refer to different fields for different people - i.e., essentially random values. That can't possibly be what you want. You should be using getFirstItem() calls with the actual names of the items that you really want to be putting in your array. You will need to study the field names used in the Domino Directory to figure this out. I recommend NotesPeek as a good tool for exploring Notes databases and/or just opening up the Domino Directory in the Domino Designer client and looking at the Person form (and associated subforms) to figure out what you need.

As to the actual error you asked about, my guess is that by adding the recommended test of If Not view Is Nothing you will gain more information, but perhaps not enough. You haven't mentioned what your debug prints are generating, but I believe there are some cases where the title is available even if the database was not successfully opened, so I don't think you should trust that as a test of whether the call worked. In fact, you really shouldn't just be doing a Call db.open("","") call. You should be doing an If db.open("","") = true to test whether it actually worked.

Answer 2

For people who know lotus notes, it's trival. For those that don't, there might be an easier way using web access.

Request this address Keep your cookies

http://server/names.nsf?login&username=MYUSERNAM&password=MYPASSWORD

Then access this url and look for a 404 status or a 200 status

http://server/names.nsf/($Users)/email@domain.com?opendocument

Of course, that requires that you have web access enabled on your server and in many cases putting your password in your code is bad, and it won't work if your servers are configured in some ways.

Before coding, test it on your server.