Attaching formatted cells to lotus notes email

Question

Good day all, first I want to apologize for raising a question for something that has obviously been discussed many times, I am just having real trouble understanding the responses and I am desperate for someone to help me out.

I have the below code written, it has worked for me for all of my previous needs of sending simple emails, but now that I want to have the email body with the range of cells added formatted (color and borders) I am not able to get it to work at all.

I did my best to study the answers that were there before, and play with the codes to adjust it to my needs, but I am truly lost.

The code below is what I use, can someone please help me adjust it so that the range I am relating to will appear in the email body with it's formats?

Thank you very much in advance, I really pray for some good help!

Sub SendEmail()



Dim TodayDate As Date
Dim x As Integer, A As Integer
Dim UserName As String
Dim MailDbName As String, msgboxtitle As String
Dim Recipient As Variant
Dim Maildb As Object
Dim MailDoc As Object
Dim AttachME As Object
Dim Session As Object
Dim stSignature As String
Dim Sent As String, EmailTo As String
Dim RecipientEmail As String, Subject As String
Dim rng As range



RecipientEmail = Worksheets("Email").range("B1").Value

EmailTo = Worksheets("Email").range("C3").Value

Subject = Worksheets("Email").range("B2").Value


Set rng = Worksheets("Email").range("B3:C10")



    ' Open and locate current LOTUS NOTES User
    
        

        
            Set Session = CreateObject("Notes.NotesSession")
            UserName = Session.UserName
            MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
            Set Maildb = Session.GetDatabase("", MailDbName)
            
            
            If Maildb.IsOpen = True Then
            Else
                Maildb.OPENMAIL
                
            End If
    
            ' Create New Mail and Address Title Handlers
            Set MailDoc = Maildb.CREATEDOCUMENT
            MailDoc.Form = "Memo"
            
            ' Select range of e-mail addresses

            MailDoc.SendTo = RecipientEmail
            MailDoc.Subject = Subject
            
            MailDoc.body = rng
            
            
            
            
            MailDoc.SaveMessageOnSend = True
            MailDoc.PostedDate = Now()
            On Error GoTo errorhandler1
            MailDoc.Send 0, RecipientEmail
            Set Maildb = Nothing
            Set MailDoc = Nothing
            Set Session = Nothing


            
  
            
errorhandler1:
            Set Maildb = Nothing
            Set MailDoc = Nothing
            Set Session = Nothing



End Sub

user_1243456 · Answer

As Richard have rightly mentioned, you are pulling the formatted cell ranges from excel and trying to put it in richtext. It won't work as is.

Either you have to (1)use third part libraries or you have (2) manually pull data from each cell and create NotesRichTextTable item to add values or (3) convert your email doc to mime parts(html), attach the excel formatted content and send the mime doc as email.

user_622571 · Answer

There is no easy way to do this.

You are going to have to pull the DisplayFormat information out of the Excel cells and use it to manually create a formatted table in the message. And unless you are using a third-party API to manipulate Notes rich text, you are going to have to build your email as a MIME/HTML message instead of an ordinary Notes rich text message. Below is an illustration of the basic coding that you will need to start creating a MIME message with HTML content.

Session.convertMIME = False 
Dim body As NotesMIMEEntity
Dim hdr As NotesMIMEHeader
Dim entity As NotesMIMEEntity
Set body = mailDoc.CreateMIMEEntity
Set hdr = body.CreateHeader({MIME-Version})
Call hdr.SetHeaderVal("1.0")
Set hdr = body.CreateHeader("Content-Type")
Call hdr.SetHeaderValAndParams({text/html"})
Call body.SetContentFromText( { The HTML That You Built From Excel Data Goes Here } )

Attaching formatted cells to lotus notes email

2 Answers