[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1346
  • Last Modified:

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
0
Hammer8
Asked:
Hammer8
  • 3
  • 2
1 Solution
 
SysExpertCommented:
just text ?
What about pictures, attachments, embedded options ?


What are you really trying to do ?

I hope this helps !
0
 
Hammer8Author Commented:
just text is sufficient.  I want to store the txt in an access db.  thanks.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now