Sending Email with Lotus Notes - (Different Database) - Excel VBA

Question

I have a tiny problem with a VBA that I prepared to send emails from a database in lotus notes (other than the main database)

When I send the same email from the main database in lotus notes, (username database) I have no problems, it runs smoothly. However, the same coding, I just change the server and the database name, it sends the emails but with a message screen in lotus notes as "do you want to save your changes?" I need to select yes or no, then it sends the emails. As I said, the only difference is the database and the server in the coding.

Here is the message in Lotus Notes:

Here is the VBA code.

Sub SendWithLotus()
Dim NSession As Object
Dim NDatabase As Object
Dim NUIWorkSpace As Object
Dim NDoc As Object
Dim NUIdoc As Object
Set NSession = CreateObject("Notes.NotesSession")
Set NUIWorkSpace = CreateObject("Notes.NotesUIWorkspace")
Set NDatabase = NSession.GETDATABASE("XXXXX/XXX/XXXServer", "mail\YYYYY")

'=> this is the main change, if I want to send from the main mail database, I don't enter anything inside the brackets and everyting works fine.

If Not NDatabase.IsOpen Then
    NDatabase.OPENMAIL
End If

'Create a new document

Set NDoc = NDatabase.CREATEDOCUMENT

With NDoc
    .SendTo = Range("O8").Value
    .CopyTo = ""
    .Subject = Range("O7").Value

    'Email body text, including marker text which will be replaced by the Excel cells

    .body = vbNewLine & vbNewLine & _
        "**Cell Contents**" & vbNewLine & vbNewLine & _
        ""

    .Save True, False
End With

'Edit the just-created document to copy and paste the Excel cells into it

Set NUIdoc = NUIWorkSpace.EDITDOCUMENT(True, NDoc)

With NUIdoc

    'Find the marker text in the Body item

    .GOTOFIELD ("Body")
    .FINDSTRING "**Cell Contents**"
    '.DESELECTALL            'Uncomment to leave the marker text in place (cells are inserted immediately before)

    'Replace it with the Excel cells

    Sheets("Sheet1").Range("A1:L58").CopyPicture xlScreen, xlBitmap
    .Paste
    Application.CutCopyMode = False
    .Send
    .Close
    NDoc.SAVEMESSAGEONSEND = True
End With

Set NSession = Nothing
    NDatabase = Nothing
    NDoc = Nothing
End Sub

The reason might be settings of the different database or I might need to code to close that message box in Lotus Notes, but I don't know how.

Search for the send keys method of VBA, to send the enter key to dismiss the message. — Luuklag
I'm looking very hard for that :) no chance as of now. Thanks. — Bilal Çelik
Your comment about the "main change" is ambiguous. Which brackets, on which line of code, are you talking about? How about just showing both versions, with one commented out? — Richard Schwartz
You're working with both NDoc and NUIdoc. That's a bit confusing. You're not showing how you're creating either of them. That means we have to guess what's going on. I see why you're using the NUIdoc, but from that point on you should probably stop working with NDoc, but I see that you're calling NUIDoc.Send and NUIDoc.Close method, and then after you've already done that you're setting NDoc.SaveMessageOnSend. That makes little sense. — Richard Schwartz

Duston Duston · Accepted Answer · 2018-05-11T13:43:32

It's hard to say, but one database may have a different default form than the other, and your code may be using it. I'd suggest adding a field on the Notes document called SaveOptions and set the value to 0. Try:

NUIDoc.Document.ReplaceItemValue("SaveOptions",0)

before sending.

Sending Email with Lotus Notes - (Different Database) - Excel VBA

1 Answers