[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 847
  • Last Modified:

Update Lotus Note 8 Distribution Lists using VBA?

Is it possible to perform maintenance on LN 8 Distribution Lists using VBA?  

Any leads, links, suggestions on this (including why not possible) would be appreciated.

  • 8
  • 4
1 Solution
Steve KnightIT ConsultancyCommented:
Of course.  What are you trying to do?  I have various scripts and excel spreadsheets for manipluating data in Notes , e.g. to pull data out from the NAB, amend and reimport it.

Have to be careful naturally not to cause conflicts with people manually changing groups etc.

I have a spreadsheet on my scripts site http://scripts.dragonit.co.uk/ that does manipulation from excel.

If you can elaborate a little we can suggest more how to do it or warn you off!

Steve KnightIT ConsultancyCommented:
e.g. Here are some of my VBA / Notes interactions:

Reads NAB for fields you ask for, in this case also including archive details for users, then updates back into archive profile:

Export people from NAB including client versions

Generic export.  By default set to to get people from NAB but can easily be groups instead

The routines in there and similar can be used to pull out the group details, which you can manipulate and push back in, or add people to the groups etc.  Virtually anything that you can do in Notes through LotusScript can be done through VBA too.

codequestAuthor Commented:
Steve, thanks for your input.   I have an associate who has to maintain two 1000+ person distribution list, and right now she has to do it manually.  So, based on you answer, I'm going to let her know that it seems possible to build an MS Access application that can serve as a GUI for this activity, that is, distribution lists are accessible LN objects through VBA.   Is that a fair statement?  

Mostly I would like to let her know that it's possible;  she will have to get the resources to do it elsewhere.  I've written some VBA/LN email generation routines, so the code isn't that difficult, once you know the interfaces.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Steve KnightIT ConsultancyCommented:
Exactly.  As to specifically WHY you would do that I am not sure as it would naturallly be a lot easier to write a simple app in Notes bit if you want to then it can be done.

Once the criteria etc. Is known you could have the database pull out a table of all the person documents to use for choices, and pull out the group members.

A group is just another document in the NAN to Notes with a multi value field for its members (an array effectively)
Steve KnightIT ConsultancyCommented:
Hmm sorry mobile ee decided to ht submit there... bit of a flaw with the window and layout of 'ok'the button next to another button on this little keyboard!

For NAN it should read NAB of course - the server's names.nsf db.
codequestAuthor Commented:
Thanks for your note.  The reason I can't use Lotus Script is because I'm in an enterprise environment where I can't get those privileges.   Plus I'm already competent with VBA.  

Is there a good link to a directory of LN "objects" (such as Distribution List)?  I've only been able to learn such from code samples, so far.
Steve KnightIT ConsultancyCommented:
Have a look at the VBA code in my sheets there will give you some ideas.

I fyou open the server name and address book "names.nsf" from a client, go to the "Groups" view.  those are your "distribution lists"... at least some will be, others will be access control etc.

Press Alt Enter, make sure it says "document" at the top, not database.   On the second tab it shows all the fields of that document.

The field "Form" is what signifies that it is a group.
The other fields make it do what it does.

Now... if you don't have access uing LotusScript / designer I suspect the domino admins migth not like it too much if programming their address book so would get their ok at least!

Do you have rights to edit/amend the address book in general, or just specific groups as their "owner"?

Steve KnightIT ConsultancyCommented:
So what I mean is IF you have the rights to do this etc.. and depending upon what you want to do you would:

Open the database "names.nsf" from the server
Find the relevant group document, generally:
Open the Groups view    
Search the view for relevant document using it's name  v
Now you have a handle on the document
Extract the field of group members, amend them as needed
Update the field
Save the document.
Job done.

Steve KnightIT ConsultancyCommented:
Here is few lines of VBA code that will open NAB, get a group you specify and get it's members into an array.  minimal error checking but might give you a start!

Rem "Declare notes objects"

Dim db As Object
Dim doc As Object
Dim session As Object
Dim v As Object

Set session = CreateObject("Notes.NotesSession")

' Get Handle onto database.  google for "NotesDatabase" object for methods and properties.
Set db = session.getdatabase("", "")
Call db.Open("servername", "names.nsf")

' Get Handle onto view.  google for "NotesView" object for methods and properties.
Set v = db.Getview("Groups")

' Get handle onto specific document in the view.  See "NotesDocument" object for methods and properties.
Set doc = v.GetDocumentByKey("LocalDomainServers") ' your group name
If Not doc Is Nothing Then
    Debug.Print doc.GetItemValue("ListName")(0)
    members = doc.GetItemValue("Members")
    Debug.Print members(0), members(1)
End If

'Make changes to fields if needed
'doc.save command to write it back
codequestAuthor Commented:
Huge help.  I'm going grant points and review this with the people who may write it.  Thanks!
codequestAuthor Commented:
Very helpful, way more than I could have expected.  Grazie!
Steve KnightIT ConsultancyCommented:
Well you can find my email address in my profile (click dragon-it link above) if you get stuck and want it writing for you....

Logically you would extract each relevant group into an access table, or sheet in excel and maintain them there.


Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 8
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now