Opening a Lotus Note draft with Excel VBA, in order to replace just values

Question

I'm trying to write a Excel VBA code which allows me to automatically create and send a Lotus Notes Email. The problem I face is the difficulty to create a rich text Email, so I think it would be easier to open a draft email, with a marker text which will be replaced (for exameple PASTE EXCEL CELLS HERE) and then just:

.GotoField ("Body")
.FINDSTRING "PASTE EXCEL CELLS HERE"'

and replace.

Any help on how to open a certain draft email? Perhabs something as .CreateDocument property?

Thank you very much!

Answer 1

Others have proposed interesting concepts, but the most robust approach would be to use HTML in a MIME enitity that is mapped to the Body Rich Text item. Using NotesSession..Convertmime = False you can build the body as HTML and then send the message. Based on the post by Joseph Hoetzl here, the LotusScript equivalent is this:

Sub Initialize()
    Dim s As New NotesSession
    Dim db As NotesDatabase
    Dim stime as Single

    Dim alog As New NotesLog("debug")
    Call alog.OpenAgentLog()

    stime = Timer
    On Error GoTo eh

    Dim doc As NotesDocument
    Dim body As NotesMIMEEntity
    Dim header As NotesMIMEHeader
    Dim stream As NotesStream
    Dim child As NotesMIMEEntity
    Dim sendTo As String
    Dim subject As String

    s.Convertmime = False
    sendto = s.Effectiveusername
    subject = "Demo Message"

    Set db= s.Currentdatabase
    Set doc=db.Createdocument()
    Set stream = s.CreateStream
    Set body = doc.CreateMIMEEntity
    Set header = body.CreateHeader({MIME-Version})
    Call header.SetHeaderVal("1.0")
    Set header = body.CreateHeader("Content-Type")
    Call header.SetHeaderValAndParams({multipart/alternative;boundary="=NextPart_="})
    'Add the to field
    Set header = body.CreateHeader("To")
    Call header.SetHeaderVal(SendTo)

    'Add Subject Line
    Set header = body.CreateHeader("Subject")
    Call header.SetHeaderVal(subject)

    'Add the body of the message
    Set child = body.CreateChildEntity

    Call stream.WriteText("<h1>Demo HTML Message</h1>")
    Call stream.WriteText(|<table colspacing="0" colpadding="0" border="none">|)
    Call stream.WriteText(|<tr><td>cell 1.1</td><td>cell 1.2</td><td>cell 1.3</td></tr>|)
    Call stream.WriteText(|<tr><td>cell 2.1</td><td>cell 2.2</td><td>cell 2.3</td></tr>|)
    Call stream.WriteText(|<tr><td>cell 3.1</td><td>cell 3.2</td><td>cell 3.3</td></tr>|)
    Call stream.WriteText(|</table>|)
    Call stream.WriteText(|<div class="headerlogo">|)
    Call stream.WriteText(|<!-- ...some more HTML -->|)

    Call child.setContentFromText(stream, {text/html;charset="iso-8859-1"}, ENC_NONE)
    Call stream.Truncate 'Not sure if I need this
    Call stream.Close
    Call doc.CloseMIMEEntities(True)
    Call doc.replaceItemValue("Form", "Memo")
    Call doc.Send(False, sendTo)


es:
    Exit Sub
eh:
    Dim emsg$
    emsg = Error & " at " & Erl & " in " & s.Currentagent.name
    Call alog.logError(Err, emsg)
    MsgBox "ERROR: " & Err & ": " & emsg
    Resume es
End Sub

All of this should convert fairly easily to VBA in Excel. You can, of course be as complex as you want with your HTML.

Answer 2

The word "draft" is probably inappropriate here, but then again so is the word "template". Both have specific meanings in Lotus Notes that aren't what you really want. Users can delete drafts, and templates are an entirely different thing. So let's just call it a boilerplate message.

I would recomemnd creating a special mail database (NSF file) on the Domino server, which will just serve as a repository for your boilerplate. You can create a folder in that mail database called "Boilerplates". Using Domino Designer you can modify that folder's design so that the Subject column is the first column in the view, and it is sorted.

Once you have that done and you have created some boilerplates and saved them in the folder, you can use VBA to do a NotesSession.getDatabase call, NotesDatabase.getView call (this is used for folders as well as views), and then use NotesView.getDocumentByKey() to retrieve a specific boilerplate by the Subject you have assigned to it. Note that you do not have to copy this document to the user's mail database in order to mail it.

Answer 3

What you want to do is non-trivial, but you mention a draft email so there may be a workaround.

In your mail settings you can specify a signature file which can be an external html file on disk. So modify the signature file, then create your new mail which will then populate the body field the way you want it.

For sample code, within the memo form there should be a button to specify what signature file to use. You can use that as the baseline.

Answer 4

Rich text is not that hard to work with, but you need to look at the Domino Designer help, especially the classes NotesRichTextItem and NotesRichTextStyle. You also need to understand the DOM (Domino Object Model). Then you can create your mail content programatically.

Otherwise I think Richard's solution is the best, that you have a separate database where you get the rich text snippets, and use the AppendRTItem method of the NotesRichText class to put it into your email.

Answer 5

Thank you all guys!

But I found exactly what I wanted, without having to recreate the whole Email everytime:

Sub EditSelectedMail()
Dim NSession As Object
Dim NDatabase As Object
Dim NUIWorkspace As Object
Dim NUIdoc As Object

Set NSession = CreateObject("Notes.NotesSession")
Set NUIWorkspace = CreateObject("Notes.NotesUIWorkspace")
Set NDatabase = NSession.GetDatabase("", "")
If Not NDatabase.IsOpen Then NDatabase.OPENMAIL

Set NUIdoc = NUIWorkspace.EDITDOCUMENT(True)
With NUIdoc

    'Find the marker text in the Body item
    .GotoField ("Body")
    .FINDSTRING "**PASTE EXCEL CELLS HERE**"

    'Copy Excel cells to clipboard
    Sheets("Sheet1").Range("A1:E6").Copy

    'Create a temporary Word Document
    Set WordApp = CreateObject("Word.Application")
    WordApp.Visible = False                                 
    WordApp.Documents.Add

    'Paste into Word document and copy to clipboard
    With WordApp.Selection
        .PasteSpecial DataType:=10      
        'Enum WdPasteDataType: 10 = HTML; 2 = Text; 1 = RTF
        .WholeStory
        .Copy
    End With

    'Paste from clipboard (Word) to Lotus Notes document
    .Paste
    Application.CutCopyMode = False

    'WordApp.Quit SaveChanges:=False
    Set WordApp = Nothing

End With  
End Sub

I just select my "template", copy it into a new message, and then run the macro.