CODEWITHSUNDEEP
vbalotus-notes
hitek
hitek

Reputation: 372

lotus notes to VBA

I'm stuck with this problem for days

I have to read a specific mailbox in lotus notes and bring all the content into an excel spread sheet

but so far I have only been able to read the default inbox and have no way of switching to the other mailbox. I 'm really new to VBA can any one help me sort this out

here is the code I 'm using

Set NSession = CreateObject("Notes.NotesSession")
   'get the name of the mailfile of the current user
DbLocation = NSession.GETENVIRONMENTSTRING("mail/mailbox", True)

'Get the notesdatabase for the mail.
Set NMailDb = NSession.GETDATABASE("mailboxer", DbLocation)
MsgBox (DbLocation)

I get an empty msgbox poping up

Upvotes: 0

Views: 6155

Answers (3)

Ed Schembor
Ed Schembor

Reputation: 8550

As D. Bugger stated, you need to be sure you have the Notes client installed on the same client machine your VB code will run, and you need to be sure the folder with the nnotes.exe file and the folder with the notes.ini file are in your environment path. (If not, you will get a COM error instantiating the Notes.NotesSession object.

If this helps, here is some starter code - not tested, but a rough guide... This walks through all documents in a Notes mailbox database, ignores anything except email documents (which have the form field = "Memo") and grabs some fields from each email. 

Public Sub exportNotesMail(MailServer$, MailDBPath$)
    Dim mailDb As Object, doc As Object, alldocs As Object, Session As Object
    Set Session = CreateObject("Notes.NotesSession")
    Set mailDb = Session.GETDATABASE(MailServer, MailDbPath$)
    If mailDb.IsOpen = False Then mailDb.OPENMAIL
    Set alldocs = mailDb.AllDocuments
    Set doc = alldocs.GetFirstDocument
    while not (doc is nothing)
        If doc.GetItemValue("Form")(0) = "Memo" Then
            thisSubject = doc.getItemValue("Subject")(0)
            thisFrom = doc.getItemValue("From")(0)
            ' get more field values
            ' Export to Excel or wherever
        End If
        Set doc = alldocs.GetNextDocument(doc)
    Next i

    ' done
End Sub

call exportNotesMail ("MyServer", "mail\myMailFile.nsf")

Upvotes: 0

D.Bugger
D.Bugger

Reputation: 2359

Some thoughts:

  • use Lotus.NotesSession if you don't have to interact with the Notes UI (Lotus.NotesSession is COM based, whereas Notes.NotesSession is OLE based)
  • make sure the user of the Notes client on the workstation running your VBA application has the rights require to open and read the mailbox

Upvotes: 1

Richard Schwartz
Richard Schwartz

Reputation: 14628

GetEnvironmentString() reads the notes.ini file. I'm not sure that's what you really want to be doing. Just from the syntax, I think you're using "mail/mailbox" as a placeholder for the actual path to the mailbox that you're looking for. E.g., you're really trying to read the mail from something like "mail/jsmith.nsf". (If I'm wrong, and you really do want to be reading the notes.ini file to get the location of the mail file, then your problem is that "mail/mailbox" is not a valid key for an ini file entry.)

My next assumption is that the Domino server where the mailbox lives is called "mailboxer", because that's what you're putting in the first argument of GetDatabase().

If I'm right about these things, then what what you need is 

Set NMailDb = NSession.GETDATABASE("mailboxer", "mail/mailbox")

where "mail/mailbox" is replaced with the actual path to the mailbox that you are trying to open.

Upvotes: 3

Related Questions