Serien-Mail aus Excel mit LN

[VBAstler]

Moin allerseits,

vielleicht vorab zum Szenario meiner Anfrage: Ich betreibe das Forum VBAsteleien.de, das sich mit VBA in der MS-Office-Familie mit Schwerpunkt Excel beschäftigt. Dort wurde vor einiger Zeit ein Thema (siehe Titel) aufgemacht, dessen XL-VBA-Seite in mehreren Versionen gelöst werden konnte. Versendet sollen sowohl einzelne als auch Serien-Mails mit Daten von Empfänger, CC, Betreff bis hin zu  Body und vor allem einem Anhang aus XL per IBM Notes 10.

Leider gibt es auf VBAsteleien.de niemand (außer dem Fragesteller), der LN zur Verfügung hat. Der folgende Code hat wenigstens schon mal erfolgreich zum Einzelversand geführt. Wenn damit allerdings Verteiler mit > 50 Empfängern erreicht werden sollen, wird die Sache träge: von mehr als drei Minuten wird berichtet. Wir suchen also nach einer Lösung, die Serien-Mails deutlich beschleunigt und haben über diese Konzepte nachgedacht:

• Tatsächlich sequenziell Einzelmails, die dann zuerst im LN Postausgang abgelegt werden und anschließend versandt werden. Dafür haben wir aber keinen LN-Code gefunden
• Alternativ der direkte Versand jeder einzelnen Mail und Rückkehr zu VBA für die nächste (m.E. die schlechteste Methode). Hier zu nutzen wir bisher den Code unten, wobei ich vermute, dass sich der durch das gezielte, nicht für jede Mail wiederholte Laden/Entladen der LN-COM-Objekte beschleunigen ließe.

Der Code liegt in einem allg.VBA-Modul:

Code

Option Explicit
Const EMBED_ATTACHMENT As Long = 1454
 
Sub SingleMail()
Dim sMailTo As String, sCopyTo As String, sSubject As String, sPDF As String, sBody As String, iMails As Integer
Dim sht As Worksheet
    
    ' Schleife durch alle Sheets des WorkBooks, in VBA etwas gefiltert, in LN also ohne Bedeutung
    With ActiveSheet
        
        ' Füllen der Variablen
        sMailTo = .Cells(1, 1)
        sCopyTo = .Cells(1, 2)
        sSubject = .Cells(1, 3)
        sPDF = ThisWorkbook.Path & "\Test " & Date & " " & Timer & ".pdf"
        sBody = "Das Protokoll vom " & Date
        iMails = 1
        
        'erstellt ein PDF des aktiven Sheets im Verzeichnis des Aufrufs
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=sPDF, _
        Quality:=xlQualityStandard, IncludeDocProperties:=False, _
        IgnorePrintAreas:=True, OpenAfterPublish:=True
        
        ' Übergabe der Variablen an LN
        Send_LN_Mail sMailTo, sCopyTo, sSubject, sPDF, sBody, iMails
    
    End With
 
End Sub
 
Sub Send_LN_Mail(sMailTo As String, sCopyTo As String, sSubject As String, sPDF As String, sBody As String, iMails As Integer)
Dim LN_Session As Object, LN_Database As Object, LN_Document As Object
Dim LN_Workspace As Object, LN_EmbedObject As Object, LN_attachement As Object
 
' Laden der Lotus COM Objekte
    Set LN_Session = CreateObject("Notes.NotesSession")
    Set LN_Database = LN_Session.GETDATABASE("", "")
 
'Falls Notes nicht geöffnet ist
    If LN_Database.IsOpen = False Then LN_Database.OPENMAIL
    
' Email erstellen
    Set LN_Document = LN_Database.CreateDocument
    Set LN_attachement = LN_Document.CreateRichTextItem("sPDF")
    Set LN_EmbedObject = LN_attachement.EmbedObject(EMBED_ATTACHMENT, "", sPDF)
 
    With LN_Document
        .Form = "Memo"
        .sendTo = sMailTo
        .copyTo = sCopyTo
        .Subject = sSubject
        .body = sBody
        .SaveMessageOnSend = True
        .PostedDate = Now()
    End With
 
' Jetzt Notes los ...!
    Set LN_Workspace = CreateObject("Notes.NotesUILN_Workspace")
    Call LN_Workspace.EDITDOCUMENT(True, LN_Document).GOTOFIELD("Body")
    
' Speicher leeren
    Set LN_EmbedObject = Nothing
    Set LN_attachement = Nothing
    Set LN_Document = Nothing
    Set LN_Database = Nothing
    Set LN_Session = Nothing
 
    MsgBox iMails & " Mail(s) wurde(n) erstellt. Bitte zu NOTES wechseln"
 
End Sub

Als Idee würde ich für einen Mehrfachversand per Schleife einen Teil der Set LN_... = Nothing an den Schluss der Schleife stellen. Das könnte dann vielleicht so aussehen:

Code

Option Explicit
Const EMBED_ATTACHMENT As Long = 1454
 
Sub MultiMail()
'XL-Variablen:
Dim sMailTo As String, sCopyTo As String, sSubject As String, iMails As Integer
Dim sPDF As String, sBody As String, sht As Worksheet
'LN-Variablen:
Dim LN_Session As Object, LN_Database As Object, LN_Document As Object
Dim LN_Workspace As Object, LN_EmbedObject As Object, LN_attachement As Object
    
'>>>>> XL-relevant: <<<<<
 
    'Schleife durch alle Sheets des WorkBooks per VBA gefiltert
    Application.ScreenUpdating = False
    For Each sht In ThisWorkbook.Sheets
    
        'Filter
        If Left(sht.CodeName, 4) <> "tab_" Then
            sht.Activate
            With ActiveSheet
            
                'Füllen der Variablen
                sMailTo = .Cells(1, 1)
                sCopyTo = .Cells(1, 2)
                sSubject = .Cells(1, 3)
                sPDF = ThisWorkbook.Path & "\Test " & Date & " @ " & Timer & ".pdf"
                sBody = "Das Protokoll vom " & Date
                iMails = iMails + 1
                
                'erstellt ein PDF des aktiven Sheets im Verzeichnis des Aufrufs
                .ExportAsFixedFormat Type:=xlTypePDF, Filename:=sPDF, _
                Quality:=xlQualityMinimum, IncludeDocProperties:=False, _
                IgnorePrintAreas:=True, OpenAfterPublish:=False
            End With
 
''>>>>> LN-relevant: <<<<<
 
            'Laden der Lotus COM Objekte
            Set LN_Session = CreateObject("Notes.NotesSession")
            Set LN_Database = LN_Session.GETDATABASE("", "")
 
            'Falls Notes nicht geöffnet ist
            If LN_Database.IsOpen = False Then LN_Database.OPENMAIL
 
            'Email erstellen
            Set LN_Document = LN_Database.CreateDocument
            Set LN_attachement = LN_Document.CreateRichTextItem("sPDF")
            Set LN_EmbedObject = LN_attachement.EmbedObject(EMBED_ATTACHMENT, "", sPDF)
 
            With LN_Document
                .Form = "Memo"
                .sendTo = sMailTo
                .copyTo = sCopyTo
                .Subject = sSubject
                .body = sBody
                .SaveMessageOnSend = True
                .PostedDate = Now()
            End With
 
            'Jetzt Notes es los ...!
            Set LN_Workspace = CreateObject("Notes.NotesUILN_Workspace")
            Call LN_Workspace.EDITDOCUMENT(True, LN_Document).GOTOFIELD("Body")
 
            'Speicher für aktuelle Mail leeren
            Set LN_Document = Nothing
            Set LN_attachement = Nothing
 
''>>>>> XL-relevant: <<<<<
        
        'Filter-Ende
        End If
        
    'Schleifen-Return
    Next
    Application.ScreenUpdating = True
        
        'Speicher für LN leeren
            Set LN_EmbedObject = Nothing
            Set LN_Database = Nothing
            Set LN_Session = Nothing
            
        MsgBox iMails & " Mails für den Versand vorbereitet, bitte zu Notes wechseln."
 
End Sub

Ganz schön viel auf einmal und für einen ersten Beitrag, ich weiß. Vielleicht kann ja trotzdem jemand (wahrscheinlich mit nur wenigen Zeilen  ) helfen.

Dafür schon vorab ganz lieben Dank! die wir natürlich auch mit einem entsprechenden Link hierher belohnen 

[Erik Schwalb]

Ich habe gesehen, dass Du "eigentlich" eine Lösung für IBM Notes 10 in Form von VBA Code suchst.

Daher hier nur zwei Hinweise als FYI.

a) IBM Notes 10 wird seitens HCL seit Juni 2024 nicht mehr supported. Für die produktive Nutzung empfiehlt HCL ein Update auf eine aktuelle HCL Notes Version.

b) Mit HCL Notes 14 wurde eine Serienmailfunktion eingeführt, die die Empfängerdaten aus einer Excel Datei bezieht.
https://help.hcl-software.com/notes/14.0.0/client/mail_create_mailmerge.html?scLang=de

Die Lösung unterstützt auch das Versenden von individuellen Anhängen pro Empfänger.
Der Code ist quelloffen im Mailtemplate implementiert und läuft m.W. auch in einem HCL Notes 12 Client.

[VBAstler]

Moin Erik,

herzlichen Dank für Deine Hinweise. Das Problem ist nur, dass sich die Aufgabe genau einem Mitarbeiter einer Firma stellt, deren IT von ihm (and so do I) als Mittelalter bezeichnet wird. Kein Witz, die arbeiten dort noch mit Office 2013 (drei-zehn!). IT hat in diesem Logistik-Betrieb offenbar erheblich weniger Bedeutung, als die steigenden Dieselkosten ... Den EOL-Hinweis werde ich aber natürlich weitergeben.

Was Du unter b) beschreibst, würde ja alle VBAsteleien lösen und das wäre natürlich prima. Ich vermute aber mal, dass nur ein Weg über den altertümlichen Umweg möglich sein wird.

Hauptaufgabe bleibt also der LN-COM-Code für den direkten Versand einzelner Mails, bzw. das Vorbereiten des-PN-Postausgangs zum performanten Komplett-Versand mehrerer Mails von dort. Gibt es da trotzdem eine Chance?

Grüße

[jBubbleBoy]

Was die Ausführung verlangsamen wird, ist die große Anzahl an geöffneten Mails in Notes, dies sollte umgangen werden, indem diese entweder nicht geöffnet werden oder indem Pausen in das Skript eingefügt werden, damit der Benutzer die Mails versenden kann. Es wird auch für den Notes-Anwender unübersichtlich, wenn mehr als 50 Tabs geöffnet sind.

Neben dem Öffnen kann man die Mails auch gleich mit LN_Document.send(false) versenden oder nur mit LN_Document.save(true, false) speichern - diese nicht versendeten Mail-Dokumente befinden sich dann im Ordner Entwürfe.

Was man nicht machen sollte, ist das Feld "PostedDate" zu setzen, das wird beim Versenden von Domino gesetzt und die Eigenschaft .SaveMessageOnSend = True würde ich auch nicht setzen, da es in der Mail-DB eine Aktion "Senden" gibt, dieser versendet und schließt das Maildokument.
In einem normalen Mail-Dokument gibt es nur ein Inhaltsfeld, das Body-Feld, hier soll der Text und der Anhang abgelegt werden, mit CreateRichTextItem wird das Body-Feld erstellt, mit AppendText wird der Text und mit EmbedObject wird der Anhang eingefügt.

Das wären meine Antworten und Hinweise auf deine Fragen, ohne den Code zu testen.

[VBAstler]

Moin MiB* Erik,

au weia, dass da für jede Mail ein Tab geöffnet wird, ist natürlich so deppert, wie noch was. Das erklärt vermutlich die drei Minuten.

Der Hinweis auf .send() und save(,) [Nachtrag: ohne die Klammern nimmt der VBE die Zeilen, mit motzt er] lässt mich hoffen, dass ich auch ohne jegliche LN-Testumgebung eine Chance habe, der  einer Lösung einen Schritt näher zukommen.

Deinen dritten Absatz kann ich mangels Kenntnissen nicht nachvollziehen, außer dass eben zwei Zeilen (P...Date & SaveMessage...) mal nur Unfug anrichten. Also werde ich sie auskommentieren. Dann würden aus XL nur LN_MailTo, LN_Subject und LN_PDF (Attachement) übergeben.

Nach der Übergabe aus XL mit SaveMessage... liegen die Mails dann in den Entwürfen und können aus LN verschickt werden. Ich hoffe, das habe ich richtig verstanden.

Lieben Dank, damit bin ich schon mal einen (von X) Schritten weiter 

*Bezug zu Deiner Signatur 

[jBubbleBoy]

Code schreiben ohne ihn zu testen ist auch ein bisschen wie "Mission Impossible", aber eine KI macht das ja auch so

Was ich im 3. Abschnitt sagen wollte ist, dass es in Notes nur ein Feld für den Mailtext und den Anhang gibt, in deinem Code werden 2 Felder angelegt, einmal für den Text und einmal für den Anhang. Das macht man eigentlich nicht, auch wenn es geht.

[VBAstler]

Moin Erik,

das mit dem Coden ohne Testumgebung sehe ich eher als "Nebel des Grauens" - bei "Mission Impossible" gibt's immer noch ein Happy End ..

Entsprechend weit weg bin ich natürlich von der von Dir beschriebenen Best Practice: Ich bin heil froh, wenn's irgendwie läuft ohne Schaden anzurichten.

Deinem Hinweis folgend, versuche ich aber gerne noch Body &  Attachement zusammenzuführen und bin auf diese Zeile gestoßen:

Code

Set LN_attachement = LN_Document.CreateRichTextItem("sPDF")
Set LN_EmbedObject = LN_attachement.EmbedObject(EMBED_ATTACHMENT, "", sPDF)

sPDF ist der Anhang. Wäre ein sBody statt der Anführungszeichen besser?

Lieben Dank!

[jBubbleBoy]

Es gibt nur das Feld Body, dieses darf nur einmal angelegt werden mit CreateRichTextItem("Body"), es gibt bei deinem Code aber noch eine Zuweisung mit .Body = sBody, das muss mit AppendText ersetzt werden.

https://help.hcl-software.com/dom_designer/10.0.1/basic/H_APPENDTEXT_METHOD.html

[VBAstler]

Mein Fragesteller hat eben den Code für den direkten Einzelversand und die Ablage mehrerer Mails in den Entwürfen bekommen und hat nun die schöne Aufgabe zu testen. Ich warte auf die Fehlermeldungen bzw. Ergebnisse in LN. Die AppendText-Änderung habe ich schon vorbereitet, falls es daran scheitert.

Noch ein Nachfrage: In der SingleMal Version werden alle LN-COM-Objekte auf Null gesetzt:

Code

Call LN_Workspace.EDITDOCUMENT(True, LN_Document).GOTOFIELD("Body")
    
' Speicher leeren
    Set LN_EmbedObject = Nothing
    Set LN_attachement = Nothing
    Set LN_Document = Nothing
    Set LN_Database = Nothing
    Set LN_Session = Nothing

Um unnötige Sets zu vermeiden, habe ich das in der MultiMail-Version aufgeteilt:

Code

 'Speicher für aktuelle Mail leeren
            Set LN_Document = Nothing
            Set LN_attachement = Nothing
 
''>>>>> XL-relevant: <<<<<
        
        'Filter-Ende
        End If
        
    'Schleifen-Return
    Next
    Application.ScreenUpdating = True
        
        'Speicher für LN leeren
            Set LN_EmbedObject = Nothing
            Set LN_Database = Nothing
            Set LN_Session = Nothing

Kann das klappen???

Viele Dank!

[VBAstler]

Moin allerseits,

in der Hoffnung, dass Ihr mich Fossilienschubser noch nicht ganz aufgeben habt, doch noch ein Frage: Wir haben jetzt funktionierende Codes für direkte Single- und Multimails über die Entwürfe. Per Multimail benötigt die Bastelei jeweils etwa sechs Sekunden. Lässt sich der oben vorgestellte Code noch irgendwie beschleunigen?

Schöne Grüße und Dank!

[Werner Götz]

Die Mails werden nach wie vor im Notes Client geöffnet und müssen dort dann noch manuell versendet werden?
Das dürfte der Zeitfresser sein und dürfte doch gar nicht notwendig sein?

Viele Grüße
-Werner

[VBAstler]

Hallo Werner,

vielen Dank für den Hinweis und ich stimme Dir vollumfänglich zu: Entweder ab mit den MultiMails in die Entwürfe, wo ggf. mal noch eine individuelle Ergänzung vorgenommen werden kann, Ansonsten Direktversand. Aber auf jeden Fall alle ungeöffnet.

Wie kann ich das im Code abbilden (bin, wie gesagt, ohne jede Testumgebung ... )?

Schöne Grüße

[Werner Götz]

Ich würde mal folgendes ausprobieren:

Entweder

Code

            With LN_Document
                .Form = "Memo"
                .sendTo = sMailTo
                .copyTo = sCopyTo
                .Subject = sSubject
                .body = sBody
                .SaveMessageOnSend = True
                .PostedDate = Now()
                Call .Save(True, True)
            End With
 
            'Jetzt Notes es los ...!
            'Set LN_Workspace = CreateObject("Notes.NotesUILN_Workspace")
            'Call LN_Workspace.EDITDOCUMENT(True, LN_Document).GOTOFIELD("Body")

also am Ende des With das .Save und die beiden Zeilen LN_Workspace auskommentieren

oder

Code

            With LN_Document
                .Form = "Memo"
                .sendTo = sMailTo
                .copyTo = sCopyTo
                .Subject = sSubject
                .body = sBody
                .SaveMessageOnSend = True
                .PostedDate = Now()
                Call .Send(False)
            End With
 
            'Jetzt Notes es los ...!
            'Set LN_Workspace = CreateObject("Notes.NotesUILN_Workspace")
            'Call LN_Workspace.EDITDOCUMENT(True, LN_Document).GOTOFIELD("Body")

also am Ende des With das .Send und die beiden Zeilen LN_Workspace wieder auskommentieren.

Hierbei sind die Mails also sofort versendet ...



Viel Erfolg / Glück
-Werner

[VBAstler]

Moin Werner,

Du siehst mich bass erstaunt! Hatte ich doch gerade diese beiden Zeilen für den eigentlichen Trigger des Versands gehalten. Und dann alles noch ohne CreateWorkSpace ...?

LN macht mich fättisch (besser gesagt meine Unkenntnis darin;)). Aber es wird unverzagt (Dritt-)getestet und sobald ein Ergebnis zu haben ist, ist es hier verfügbar.

Herzlichen Dank und einen schönen Abend!