Ron de Bruin
Excel Automation

Lotus Notes: Send formatted data from a range or chart 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.

Task:
Insert a pre-defined formatted range of data to be inserted as part of the bode in an outgoing e-mail.

VBA code :

Option Explicit
 
'Function for finding the first top level window in the windows list
'that meet the criteria.
Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
      (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
 
 
Sub Send_Formatted_Range_Data()
   Dim oWorkSpace As Object, oUIDoc As Object
   Dim rnBody As Range
   Dim lnRetVal As Long
 
   Const stTo As String = "Excel@Microsoft.com"
   Const stCC As String = "Lotus Notes@IBM.com"
   Const stBody As String = vbCrLf & "As per agreement." & vbCrLf _
         & "Kind regards" & vbCrLf & "Dennis"
   Const stSubject As String = "Report xxx"
   Const stMsg As String = "An e-mail has been succesfully created and saved."
 
   'Check if Lotus Notes is open or not.
   lnRetVal = FindWindow("NOTES", vbNullString)
 
   If lnRetVal = 0 Then
      MsgBox "Please make sure that Lotus Notes is open!", vbExclamation
      Exit Sub
   End If
 
   Application.ScreenUpdating = False
 
   'A named range in the activesheet is in use.
   Set rnBody = ActiveSheet.Range("Report")
   rnBody.Copy
 
   'Instantiate the Lotus Notes COM's objects.
   Set oWorkSpace = CreateObject("Notes.NotesUIWorkspace")
 
   On Error Resume Next
   Set oUIDoc = oWorkSpace.ComposeDocument("", "mail\xldennis.nsf", "Memo")
   On Error GoTo 0
 
   Set oUIDoc = oWorkSpace.CurrentDocument
 
   'Using LotusScript to create the e-mail.
   Call oUIDoc.FieldSetText("EnterSendTo", stTo)
   Call oUIDoc.FieldSetText("EnterCopyTo", stCC)
   Call oUIDoc.FieldSetText("Subject", stSubject)

   'If You experience any issues with the above three lines then replace it with:
   'Call oUIDoc.FieldAppendText("EnterSendTo", stTo)
   'Call oUIDoc.FieldAppendText("EnterCopyTo", stCC)

   'Call oUIDoc.FieldAppendText("Subject", stSubject)
   
   'The can be used if You want to add a message into the created document.
   Call oUIDoc.FieldAppendText("Body", vbNewLine & stBody)
 
   'Here the selected range is pasted into the body of the outgoing e-mail.
   Call oUIDoc.GoToField("Body")
   Call oUIDoc.Paste

   'Save the created document.
   Call oUIDoc.Save(True, False, False)
   'If the e-mail also should be sent then add the following line.
   'Call oUIDoc.Send(True)
 
   'Release objects from memory.
   Set oWorkSpace = Nothing
   Set oUIDoc = Nothing
 
   With Application
      .CutCopyMode = False
      .ScreenUpdating = True
   End With
 
   MsgBox stMsg, vbInformation
 
   'Activate Lotus Notes.
   AppActivate ("Notes")
 
End Sub

Comments:
The technique used here can also be applied for inserting charts as part of the body. In order to do it we must check that a chart is the active object before copying like the following snippet code shows

'Check to see that the active object is a chart.
If ActiveChart Is Nothing Then
   MsgBox "Please select a chart to be copied!", vbExclamation
   Exit Sub
End If

'And to copy the chart.
ActiveChart.CopyPicture xlScreen, xlPicture, xlScreen

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