First of all, a belated happy new year to everyone who regular visit my blog. I hope to continue to feed the blog with interesting material also under the year 2011. We are up to some challenges now that corporates tend to roll out Windows 7 and Office in a wider scale.
Anyway, last year I closed my English site and I plan to re-launch it during the year but without the code articles. As per agreement with Microsoft some of them have been published at MSDN. In addition, some people have been in touch with me and also with other developers in the online community about where to find my entries about controlling Lotus Notes via Excel and VBA. Until now they have only been available on my local backup.
I’m very happy to announce that Ron de Bruin has accepted to host these articles at his site. Ron has for the last couple of years built-up a great number of excellent articles on various topics like data acquisition (ADO/DAO), Ribbon UI and interacting with MS Outlook, Outlook Express and Windows Mail from MS Excel. In view of the content of his site it’s great that Lotus Notes has been added to the list of e-mail clients to be covered.
The head page for the Lotus Notes section can be found here. I have agreed to update the examples when necessary and also to add new tips when possible.
Kind regards,
Dennis
VSTO & .NET & Excel 
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
Comment by Gustavo Glez — April 27, 2012 @ 12:15 am