Ron de Bruin
Excel Automation

Lotus Notes: Send data from a range as part of an e-mail's body

Important message to visitors of this page

Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.

Luckily, Ron was kind enough to allow me to publish all of his Excel content here.

Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.

Kind regards

Jan Karel Pieterse

The code examples on this page are created by Excel MVP Dennis Wallentin but are not on his site anymore. But Dennis allow me to publish it on my site for all the Excel/Lotus Notes users in the world.

The information in this article applies to:
Microsoft Excel 2000 and later.
Lotus Notes 4.x and later.

External reference:

Microsoft Forms 2.0 Object Library

Task:
Let users select a range of data in a worksheet to be inserted into the body of an outgoing e-mail.

VBA code :

Sub Send_Unformatted_Rangedata()
   Dim noSession As Object, noDatabase As Object, noDocument As Object
   Dim vaRecipient As Variant
   Dim rnBody As Range
   Dim Data As DataObject
 
   Const stSubject As String = "Send only an unformatted range of data."
   Const stMsg As String = "Data as part of the e-mail's body."
   Const stPrompt As String = "Please select the range:"
 
   'This is one technique to send an e-mail to many recipients but for larger
   'number of recipients it's more convenient to read the recipient-list from
   'a range in the workbook.
   vaRecipient = VBA.Array("first@xldennis.com", "second@xldennis.com")
 
   On Error Resume Next
   Set rnBody = Application.InputBox(Prompt:=stPrompt, _
         Default:=Selection.Address, Type:=8)
 
   'The user canceled the operation.
   If rnBody Is Nothing Then Exit Sub
 
   On Error GoTo 0
 
   'Instantiate Lotus Notes COM's objects.
   Set noSession = CreateObject("Notes.NotesSession")
   Set noDatabase = noSession.GETDATABASE("", "")
 
   'Make sure Lotus Notes is open and available.
   If noDatabase.IsOpen = False Then noDatabase.OPENMAIL
 
   'Create the document for the e-mail.
   Set noDocument = noDatabase.CreateDocument
 
   'Copy the selected range into memory.
   rnBody.Copy
 
   'Retrieve the data from then copied range.
   Set Data = New DataObject
   Data.GetFromClipboard
 
   'Add data to the mainproperties of the e-mail's document.
   With noDocument
      .Form = "Memo"
      .SendTo = vaRecipient
      .Subject = stSubject
      'Retrieve the data from the clipboard.
      .Body = Data.GetText & " " & stMsg
      .SaveMessageOnSend = True
   End With
 
   'Send the e-mail.
   With noDocument
      .PostedDate = Now()
      .Send 0, vaRecipient
   End With
 
   'Release objects from memory.
   Set noDocument = Nothing
   Set noDatabase = Nothing
   Set noSession = Nothing
 
 
   'Activate Excel for the user.
   AppActivate "Microsoft Excel"
 
   'Empty the clipboard.
   Application.CutCopyMode = False
 
   MsgBox "The e-mail has successfully been created and distributed.", vbInformation
 
End Sub

Comments:
Working with Lotus Notes in Excel and as well in other Office-programs is not an easy task. Using early binding is a problematic area and therefore late binding is used in the above example.

Copyright © 2022 Ron de Bruin

Site designed by Spider Web Woman Designs