CODEWITHSUNDEEP
excelvbaemaillotus-notesrichtext
Nissan
Nissan

Reputation: 11

Attaching formatted cells to lotus notes email

I have written the below code. It has worked for sending simple emails.

Now I want the email body with the range of cells added formatted (color and borders).

I did my best to study the answers that were there before, and adjust the code.

I want that the range will appear in the email body with it's formats?

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

Upvotes: 1

Views: 143

Answers (2)

Richard Schwartz
Richard Schwartz

Reputation: 14628

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 } )

Upvotes: 0

flykarthick
flykarthick

Reputation: 79

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.

Upvotes: 1

Related Questions