Ron de Bruin
Excel Automation

Lotus Notes: Retrieve e-mailaddresses from a Notes database

Important message to visitors of this page

Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.

Luckily, Ron was kind enough to allow me to publish all of his Excel content here.

Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.

Kind regards

Jan Karel Pieterse

The code examples on this page are created by Excel MVP Dennis Wallentin but are not on his site anymore. But Dennis allow me to publish it on my site for all the Excel/Lotus Notes users in the world.

The information in this article applies to:
Microsoft Excel 2000 and later.
Lotus Notes 4.x and later.

External reference:

Microsoft ActiveX Data Objects 2.5 Library and above.
Lotus NotesSQL-driver for download: NotesSQL

Task:
Retrieve e-mailaddresses from a Notes database and dump the data into a worksheet.

VBA code :

Option Explicit
 
Sub Retrieve_E_Mailaddresses_Notes_Database()
   Dim cnt As ADODB.Connection
   Dim rst As ADODB.Recordset
   Dim xlCalc As XlCalculation
   Dim wbBook As Workbook
   Dim wsSheet As Worksheet
   Dim rnData As Range
 
   'One easy way to get the fieldnames in the database
   'is to create a query by using MS Query.
   Const stSQL As String = "SELECT MailAddress FROM Person ORDER BY MailAddress"
 
   With Application
      xlCalc = .Calculation
      .Calculation = xlCalculationManual
      .EnableEvents = False
      .ScreenUpdating = False
   End With
 
   Set wbBook = ThisWorkbook
   Set wsSheet = wbBook.Worksheets(1)
 
   With wsSheet
      Set rnData = .Range("A2")
   End With
 
   'Instantiate the Connectionobject.
   Set cnt = New ADODB.Connection
 
   'Open the connection.
   cnt.Open ("Driver={Lotus NotesSQL Driver (*.nsf)};Database=names.nsf;Server=Local;")
 
   'Instantiate the Recordsetobject and execute the query.
   Set rst = cnt.Execute(stSQL)
 
   'Dump the recordset into the worksheet.
   rnData.CopyFromRecordset rst
 
   rst.Close
   cnt.Close
 
   'Release objects from memory.
   Set cnt = Nothing
   Set rst = Nothing
 
   With Application
      .Calculation = xlCalc
      .EnableEvents = True
      .ScreenUpdating = True
   End With
 
End Sub

Comments:
A relative easy and straightforward way to retrieve data from a Notes database.

Copyright © 2022 Ron de Bruin

Site designed by Spider Web Woman Designs