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

 

[John_w]

Is this an existing email, created in the Notes GUI, in which you want the Excel cells to be pasted? Or is it a new email which will be created by VBA, at the same time as pasting the Excel cells in it?

 

[Sunny k]

Hi john

wiah you a happy new year

thanks for the reply. its a new mail . data should be copied from the sheet and pasted as richtext and send it to user. also the no of records will vary in the range . if you can help out me with this sample i will make changes in the range manually .

 

[John_w]

Try this. The Lotus Notes client GUI must be open and the Mail tab must be open and be the active tab. This example pastes the Excel cells in A1:E6 on Sheet1; change these to suit your requirements. You will also need to change the SendTo email address.

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

 

[Sunny k]

hi john

thanks for the quick response . but its pasting range as image in body of email. i want it to be pasted as rich text.

right now i am doing it manually lfirst copy the range goto edit then choose paste special as rich text.

i want data to be pasted as rich text

 

[Sunny k]

any help ....

 

[John_w]

I can't find anything in the Lotus Notes API for paste special, so probably the only way to paste the Excel cells as rich text is to use the VBA SendKeys function (I guarantee this won't work reliably, if at all) or send key presses with the Windows API or a keyboard/mouse emulator like AutoIt to automate the manual key presses or mouse clicks.

In fact, I found this thread - http://webcache.googleusercontent.c.../Lotus_SmartSuite/Lotus_Notes/Q_21829026.html - which claims to almost work for AutoIt. Search the page for 'AutoIt3 script' to find the code.

 

[Hmolina]

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

 

[John_w]

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

This is an excellent technique and a useful discovery, so thanks for posting. Your code pastes the Excel cells as plain text (DataType:=2); here is an improved version which pastes as HTML. Pasting as HTML preserves most of the cell formats - font, colour, bold, underline, etc. The only cell format not preserved in the Notes email appears to be the cell border.

Sub Notes_Email_Excel_Cells2()

    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 subject As String
    
    subject = "Pasted Excel cells using Word PasteSpecial method " & Now
    Debug.Print subject
    
    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 = "email.address@email.com"              'CHANGE RECIPIENT EMAIL ADDRESS
        .CopyTo = ""
        .subject = subject
        
        'Email body text, including marker text which will be replaced by the Excel cells
        
        .body = "Text in email body" & vbLf & vbLf & _
            "**PASTE EXCEL CELLS HERE**" & vbLf & vbLf & _
            "Excel cells are shown above"
        
        .Save True, False
    End With
    
    'Edit the just-created document to copy and paste the Excel cells into it via Word
    
    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)
        
        'Copy Excel cells to clipboard
        
        Sheets("Sheet1").Range("A1:E6").Copy                  'CHANGE SHEET AND RANGE TO BE COPIED AND PASTED
    
        'Create a temporary Word Document
        
        Set WordApp = CreateObject("Word.Application")
        WordApp.Visible = False                                 'True to aid debugging
        WordApp.Documents.Add
        
        'Paste into Word document and copy to clipboard
            
        With WordApp.Selection
            .PasteSpecial DataType:=10      'Enum WdPasteDataType: 10 = HTML; 2 = Text; 1 = RTF
            .WholeStory
            .Copy
        End With

        'Paste from clipboard (Word) to Lotus Notes document
        
        .Paste
        Application.CutCopyMode = False
   
        WordApp.Quit SaveChanges:=False
        Set WordApp = Nothing
   
        .Send
        .Close
    End With
    
    Set NSession = Nothing

End Sub

 

[Hmolina]

You are welcome, and the html text helps a lot, thank you again