Send a Range as rich text in body through lotus notes

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
 
Dear John,
Thanks a lot,
As of now I got one more assignment in which if we input the BIC SWIFT CODE then automatically the bank details will be fed in the respective cells from the website, so for this I am trying to figure it out and working on this code. Not getting the results as desired. If possible please help me.
This is a completely different request so please start a new thread.
 
Upvote 0

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
With this and your previous post I think I understand what you're asking for now.

Try this:

VBA Code:
    Dim lastCellRowNumber As Long
    With Worksheets("SUMMARY")
        'Last row in columns C:D, ignoring blanks and blank results of formulas
        Set embedCells = .Columns("C:D")
        lastCellRowNumber = .Evaluate("max(if(" & embedCells.Address & "<>"""",row(" & embedCells.Address & ")))")
        Set embedCells = .Range("C1:D" & lastCellRowNumber)
    End With
Insert the cells into the Notes email body like this (same as previously):
Code:
        embedCells.CopyPicture , xlBitmap
        .Paste
        Application.CutCopyMode = False
        DoEvents
PS use CODE tags when posting VBA code - the </> icon in the message editor.
Sorry, John codes were again pasting whole cells....Its not working.
 
Upvote 0
a
Sorry, John codes were again pasting whole cells....Its not working.
Actually I have used below formula in all cells.

{=IFERROR(INDEX(Filtered!$B$2:$B$5000, SMALL(IF($A$2=Filtered!$C$2:$C$1000, ROW($C$2:$C$1000)-ROW(Filtered!$C$2)+1), ROW(19:19))), " ")}[/CODE]
 
Upvote 0
Try changing the " " in your formula to "".
Dear John as per your code and as per my requirement it is working fine with this below code.
VBA Code:
 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, BODYeX As String
    Dim attachmentFile As String
    Dim embedCells As Range
    Dim lastCellRowNumber As Long
    
    
    '------------ User-defined settings section ------------
    
    
    SendTo = Sheets("SUMMARY").Range("E2")
    
    CopyTo = Sheets("SUMMARY").Range("F2")
    
    Subject = Sheets("SUMMARY").Range("B7")
    BODYeX = Sheets("SUMMARY").Range("B13")
    
          
    'The cells to be embedded in the email body
    
    With Worksheets("SUMMARY")
        'Last row in columns C:D, ignoring blanks and blank results of formulas
        Set embedCells = .Columns("C:D")
        lastCellRowNumber = .Evaluate("max(if(" & embedCells.Address & "<>"""",row(" & embedCells.Address & ")))")
        Set embedCells = .Range("C1:D" & lastCellRowNumber)
    End With

    'Optional file attachment - full folder path and file name, or "" for no attachment
    attachmentFile = " "
    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 " ", BODYeX
        
        Sheets("SUMMARY").Range("C1:D" & lastCellRowNumber).Copy       'CHANGE THIS
        .Paste
        Application.CutCopyMode = False
        
      
    
    End With
 
    
    Set NUIDocument = Nothing
    Set NWorkspace = Nothing
    Set NMailDb = Nothing
    Set NSession = Nothing

End Sub

But the thing is that every time I have to change the font for every pasted values and also the entire message also. Is it possible to set any default paste option as my desired font in lotus mail or in entire code. Actually as per requirement we don't want to paste the table as image.
 
Upvote 0
Dear John, Thank you once again very much. As per your given code and as per my work requirement it is working fine with this below code. You have provided the code to paste the data in the image format, but as per our current requirement we want it to be in text without any change in format.
VBA Code:
 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, BODYeX As String
    Dim attachmentFile As String
    Dim embedCells As Range
    Dim lastCellRowNumber As Long
   
   
    '------------ User-defined settings section ------------
   
   
    SendTo = Sheets("SUMMARY").Range("E2")
   
    CopyTo = Sheets("SUMMARY").Range("F2")
   
    Subject = Sheets("SUMMARY").Range("B7")
    BODYeX = Sheets("SUMMARY").Range("B13")
   
         
    'The cells to be embedded in the email body
   
    With Worksheets("SUMMARY")
        'Last row in columns C:D, ignoring blanks and blank results of formulas
        Set embedCells = .Columns("C:D")
        lastCellRowNumber = .Evaluate("max(if(" & embedCells.Address & "<>"""",row(" & embedCells.Address & ")))")
        Set embedCells = .Range("C1:D" & lastCellRowNumber)
    End With

    'Optional file attachment - full folder path and file name, or "" for no attachment
    attachmentFile = " "
    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 " ", BODYeX
       
        Sheets("SUMMARY").Range("C1:D" & lastCellRowNumber).Copy       'CHANGE THIS
        .Paste
        Application.CutCopyMode = False
       
     
   
    End With

   
    Set NUIDocument = Nothing
    Set NWorkspace = Nothing
    Set NMailDb = Nothing
    Set NSession = Nothing

End Sub

But the thing is that every time I have to change the font for every pasted values and also the entire message also. Is it possible to set any default paste option as my desired font in lotus mail or in entire code. Actually as per requirement we don't want to paste the table or as image.
Actually as per our current requirement we don't want to paste any data or table as image format.
 
Upvote 0
But the thing is that every time I have to change the font for every pasted values and also the entire message also. Is it possible to set any default paste option as my desired font in lotus mail or in entire code. Actually as per requirement we don't want to paste the table as image.
In that case, try adapting the code at:


which shows how to use AppendText and AppendStyle with different fonts. As you now don't want to paste the table as an image you would have to construct the AppendText string from your column values, with appropriate spacing (e.g. using the VBA Format function). I think AppendText uses the default Notes font if you don't define a font with AppendStyle.

As one poster in that thread found, the Lotus Notes window must be maximised in order for the Send_Notes_Email routine to work. Handle this with the code at:


Alternatively, create a HTML MIME email, including a HTML table containing the table values and inline styles for the fonts. A Lotus Notes MIME email is a completely different approach and not covered by the code above; there might be code on this forum to create and send such an email if you search.
 
Upvote 0
Once again thank you John. it really worked. Its highly appreciated........................thank you thank you very much.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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