VSTO & .NET & Excel

January 13, 2011

Lotus Notes & Excel & VBA

Filed under: Developer sites, Excel, Tools — Dennis M Wallentin @ 11:51 pm

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

Advertisements

1 Comment »

  1. 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


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: