Trouble with sending emails with vba via IBM Notes-CodePudding

with the function below, I have the option of sending e-mails from Excel via IBM Notes. Basically, it works very well. However, I recently had to revise the code, as the message text was always inserted under the IBM Notes signature. I was able to solve this problem, but unfortunately two problems have now emerged that I did not had before.

I am grateful for every tip and every help!

The problems

The "Save" function no longer works, i.e. IBM Notes also saves the e-mail in the "Sent" folder if I do not want it (parameter blnSaveEMail = false).

The second problem has to do with my work context: I have two email accounts. A personal service e-mail address [email protected] (mailfile: jdoe.nsf) and a branch e-mail address [email protected] (mailfile: mybranch.nsf). As far as I could determine, both mail files are in the same base directory. If I use the code below with my personal e-mail, the parameter blnQuickSend = true works without problems, if I use my branch e-mail address, IBM Notes asks me whether I want to save the changes, although I would like to send an e-mail without asking.

I hope I was able to describe my issue clearly and understandably. I thank you for your attention!

Warm greetings from Dresden Sergeij

PS: I'am a german native :), thankfully Google helped me a lot to translate my problem in english.

The code

Public Function Send_EMail( _
    varRecipient As Variant, _
    varCopyTo As Variant, _
    varBlindcopyTo As Variant, _
    strSubject As String, _
    strMessage As String, _
    strAttachement As String, _
    Optional blnSaveEMail As Boolean = True, _
    Optional blnQuickSend As Boolean = False, _
    Optional strAlternative_Mailfile As String _
        ) As Boolean

' Lotus Notes Client 4.5.x or newer requiered ' Parameters: ' varRecipient, varCopyTo, varBlindcopyTo = a STRING or STRING-Array with email-adresses ' strSubject = a STRING that represents the issue/title of the email ' strMessage = a STRING that represents the content of the email ' strAttachement = a STRING with filepaths (one or more), seperated by ";" ' blnSaveEMail = a BOOLEAN, that represents whether the email is saved in the "Sent"-folder or not ' blnQuickSend = a BOOLEAN, that represents whether the email will be sent immediately ' strAlternative_Mailfile = a STRING with an alternative Mailfile-name (*.nsf)

    Dim objLotusNotes As Object
    Dim objMaildatabase As Object 'Die Maildatabase
    Dim strMailServer As String 'Der Mailserver
    Dim strMailFile As String ' Die Maildatei
    Dim objEMail As Object 'Die E-Mail in IBM Notes
    Dim objAttachement As Object 'Das Anlage Richtextfile Object
    Dim objSession As Object 'Die Notes Session
    Dim objEmbedded As Object 'Attachement
    Dim arrAttachements() As String 'Liste mehrere Anhänge
    Dim lngIndex As Long
    Dim strFilepath As String
    Dim objNotesfield As Object 'Datenfeld in IBM Notes
    Dim objCurrentEMail As Object 'Aktuelle E-Mail

'Start an IBM Notes Session

    Set objSession = CreateObject("Notes.NotesSession")

'Open IBM-Notes-Database

    strMailServer = objSession.GetEnvironmentString("MailServer", True)
    
    If VBA.Len(strAlternative_Mailfile) = 0 Then
        strMailFile = objSession.GetEnvironmentString("MailFile", True)
    Else
        strMailFile = "mail/" & strAlternative_Mailfile
    End If
    
    Set objMaildatabase = objSession.GETDATABASE(strMailServer, strMailFile)

'Check whether the maildatabase is open or not 'Throws an error, if the database is not open

    If Not objMaildatabase.IsOpen Then objMaildatabase.OPENMAIL

'Create new email

    Set objEMail = objMaildatabase.CREATEDOCUMENT

'Put content in fields

    Set objNotesfield = objEMail.APPENDITEMVALUE("Subject", strSubject)
    Set objNotesfield = objEMail.APPENDITEMVALUE("SendTo", varRecipient)
    Set objNotesfield = objEMail.APPENDITEMVALUE("BlindCopyTo", varBlindcopyTo)
    Set objNotesfield = objEMail.APPENDITEMVALUE("CopyTo", varCopyTo)

'Load workspace

    Set objLotusNotes = CreateObject("Notes.NotesUIWorkspace")

'Create new email-document

    objLotusNotes.EDITDOCUMENT True, objEMail

'Select the current email

    Set objCurrentEMail = objLotusNotes.CurrentDocument

'Put content into email

     objCurrentEMail.GotoField "Body"
     objCurrentEMail.InsertText strMessage

'Add attachements

    arrAttachements = VBA.Split(strAttachement, ";")
     
    For lngIndex = LBound(arrAttachements) To UBound(arrAttachements)
        strFilepath = arrAttachements(lngIndex)
        If strFilepath <> "" And VBA.Dir(strFilepath) <> "" Then
            Set objAttachement = objEMail.CREATERICHTEXTITEM("Attachment" & lngIndex)
            Set objEmbedded = _
                objAttachement.EMBEDOBJECT(1454, "", strFilepath, "Attachment" & lngIndex)
        End If
    Next

'Set if email should be saved or not

    objEMail.SAVEMESSAGEONSEND = blnSaveEMail
    
    'If blnSaveEMail = True Then objEMail.Save False, False, False

'Check, whether the email should be sent immediately or not

    If blnQuickSend = True Then

'Send email

        objCurrentEMail.Send False, varRecipient

'Close email

        objCurrentEMail.Close
    
    Else
    
        objEMail.Save False, False, False
        
    End If

'Return TRUE

    Send_EMail = True
    

End Function

CodePudding user response：

Ok... where should I start... there are some logical errors in your code based on not understanding the methods you use and the difference between frontend- and backend- classes...

Let's begin at the top:

'Check whether the maildatabase is open or not 'Throws an error, 
'if the database is not open
If Not objMaildatabase.IsOpen Then objMaildatabase.OPENMAIL

Your comment is wrong. No error is thrown at all. If your constructed path (variable strMailFile) is wrong or the database cannot be accessed then this line will make sure to fallback to the mailfile configured in your location document in Notes Client.

'Create new email-document
objLotusNotes.EDITDOCUMENT True, objEMail

Again: Comment is wrong. What this command does is: It opens the email that you created in backend (represented by variable objEMail) in the frontend.

'Select the current email
Set objCurrentEMail = objLotusNotes.CurrentDocument

and assigns it to a NotesUIDocument- frontend- variable (select the current email is wrong). As "EDITDOCUMENT" already returns as NotesUIDocument, you could shorten this like this:

'Open eMail in frontend and assign to NotesUIDocument variable
Set objCurrentEMail = objLotusNotes.EDITDOCUMENT(True, objEMail)

After having created a frontenddocument you still continue to manipulate the (now linked) backend document. You should move the creation of the frontend all the way down to the end of your code as having a document open in frontend does not work well with manipulating the same document in backend, especially when handling NotesRichtextItems and attachments. So move the above lines just below the for- loop.

'Set if email should be saved or not
objEMail.SAVEMESSAGEONSEND = blnSaveEMail

Yes... but no: You set the property SAVEMESSAGEONSEND to the backend document objEMail. Unfortunately the frontend- document objCurrentEMail does not care at all for this. To have your code obey this option, you would have to use the send- method of objEMail, not the send- method of objCurrentEMail. If you want the frontend to not save a document that it sends, you need to do it differently by setting a field called "SAVEOPTIONS" to "0":

objEMail.ReplaceItemValue( "SAVEOPTIONS", "0" )


'Send email
 objCurrentEMail.Send False, varRecipient

regarding your comment: almost... unfortunately you try the NotesDocument backend method "send" (that has 2 parameters) against the NotesUIDocument- Object "objCurrentEMail. NotesUIDocument has a send method as well, but it does not have any parameters..normally an error should be thrown here....

EITHER you try to send the backend:

objEMail.Send False, varRecipient

OR you send it in the frontend:

objCurrentEMail.Send

Your "objCurrentEMail.Close" will always ask you if you want to save the document unless you have set SAVEOPTIONS = "0". If you really want to save the document after sending, use

objCurrentEMail.Save

before the close.

Hope that helps you sort out some of the issues.

CodePudding user response：

thank you very much for your help. I'm not good at working with the NOTES-API.

I've tried to follow your instructions, but unfortuneately the problems are still there.

Here is my code with your suggestions. I think, I didn't place the codeline in the right order. Could you please look over it again?

    Public Function Send_EMail( _
    varRecipient As Variant, _
    varCopyTo As Variant, _
    varBlindcopyTo As Variant, _
    strSubject As String, _
    strMessage As String, _
    strAttachement As String, _
    Optional blnSaveEMail As Boolean = True, _
    Optional blnQuickSend As Boolean = False, _
    Optional strAlternative_Mailfile As String _
        ) As Boolean
 
    Dim objLotusNotes As Object
    Dim objMaildatabase As Object 'Die Maildatabase
    Dim strMailServer As String 'Der Mailserver
    Dim strMailFile As String ' Die Maildatei
    Dim objEMail As Object 'Die E-Mail in IBM Notes
    Dim objAttachement As Object 'Das Anlage Richtextfile Object
    Dim objSession As Object 'Die Notes Session
    Dim objEmbedded As Object 'Attachement
    Dim arrAttachements() As String 'Liste mehrere Anhänge
    Dim lngIndex As Long
    Dim strFilepath As String
    Dim objNotesfield As Object 'Datenfeld in IBM Notes
    Dim objCurrentEMail As Object 'Aktuelle E-Mail