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

Hi,
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.  
e.g.
To:xxxxx@xxxxxx.com
cc:xxxxx@xxxxxx.com
BCC:xxxxx@xxxxxx.com
Subject:Blockages

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):

aaname:
xxxxxxx
success_input:
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.

Cheers
Sub LOTUS_NOTES_GET_ATTACHMENTS()
    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
    Dim VIEW As NOTESVIEW
    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 VIEW = db.GETVIEW("($BLOCKAGES)")
    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

Lotus IBMMicrosoft ApplicationsVisual Basic Classic

Avatar of undefined
Last Comment
Bill-Hanson

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Bill-Hanson

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
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
Bill-Hanson

Lotus.NotesSession is from the COM library.  Notes.NotesSession is from the OLE library.  They are basically the same, but have these differences:

COM

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!!!

OLE

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!
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23