Link to home
Start Free TrialLog in
Avatar of Mark_Walk
Mark_WalkFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Using Excel VB to find an Email in Lotus Notes

I'm trying to get Excel to go into a Lotus Notes database to search for all emails in a particular folder with a set title and copy all the email data into Excel.  This would need to include the To:, CC:, BCC:, Subject and parts of the main body of the text.  

The main text body will always be in the following format and I need to extract the aaname (i.e. xxxxxxx) and the success_input (i.e. Hello Everybody):

Hello everybody

This was sent from

The email that I receive is sent via a web form.

I have used code before to email from lotus notes using excel vb and also to extract an excel spreadsheet from a particular email as per the below code so i'm guessing i'm close but am all out of ideas at the moment.

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Dim session, db, col, doc, rtitem
    Dim notesdir As Variant
    Dim FileName As String
    Dim lenfilename As Long
    Dim FileCount As Integer
    Dim docCollection As Object
    Const MAX = 100
    Const RICHTEXT = 1
    FileCount = 0
    Set session = CreateObject("Notes.NotesSession")
    Set notesdir = session.GETDBDIRECTORY()
    Set db = session.GETDATABASE("MAIL/GBNUNOTES/XXXXX", "Mail\C\12345A.nsf")
    Set docCollection = db.ALLDOCUMENTS
    NoteCount = docCollection.Count
    For n = 1 To NoteCount
        Set doc = docCollection.GETNTHDOCUMENT(n)
        If doc.HASEMBEDDED Then
            For Each i In doc.ITEMS
                If i.Type = 1084 Then
                    For Each v In i.Values
                        Set Object = doc.GETATTACHMENT(v)
                        FileCount = FileCount + 1
                        FileName = Object.Name
                        lenfilename = Len(FileName)
                        If lenfilename > 1 Then
                            If Left(FileName, 18) = "THISFILE_" & MMM_YY Then
                                'Call Object.EXTRACTFILE(ActiveWorkbook.Path & "/" & MMM_YY & "/" & FileName)
                                Call Object.EXTRACTFILE(ActiveWorkbook.Path & "/" & FileName)
                            End If
                        End If
                    Next v
                End If
            Next i
        End If
    Next n
End Sub

Open in new window

Avatar of Bill-Hanson
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mark_Walk


Nice cheers that is spot on.  For some reason I had to change the Lotus.NotesSession to Notes.NotesSession.  Appart from this it work fine. Thanks again for your help
Lotus.NotesSession is from the COM library.  Notes.NotesSession is from the OLE library.  They are basically the same, but have these differences:


Objects are named using the Lotus prefix (ie: Lotus.NotesSession).

The Notes client must be installed, but does not need to be running.

 You must call the NotesSession.Initialize method, passing the password of the most recent user.

Frontend objects cannot be used!!!


Objects are named using the Notes prefix (ie: Notes.NotesSession).

The Notes client must be installed and running.  If the client is not running, the OLE server will automatically start it and prompt the user to log in.

 There is no NotesSession.Initialize method.  Authentication is obtained throught the running client.

Frontend objects are supported!