Extract header info & message to Access DB

We are creating an Access app to use to track & repond to problems users are having with our HR system and also to request queries & reports.  They currently eamil us at a department box.   We want to extract this information daily into Access tables as automatically as is possible.

How is this done?
Who is Participating?
MWConnect With a Mentor Commented:
Create a script agent using ODBC to fill the MS Access db.
I guess the department box is a mail-in database.
The difficulty are the body fields. It actually would make sense to create
three agents.

1. Agent1 would run on "new arrived email" and would execute agent2 and agent3
2. Agent2 would be a formula agent running again on "new arrived email".
The formula would be: FIELD BodyText := @Abstract([TextOnly];999;"Body")
3. The third agent would be a script agent running "Once command may be used"
with the following example code:

Sub Initialise
     Dim session  As NotesSession
     Dim curDB    As NotesDatabase
     Dim view     As NotesView
     Dim Doc      As NotesDocument

     Dim con      As ODBCConnection
     Dim qry      As ODBCQuery
     Dim res      As ODBCResultSet

     Set session = New notessession
     Set curDB   = session.CurrentDatabase
     Set view    = curDB.GetView("$Inbox")
     Set con = New ODBCConnection
     Set qry = New ODBCQuery
     Set res = New ODBCResultSet
     con.SilentMode = True
     If Not con.ConnectTo("ODBCConnectionName", "UserName", "Password") Then
         Exit Sub
     End If
     Set qry.Connection = con      
     Set doc = view.GetFirstDocument

     Do While Not doc Is Nothing
         qry.SQL = | INSERT AccessTableName
                     SET    From = | & doc.From(0) & |,
                            Subject = | & doc.Subject(0) & |,
                            Text = | & doc.BodyText(0) & |,
         Set res.Query = qry
         Call res.execute
         If Not res.GetError() = DBstsSuccess Then
            Exit Sub
         End If
         Call doc.removefromfolder("$Inbox")
         Set doc = view.getfirstdocument
End Sub
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.