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?
mbonaciConnect With a Mentor Commented:
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
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.
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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

All Courses

From novice to tech pro — start learning today.