Access VBA: Lotus Notes: select notes template & fill with data from Access

Question

I am facing a strange problem: I have an VBA script in Access, which controls Lotus Notes the following way when clicking a button:

connect to and opens a DB
searches for a certain template
generates a new mail (in uiview)
fills data into body and subject
brings Lotus Notes to the front

Now in the case that an user clicks that button in my VBA form again the same procedure results in an error, as

Set nMailDB = nSession.OpenDatabase(......)

results in "Nothing"!?!

Is there any way to get the titles/ names or whatever of any TAB currently opened in Lotus Notes?

Sub createNotesMail(frm As Form)
Dim nMailDb As Object
Dim nMailDoc As Object
Dim nMailTempDoc As Object
Dim nSession As Object
Dim nView As Object
Dim nWorkspace As Variant
Dim nCursor As Object
Dim nStationery As Object
Dim StationeryName As String

'Start Lotus Notes Session
Set nSession = CreateObject("Notes.NotesSession")
Set nMailDb = nSession.GetDatabase(NOTES_SERVER, NOTES_MAILIN)

' Open the Stationery View
Set nView = nMailDb.GetView("Stationery")
Set nWorkspace = CreateObject("Notes.NotesUIWorkspace")
Set nCursor = nView.GetFirstDocument

Do While Not nCursor Is Nothing
    ' get stationery value
    StationeryName = nCursor.GetItemValue("MailStationeryName")(0)
    ' match form template selection versus stationery
    If StationeryName = frm.TEMPLATE_NAME Then
        ' grab users signature from a temp UI document
        Set nMailTempDoc = nMailDb.CreateDocument
        Set nMailTempDoc = nWorkspace.Editdocument(True, nMailTempDoc)
        With nMailTempDoc
            .gotofield "Body"
            .SelectAll
            .Copy
            .Close
        End With

        ' create new document from stationery template, find and replace <PLACEHOLDERs>
        Set nMailDoc = nWorkspace.Editdocument(False, nCursor)
        With nMailDoc
            .gotofield "Body"
            .FINDSTRING "<SIGNATURE>"
            .Paste

            copyToClipboard frm.RECIPIENT
            .gotofield "Body"
            .FINDSTRING "<NAME>"
            .Paste

            copyToClipboard frm.DUEDATE
            .gotofield "Body"
            .FINDSTRING "<DUEDATE>"
            .Paste

            copyToClipboard frm.ORDERID
            .gotofield "Body"
            .FINDSTRING "<ORDERID>"
            .Paste

            Dim SubjectTemp As String
            SubjectTemp = .FIELDGETTEXT("Subject")
            SubjectTemp = Replace(SubjectTemp, "<ORDERID>", frm.ORDERID)
            SubjectTemp = Replace(SubjectTemp, "<DUEDATE>", frm.DUEDATE)
            .FIELDSETTEXT "Subject", SubjectTemp

            .FIELDSETTEXT "EnterSendTo", frm.RECIPIENT
        End With
        GoTo nMail_OK
    Else
        Set nCursor = nView.GetNextDocument(nCursor)
    End If
Loop

MsgBox "Error: Lotus Notes Template already opened or not found!"

nMail_OK:

Set nMailDb = Nothing
Set nMailDoc = Nothing
Set nMailTempDoc = Nothing
Set nSession = Nothing
Set nView = Nothing
Set nWorkspace = Nothing
Set nCursor = Nothing
Set nStationery = Nothing
End Sub

As far as I know not, this is managed by Notes. Do you close the notessession at the end of the button? Maybe some objects got recycled? More Code more help :-) — umeli

Unknown Unknown · Accepted Answer · 2018-05-01T06:17:16

As previously answered by Thommy Tomka in an edit to the question:

I found a possible reason: If the template, I choose to open and fill with data from my database is already open in Lotus Notes, then "MailStationeryName" is empty! As soon as the template is closed, the value is back to what should be:

StationeryName = nCursor.GetItemValue("MailStationeryName")(0)

Now this works for me! I will extend the script to check, if any of the Lotus Notes templates, which are used by my database is already opened, which means a user has not finished a task.

NOTES_SERVER and NOTES_MAILIN are CONSTs declared elsewhere.

Access VBA: Lotus Notes: select notes template & fill with data from Access

1 Answers