VBA to Lotus Notes - Variable body with formatting ( Colors )

Question

I'm currently working in the automation of a process at work that used to require a lot of hand work and gathering data from several sources and ended in sending an email with:

• Header ( fixed ) Regular

• Description ( One line for each cell with data in a given range ) Bold

• Footer ( fixed ) - Text Color: Red

• Attachment

Well, we had a stationery to aid with the email, but as i can't guarantee that everybody will have the stationery properly set up i am looking for a more elegant way to do so ( basically the goal is to make it fool-proof ), so i started to work on a way to do it mixing VBA+Formulas in the cells.

So far my code creates the message on notes, inserts the adress list, title and attaches the file that it generates, but when it comes to inserting the body, fat chance! I can insert a single-lined message but without any formatting or styles, the ones described above in bold next to the elements of the body.

• What i'm chasing is a way to paste the text in given cells from my spreadsheet to notes and apply formatting on them, so each cell value would be a line of text on notes, with different styling.

I've been reading questions and articles for about 3 days already without any success, and i decided to ask it myself cause it's a big step forward in my project, is there a way to do it? i believe i'm looking for something like

notesmagicproperty.boldthisrange("B3")

that translates to

"03 - Lorem ipsum dolor sit amet"

Thanks in advance, Stack Overflow has saved me a thousand times already!

Also, sorry for not posting the code, i'm writing this from home and it's 3am so i have no access to it at the moment.

Answer 1

0. NotesRichTextRange.SetStyle method

NotesRichTextRange.SetStyle method is what you are looking for. For this method you need to create NotesRichTextStyle object. Also you need to SetBegin end SetEnd of range by using NotesRichTextNavigator object.
Here is example:

Dim ses As New NotesSession 
Dim doc As NotesDocument
Dim richText As NotesRichTextItem
Dim navigator As NotesRichTextNavigator
Dim range As NotesRichTextRange
Dim headerStyle As NotesRichTextStyle
Dim descriptionStyle As NotesRichTextStyle
Dim footerStyle As NotesRichTextStyle

'Create your doc.

'Generate rich text content:    
Set richText = doc.CreateRichTextItem("Body")
Set navigator = richText.CreateNavigator
Set range = richText.CreateRange

richText.AppendText("Header")
richText.AddNewline(1)

Set headerStyle = ses.CreateRichTextStyle
headerStyle.Underline = True

Set descriptionStyle = ses.CreateRichTextStyle
descriptionStyle.Bold = True

Set footerStyle = ses.CreateRichTextStyle
footerStyle.NotesColor = COLOR_RED

navigator.FindFirstElement(RTELEM_TYPE_TEXTPARAGRAPH)

range.SetBegin(navigator)
range.SetEnd(navigator)

Call range.SetStyle(headerStyle)

For index% = 0 To 7
    richText.AppendText("Description" & index%)
    richText.AddNewline(1)

    navigator.FindNextElement(RTELEM_TYPE_TEXTPARAGRAPH)

    range.SetBegin(navigator)
    range.SetEnd(navigator)

    Call range.SetStyle(descriptionStyle)
Next

richText.AppendText("Footer")
richText.AddNewline(1)

navigator.FindNextElement(RTELEM_TYPE_TEXTPARAGRAPH)

range.SetBegin(navigator)
range.SetEnd(navigator)

Call range.SetStyle(footerStyle)

Call richText.EmbedObject(EMBED_ATTACHMENT, "", "SomeFile")

richText.Update

'Process your doc.

This example generates this rich text:

1. NotesDocument.RenderToRTItem method

The other way is to use NotesDocument.RenderToRTItem method. For this method you need to create a form and style it as you need. For example, create a form "Message" and add to this form four fields:

And use this form in your code:

Dim ses As New NotesSession
Dim db As NotesDatabase
Dim messageDoc As NotesDocument
Dim attachment As NotesRichTextItem
Dim description(7) As String
Dim doc As NotesDocument
Dim richText As NotesRichTextItem

Set db = ses.CurrentDatabase
Set messageDoc = db.CreateDocument
messageDoc.Form = "Message"
messageDoc.Header = "Header"

For index% = 0 To Ubound(description)
    description(index%) = "Description" & index%
Next

messageDoc.Description = description
messageDoc.Footer = "Footer"

Set attachment = messageDoc.CreateRichTextItem("Attachment")
Call attachment.EmbedObject(EMBED_ATTACHMENT, "", "SomeFile")

'Create your doc.

'Generate rich text content:    
Set richText = doc.CreateRichTextItem("Body")
Call messageDoc.RenderToRTItem(richText)
richText.Update

'Process your doc.

This example generates this rich text:

2. NotesUIDocument.Import method

You can genereate the rich text content somewhere else and import it to your document by using NotesUIDocument.Import method.
Here is example for importing html content:

Dim ses As New NotesSession
Dim db As NotesDatabase
Dim doc As NotesDocument
Dim richText As NotesRichTextItem
Dim ws As New NotesUIWorkspace
Dim uidoc As NotesUIDocument

'Generate html file
tempdir$ = Environ("Temp")

file = Freefile
filename$ = tempdir$ & "\temp.html"
Open filename$ For Output As file

Print #file, "<u>Header</u><br>"

For index% = 0 To 7
    Print #file, "<b>Description" & index% & "</b><br>"
Next

Print #file, "<font color='red'>Footer</font><br><br>"

Close file

Set db = ses.CurrentDatabase
Set doc = db.CreateDocument

'Create your doc.

'Add attachment to rich text:
Set richText = doc.CreateRichTextItem("Body")
Call richText.EmbedObject(EMBED_ATTACHMENT, "", "SomeFile")

Set uidoc = ws.EditDocument(True, doc)

uidoc.GotoField("Body")
uidoc.Import "html", filename$

'Process your doc.

This example generates this rich text:

Answer 2

Please note that this code IS NOT MINE I took it from user John_W in a mr excel post, I'm just pasting it here because I wanted to share something that helped me as it might help others. Also, I won't link the page here because I don't think it's fair with Stack Overflow but I have a big Thank You for John_W for sharing this online.

Sub Notes_Email_Excel_Cells()

    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("", "")

    If Not NDatabase.IsOpen Then
        NDatabase.OPENMAIL
    End If

    'Create a new document

    Set NDoc = NDatabase.CreateDocument

    With NDoc
        .SendTo = "email.address@email.com"       'CHANGE THIS
        .CopyTo = ""
        .subject = "Pasted Excel cells " & Now

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

        .body = "Text in email body" & vbNewLine & vbNewLine & _
            "**PASTE EXCEL CELLS HERE**" & vbNewLine & vbNewLine & _
            "Excel cells are shown above"

        .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 "**PASTE EXCEL CELLS HERE**"
        '.DESELECTALL            'Uncomment to leave the marker text in place (cells are inserted immediately before)

        'Replace it with the Excel cells

        Sheets("Sheet1").Range("A1:E6").Copy       'CHANGE THIS
        .Paste
        Application.CutCopyMode = False

        .Send
        .Close
    End With

    Set NSession = Nothing

End Sub