Creating a rich text email in excel, to be sent via lotus notes 7, out of dynamically created RTF

Question

I'm trying to programatically generate an email in my Lotus Notes 7 client, with the contents being a chunk of RTF code that I build specifically for each email to be sent, in excel.

I'm able to build, populate and send the email. But the body comes across as the ascii text of my RTF code, rather than the formatted version I was expecting.

I have a feeling it's because my emailer function (inherited from a previous developer) is passing it as plain text. How can I get it passed as Rich text?

The relevant emailer code: (mail box name is company specific, so has been changed) Also, please note that I want the RichText as the body, not an attachment.

Public Sub SendNotesMail(sSubject, sTo, sCC, sBCC, sAttachment, sMessage, Optional sSaveSent As Boolean = True)

'Set up the objects required for Automation into lotus notes
Dim Maildb                              'The mail database
Dim UserName As String                  'The current users notes name
Dim MailDbName As String                'THe current users notes mail database name
Dim MailDoc                             'The mail document itself
Dim RichTextitem As Object              'The RichText Item
Dim AttachME                            'The sAttachment richtextfile object
Dim Session                             'The notes session
Dim EmbedObj                            'The embedded object (sAttachment)
Dim rAttach() As String
Dim x As Integer

'Start a session to notes
Set Session = CreateObject("Notes.NotesSession")
Session.ConvertMime = False

'Get the sessions username and then calculate the mail file name
'You may or may not need this as for MailDBname with some systems you
'can pass an empty string
UserName = Session.UserName
MailDbName = "XXXXX.nsf"

'Open the mail database in notes
Set Maildb = Session.GETDATABASE("", "XXXXXX.nsf")

' Is Mail file already open?
If Maildb.IsOpen = False Then
    Maildb.OPENMAIL
End If

'Set up the new mail document
Set MailDoc = Maildb.CREATEDOCUMENT
MailDoc.form = "Memo"
MailDoc.sendto = sTo
MailDoc.CopyTo = sCC
MailDoc.BlindCopyTo = sBCC
MailDoc.Subject = sSubject
Set RichTextitem = MailDoc.Createrichtextitem("Body")
Call RichTextitem.appendtext(sMessage)
MailDoc.SaveMessageOnSend = sSaveSent

'Set up the embedded object and Attachment and attach it
If sAttachment <> "" Then
    ParseWords rAttach(), sAttachment, ";"
    For x = 0 To UBound(rAttach) Step 1
        Set AttachME = MailDoc.Createrichtextitem("Attachment")
        Set EmbedObj = AttachME.EmbedObject(1454, "", rAttach(x), "Attachment")
    Next x
End If

'Send the document
MailDoc.SaveMessageOnSend = True
MailDoc.SEND 0, sTo

'Clean Up
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj = Nothing

End Sub

Here is a chunk of my RTF text. It doesn't render anything, this is just to show the text format I mean.

{\rtf1\adeflang1025\ansi\ansicpg1252\uc1\adeff31507\deff0\stshfdbch31505\stshfloch31506\stshfhich31506\stshfbi31507\deflang1033\deflangfe1033\themelang1033\themelangfe0\themelangcs0{\fonttbl{\f0\fbidi \froman\fcharset0\fprq2{\*\panose 02020603050405020304}Times New Roman;}{\f1\fbidi \fswiss\fcharset0\fprq2{\*\panose 020b0604020202020204}Arial;}
{\f34\fbidi \froman\fcharset1\fprq2{\*\panose 02040503050406030204}Cambria Math;}        {\flomajor\f31500\fbidi \froman\fcharset0\fprq2{\*\panose 02020603050405020304}Times New Roman;}
{\fdbmajor\f31501\fbidi \froman\fcharset0\fprq2{\*\panose 02020603050405020304}Times New Roman;}{\fhimajor\f31502\fbidi \froman\fcharset0\fprq2{\*\panose 02040503050406030204}Cambria;}
{\fbimajor\f31503\fbidi \froman\fcharset0\fprq2{\*\panose 02020603050405020304}Times New Roman;}{\flominor\f31504\fbidi \froman\fcharset0\fprq2{\*\panose 02020603050405020304}Times New Roman;}
{\fdbminor\f31505\fbidi \froman\fcharset0\fprq2{\*\panose 02020603050405020304}Times New Roman;}{\fhiminor\f31506\fbidi \fswiss\fcharset0\fprq2{\*\panose 020f0502020204030204}Calibri;}
{\fbiminor\f31507\fbidi \froman\fcharset0\fprq2{\*\panose 02020603050405020304}Times New Roman;}{\f39\fbidi \froman\fcharset238\fprq2 Times New Roman CE;}{\f40\fbidi \froman\fcharset204\fprq2 Times New Roman Cyr;}
{\f42\fbidi \froman\fcharset161\fprq2 Times New Roman Greek;}{\f43\fbidi \froman\fcharset162\fprq2 Times New Roman Tur;}{\f44\fbidi \froman\fcharset177\fprq2 Times New Roman (Hebrew);}{\f45\fbidi \froman\fcharset178\fprq2 Times New Roman (Arabic);}
{\f46\fbidi \froman\fcharset186\fprq2 Times New Roman Baltic;}{\f47\fbidi \froman\fcharset163\fprq2 Times New Roman (Vietnamese);}{\f49\fbidi \fswiss\fcharset238\fprq2 Arial CE;}{\f50\fbidi \fswiss\fcharset204\fprq2 Arial Cyr;}
{\f52\fbidi \fswiss\fcharset161\fprq2 Arial Greek;}{\f53\fbidi \fswiss\fcharset162\fprq2 Arial Tur;}{\f54\fbidi \fswiss\fcharset177\fprq2 Arial (Hebrew);}{\f55\fbidi \fswiss\fcharset178\fprq2 Arial (Arabic);}

Ken Pespisa Ken Pespisa · Accepted Answer · 2011-08-26T01:47:46

Your RTF formatted text won't work with Lotus Notes's Rich Text format. They are separate things.

You can create Rich Text using the Notes API. See the methods that start with NotesRichTextXXXX. It's not simple, mind you, and there's no easy way to convert the RTF to Notes Rich Text unfortunately.

There is an add-on that will help you deal with Rich Text. I haven't tried it but it has always been highly recommended: http://www.geniisoft.com/showcase.nsf/MidasLSX

Using the MIME classes you can send an HTML email via Notes, and that may be your best bet.

Creating a rich text email in excel, to be sent via lotus notes 7, out of dynamically created RTF

2 Answers