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
Hammer8Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

SysExpertCommented:
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.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

mbonaciCommented:
And maybe even a better one for this purpose:
'ExportToAccess: 
 
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
		 		 Else
		 		 		 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
		 		 result.Execute
		 		 Set doc = dc.GetFirstDocument
		 		 While Not(doc Is Nothing)
		 		 		 result.AddRow
		 		 		 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))
		 		 		 result.UpdateRow
		 		 		 Set doc = dc.GetNextDocument(doc)
		 		 Wend
		 		 result.Close(DB_CLOSE)
		 		 con.Disconnect		 		 
		 Else
		 		 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
mbonaciCommented:
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)
      Loop


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:
      Exit Function
ERROR_FUNCTION:
      ADODbConnect = 0
      Goto EXIT_FUNCTION
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:
      Exit Function
ERROR_FUNCTION:
      ADOExecSQL = 0
      Goto EXIT_FUNCTION
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:
      Exit Function
ERROR_FUNCTION:
      ADOGetRecordset = 0
      Goto EXIT_FUNCTION
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.