Loop Through Emails in Notes Client in VBA Excel

Hi, I wonder if there is a way in VBA Excel to loop through each email in my Notes inbox (or which ever folder is currently open in the Notes Client); pulling the text off each email as they are looped through.  Thanks, Hammer8
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

just text ?
What about pictures, attachments, embedded options ?

What are you really trying to do ?

I hope this helps !
Hammer8Author Commented:
just text is sufficient.  I want to store the txt in an access db.  thanks.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

And maybe even a better one for this purpose:
Option Public
Option Declare
Sub Initialize
		 Dim session As New NotesSession
		 Dim db As NotesDatabase
		 Dim dc As NotesDocumentCollection
		 Dim doc As NotesDocument
		 Dim strDB As String		 
		 Dim dbq As String
		 Dim ConnectionString As String
		 Dim con As New ODBCConnection
		 Dim qry As New ODBCQuery
		 Dim result As New ODBCResultSet
		 Set session = New NotesSession
		 Set db = session.CurrentDatabase
		 Set dc = db.UnprocessedDocuments		 
		 If (dc.Count > 0) Then
		 		 strDB = "C:\Newdb4.mdb" ' this is a hardcoded path
     		 'allow user to redifine mdb path
		 		 strDB = Inputbox$("Enter mdb path: ","New mdb path", strDB)
		 		 dbq = "; Dbq=" & strDB
		 		 Forall DSN In con.ListDataSources
			 		 If (Instr(1,dsn, "Access") > 0) Then ConnectionString = DSN & dbq
		 		 End Forall
		 		 If Not con.ConnectTo( ConnectionString ) Then
		 		 		 Messagebox "Could not connect to: " & ConnectionString
		 		 		 Exit Sub
		 		 		 Messagebox "Connected to: " & ConnectionString		 
		 		 End If
		 		 Set qry.Connection = con
		 		 Set result.Query = qry
		 		 qry.SQL = "SELECT * FROM Employees" 'use right table name from mdb here
		 		 Set doc = dc.GetFirstDocument
		 		 While Not(doc Is Nothing)
		 		 		 Call result.SetValue("EmployeeID",doc.EmployeeID(0))
		 		 		 Call result.SetValue("FirstName",doc.FirstName(0))
		 		 		 Call result.SetValue("LastName",doc.LastName(0))
		 		 		 Call result.SetValue("Department",doc.Department(0))
		 		 		 Set doc = dc.GetNextDocument(doc)
		 		 Messagebox "No documents selected."
		 End If
End Sub

Open in new window

Hammer8Author Commented:
Thank you for your help, but I was looking for VBA Excel code.  My setup does not allow me to use Notes Agents.  Hammer8
Then dim variables as Object and init session like this:

      Dim sess As Object
      Dim db As Object
      Dim view As Object
      Dim doc As Object
      Dim mailServer As String
      Dim mailFile As String
      Dim fld1 As String
      Dim strSQL As String

      Set sess = CreateObject( "Lotus.NotesSession" )
      Call sess.Initialize( password )

      Dim objADOConnection As Object
      Set objADOConnection = CreateObject("ADODB.Connection")

'to get your mail db:
      mailServer = sess.GetEnvironmentString( "MailServer", True )
      mailFile = sess.GetEnvironmentString( "MailFile", True )
      Set db = sess.GetDatabase( mailServer, mailFile )

'Get Inbox folder:
      Set view = db.GetView( "($Inbox)" )

'Loop through all documents in Inbox:
      Set doc = view.GetFirstDocument
      Do Until doc Is Nothing
            'to get document field:
            fld1 = doc.GetItemValue( "field1Name" )(0)

            'init SQL statement:
            strSQL = "INSERT INTO tblName VALUES(" & fld1 & "," & fld2 & "," & fld3)

            'write to Access through ADO using the function bellow:
            Call ADOExecSQL( strSQL )

            Set doc = view.GetNextDocument(doc)

How ADO works:

Public cnConn As Variant
Public strConn As String
Public strSQL As String

      strConn  = "my connection string"  'make this global

Function ADODbConnect
      ADODbConnect = 1
      On Error Goto ERROR_FUNCTION
      Set cnConn = CreateObject("ADODB.Connection")
      cnConn.Open strConn
      Exit Function
      ADODbConnect = 0
End Function

Function ADOExecSQL(strSQL As String)
      ADOExecSQL = 1
      On Error Goto ERROR_FUNCTION
      If ADODbConnect() = 0 Then Goto ERROR_FUNCTION
      cnConn.Execute strSQL
      Exit Function
      ADOExecSQL = 0
End Function

Function ADOGetRecordSet(strSQL As String,  rsRec As Variant)
      ADOGetRecordset = 1
      On Error Goto ERROR_FUNCTION
      Set rsRec = CreateObject("ADODB.Recordset")
      If ADODbConnect() = 0 Then Goto ERROR_FUNCTION
      rsRec.Open strSQL, cnConn, 3, 2
      Exit Function
      ADOGetRecordset = 0
End Function

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.