Send a Range as rich text in body through lotus notes

[Sunny k]

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=192 border=0><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #d99795" width=64 height=17>Name</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #d99795" width=64>Id</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #d99795" width=64>City</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>a</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">111</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">WA</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>b</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">134</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">DC</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>c</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">145</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">VA</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>d</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">156</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">CA</TD></TR></TBODY></TABLE>

hi all

need your help . i need a code which can send a range in the body of mail in lotus notes with formatting like if i select a range given above it should be copied as paste sepcial as richtext in the body of email. so that the user can copy the text .

thanks

 

[ChIpStIcK]

Is there a way of specifying which inbox the mail is sent from? I have a situation where each user has their own box but I'd like to have the mail sent from a shared inbox.

 

[deadlyjoker]

Hi, i got a little different condition, my database mail are stored in our company's server, so when i tried to run the macro i got an error messages "Class are not registered on local machine" and when i tried to debug it highlight the "Set NUIWorkSpace = CreateObject("Notes.NotesUIWorkspace")", could anyone help me with this problem coz it would do me greatly on my work..Thx!!

I found the answer to the problem with pasting picture instead of text. Here is te code, basically what it does, it takes the range of cell into word and copies it to notes
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
Dim WordApp As Object
Dim WordDoc 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 = "ehmolina@lenovo.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("c8:c56").Select 'CHANGE THIS

Set WordApp = CreateObject("Word.Application")
Set WordDoc = WordApp.Documents.Add
Selection.Copy
With WordApp.Selection
.PasteSpecial DataType:=2
.WholeStory
.Copy
End With



.Paste
Application.CutCopyMode = False







.Send
.Close
End With

Set NSession = Nothing

End Sub

 

[rallcorn]

BRILLIANT!!!!

I've searched days for a solution to sending emails via EXCEL VBA using Lotus Note UI and retaining Excel cell formatting (bold, underline, font color & font size).

I could make it happen using Lotus Notes backend by creating richTextItem and richTextStyle . . . but with backend, certain webmail users don't receive the email body or attachments. Using Lotus Notes UI the webmail recipients get what they need, but the richText items seem to be virtually impossible (with my limited knowledge!)

Many thanks for the solution found in this thread!

 

[John_w]

BRILLIANT!!!!

I've searched days for a solution to sending emails via EXCEL VBA using Lotus Note UI and retaining Excel cell formatting (bold, underline, font color & font size).

I'm glad you found the code useful. The code in post #9 of this thread (http://www.mrexcel.com/forum/excel-...ext-body-through-lotus-notes.html#post2762262) uses a temporary Word document as an intermediate object to copy and paste the Excel cells into the Notes email as a table. This means the table contents are editable in the recipient's and sender's email. It also retains most of the Excel cell formatting; the only cell format not retained seems to be the cell border.

If you don't need the ability to edit the table of cells in the sent/received email, the following code copies and pastes the Excel cells directly into the email as a bitmap image, preserving all cell formatting (bold, underline, italic, fonts, text colour, background colour, borders, etc.). All the user-defined settings are in a section at the top of the code, for ease of customisation.

Public Sub Notes_Email_Excel_Cells3()

    Dim NSession As Object
    Dim NUIWorkSpace As Object
    Dim NDatabase As Object
    Dim NDoc As Object
    Dim NUIdoc As Object
    Dim Subject As String
    Dim SendTo As String, CopyTo As String
    Dim embedCells As Range
    
    '------------ User-defined settings section ------------
    SendTo = "email1@email.com"
    CopyTo = "email2@email.com,email3@email.com"
    
    Subject = "Email subject"
     
    'The cells to be embedded in the email
    Set embedCells = Sheets("Sheet1").Range("A1:C8")
    '------------ End of user-defined settings ------------
    
    Set NSession = CreateObject("Notes.NotesSession")
    Set NUIWorkSpace = CreateObject("Notes.NotesUIWorkspace")
    Set NDatabase = NSession.GetDatabase("", "")
    If Not NDatabase.IsOpen Then NDatabase.OPENMAIL
    
    'Create a new Lotus Notes document
    
    Set NDoc = NDatabase.CreateDocument
    
    With NDoc
        .SendTo = SendTo
        .CopyTo = CopyTo
        .Subject = Subject
        
        'Email body text, including a placeholder which will be replaced by the Excel cells
        
        .body = "Text in email body" & vbLf & vbLf & _
            "{IMAGE_PLACEHOLDER}" & vbLf & vbLf & _
            "Excel cells are shown above"
        
        .Save True, False
    End With
    
    'Edit the new document using Notes UI to copy and paste the Excel cells into it
    
    Set NUIdoc = NUIWorkSpace.EDITDocument(True, NDoc)
    With NUIdoc
    
        'Find the placeholder in the Body item
        
        .GotoField "Body"
        .FINDSTRING "{IMAGE_PLACEHOLDER}"
        '.DESELECTALL 'Uncomment to leave the placeholder in place (cells are inserted immediately before it)
        
        'Copy Excel cells as a bitmap to the clipboard and paste into the email
        
        embedCells.CopyPicture xlBitmap
        .Paste
        Application.CutCopyMode = False
       
        .Send
        .Close
    End With
    
    Set NSession = Nothing

End Sub

Both procedures in this thread send plain text emails. If you want to send a Notes email which includes rich text styles (bold, italic, underline, font type, colour and size) in the email body, an embedded image, automatic signature (if defined in User Preferences) and a file attachment use the code in http://www.mrexcel.com/forum/excel-...-preparation-excel-worksheet.html#post3391183.

 

[rallcorn]

John_w, thanks so much. I don't need the table edit capability so I'll check out your suggestions tomorrow. The approach of copying, opening Word, pasting, copying, closing Word -- slows things down quite a bit because I have to copy 6 different pieces that need to retain bold, underline, color, font size. I'm sure one of your offered solutions will speed it back up.

Really appreciate your suggestions!

 

[Bigtaff]

Hi there.

The above works really well for me, thank you for sharing it.

Is there any way to include the senders email signature from Lotus Notes as well?

Thanks, Lee

 

[John_w]

Is there any way to include the senders email signature from Lotus Notes as well?

This code includes the automatic email signature and an optional attachment.

Public Sub Send_Lotus_Email3()

    Const EMBED_ATTACHMENT = 1454
    
    Dim NSession As Object
    Dim NWorkspace As Object
    Dim NMailDb As Object
    Dim NUIDocument As Object
    Dim NRTattachment As Object
    Dim Subject As String
    Dim SendTo As String, CopyTo As String
    Dim attachmentFile As String
    Dim embedCells As Range
    
    '------------ User-defined settings section ------------
    
    SendTo = "email1@address.com,email2@address.com"
    CopyTo = "email3@address.com"
    
    Subject = "Email subject"
    
    'The cells to be embedded in the email body
    Set embedCells = Sheets("Sheet1").Range("A1:E6")
    
    'Optional file attachment - full folder path and file name, or "" for no attachment
    attachmentFile = "C:\path\to\file.xls"
    attachmentFile = ""
    
    '------------ End of user-defined settings ------------
    
    Set NSession = CreateObject("Notes.NotesSession")   'OLE, late binding only
    Set NWorkspace = CreateObject("Notes.NotesUIWorkspace")
    
    Set NMailDb = NSession.GetDatabase("", "")
    NMailDb.OpenMail
        
    NWorkspace.ComposeDocument , , "Memo"
    
    Set NUIDocument = NWorkspace.CurrentDocument
        
    With NUIDocument
        .FieldSetText "EnterSendTo", SendTo
        .FieldSetText "EnterCopyTo", CopyTo
        .FieldSetText "EnterBlindCopyTo", ""
        .FieldSetText "Subject", Subject
        .GotoField "Body"
        
        '------------ Start of email body text ------------
        
        .InsertText "Start of email body text."
        
        'Copy and paste Excel cells as a bitmap image into the email body
        
        .InsertText vbLf & vbLf & "Excel cells as a bitmap image:" & vbLf & vbLf
        embedCells.CopyPicture , xlBitmap
        .Paste
        Application.CutCopyMode = False
        
        .InsertText vbLf & vbLf & "End of email body text." & vbLf & vbLf
    
        '------------ End of email body text ------------
    
        'Optional file attachment
        
        If attachmentFile <> "" Then
            Set NRTattachment = .Document.CreateRichTextItem("Attachment")
            NRTattachment.EmbedObject EMBED_ATTACHMENT, "", attachmentFile
        End If
        
        .Save
        .Close
    End With
    
    'Send S key to click the 'Send and Save' button to send mail document
    
    Application.Wait DateAdd("s", 2, Now)
    AppActivate "Send Mail", True
    SendKeys "S"
    
    Set NUIDocument = Nothing
    Set NWorkspace = Nothing
    Set NMailDb = Nothing
    Set NSession = Nothing

End Sub

 

[sankarsr]

Thank you John and all for very useful information. I have following requirements, it would be great if you can provide help and solution.

1) How to send emails faster - without opening a new mail in a visible way, directly invoking API class etc.,. My content is static content. No need to copy and paste from excel. I believe that way it would be faster. Present script is erroring out sometimes and says "Unable to find search string". Seems like timing issue only.

2) My mails are not getting saved in SENT folder. I have used exact EXCEL COPY PASTE code. Initially it saved for 50 mails, then it stopped.

3) Mail is waiting for my confirmation when it was unable to resolve email id. It is showing options like "Skip", "Skip ALL", "Send Anyway". I want it to send without checking and let me mail bounce later. This way we can send mails continuously without our intervention.

 

[John_w]

1) How to send emails faster - without opening a new mail in a visible way, directly invoking API class etc.,. My content is static content. No need to copy and paste from excel. I believe that way it would be faster. Present script is erroring out sometimes and says "Unable to find search string". Seems like timing issue only.

2) My mails are not getting saved in SENT folder. I have used exact EXCEL COPY PASTE code. Initially it saved for 50 mails, then it stopped.

3) Mail is waiting for my confirmation when it was unable to resolve email id. It is showing options like "Skip", "Skip ALL", "Send Anyway". I want it to send without checking and let me mail bounce later. This way we can send mails continuously without our intervention.

Try the code at http://www.mrexcel.com/forum/excel-...erent-files-them-lotus-notes.html#post3358964 to get started.

 

[sankarsr]

Try the code at http://www.mrexcel.com/forum/excel-...erent-files-them-lotus-notes.html#post3358964 to get started.

Hi John,

It is giving the error at ".send False". Can you please help ?

Error:

Run-time error '-2147417851 (80010105)';
Automation error
The server threw an exception