Loop Through Emails in Notes Client in VBA Excel

Posted on 2009-04-22
Last Modified: 2013-12-18
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
Question by:Hammer8
    LVL 63

    Expert Comment

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

    What are you really trying to do ?

    I hope this helps !

    Author Comment

    just text is sufficient.  I want to store the txt in an access db.  thanks.
    LVL 22

    Expert Comment

    LVL 22

    Expert Comment

    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


    Author Comment

    Thank you for your help, but I was looking for VBA Excel code.  My setup does not allow me to use Notes Agents.  Hammer8
    LVL 22

    Accepted Solution

    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
          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
          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
          ADOGetRecordset = 0
          Goto EXIT_FUNCTION
    End Function

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    For beginners of Lotus Notes user this is important to know about the types of files and their location supported by IBM Notes. Mostly users are unaware about how many file types are created and what their usages are. This Article is fully dedicated…
    Notes Document Link used by IBM Notes is a link file which aids in the sharing of links to documents in email and webpages. The posts describe the importance and steps to create a Lotus Notes NDL file in brief.
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now