Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Using Excel VB to find an Email in Lotus Notes

Avatar of Mark_Walk
Mark_WalkFlag for United Kingdom of Great Britain and Northern Ireland asked on
Visual Basic ClassicLotus IBMMicrosoft Applications
3 Comments1 Solution633 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Avatar of Bill-Hanson
Bill-HansonFlag of United States of America image

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Commented:
This problem has been solved!
Unlock 1 Answer and 3 Comments.
See Answers