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
 
Sort by date Sort by votes
Rahulkr said:
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.
Click to expand...
This is a completely different request so please start a new thread.
 
Upvote 0

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
John_w said:
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.
Click to expand...
Sorry, John codes were again pasting whole cells....Its not working.
 
Upvote 0
a
Rahulkr said:
Sorry, John codes were again pasting whole cells....Its not working.
Click to expand...
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
John_w said:
Try changing the " " in your formula to "".
Click to expand...
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
Rahulkr said:
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.
Click to expand...
Actually as per our current requirement we don't want to paste any data or table as image format.
 
Upvote 0
Rahulkr said:
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.
Click to expand...
In that case, try adapting the code at:

www.mrexcel.com

VBA Code: Lotus Notes Email Preparation From Excel Worksheet

Hi All, I managed to get a code that will automate my email preparation. The code gets recipients, subject and the body of the mail from the active worksheet and it works perfectly for the recipients and subject, not so much for the body. Say the mail body is c1, c2, c3, c4, c5, c6, c7 and...
www.mrexcel.com www.mrexcel.com

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:

www.mrexcel.com

VBA Code: Lotus Notes Email Preparation From Excel Worksheet

Hi All, I managed to get a code that will automate my email preparation. The code gets recipients, subject and the body of the mail from the active worksheet and it works perfectly for the recipients and subject, not so much for the body. Say the mail body is c1, c2, c3, c4, c5, c6, c7 and...
www.mrexcel.com www.mrexcel.com

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
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
570
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
912
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,907
Messages
6,175,300
Members
452,633
Latest member
DougMo

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