Send a Range as rich text in body through lotus notes

S

Sunny k

New Member
Joined
Jan 3, 2011
Messages
6
<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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
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?
 
Upvote 0
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 .
 
Upvote 0
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.
Code: 
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Hmolina said:
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
Click to expand...
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.
Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
576
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
913
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Forum statistics

Threads
1,223,994
Messages
6,175,849
Members
452,675
Latest member
duongtruc1610

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top