Lotus Notes: Send a workbook as attachment to a created e-mail

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:
Create a menu command that enables sending a workbook as an attachment to a created Lotus Notes e-mail.

VBA code :

Here we create the menu-option:

Option Explicit
Dim bcLotus As Office.CommandBarControl
 
Sub Create_Lotus_Menu()
   Dim cbNew As Office.CommandBar
   Dim bcSendTo As Office.CommandBarControl
 
   'This is a good approach when working with several language versions of Excel.
   'Create the menu command.

   Set bcSendTo = CommandBars.FindControl(Type:=msoControlPopup, ID:=30095)
   Set cbNew = bcSendTo.CommandBar
   Set bcLotus = cbNew.Controls.Add
 
   'Set the properties of the new created command.
   With bcLotus
      .BeginGroup = True
      .Caption = "Send workbook as attachment with Lotus Notes"
      .FaceId = 719
      .OnAction = "SendWithLotus"
      .Tag = "Lotus"
   End With
 
End Sub

Here we create the procedure for deleting the menu command:

Sub Delete_Lotus_Menu()
   On Error Resume Next
   'Delete the control from the File-menu.
   Set bcLotus = CommandBars.FindControl(, , Tag:="Lotus")
   bcLotus.Delete
   'Restore the built-in errorhandling.
   On Error GoTo 0
End Sub

The procedure for executing the main task:

Sub SendWithLotus()
   Dim noSession As Object, noDatabase As Object, noDocument As Object
   Dim obAttachment As Object, EmbedObject As Object
   Dim stSubject As Variant, stAttachment As String
   Dim vaRecipient As Variant, vaMsg As Variant
 
   Const EMBED_ATTACHMENT As Long = 1454
   Const stTitle As String = "Status Active workbook"
   Const stMsg As String = "The active workbook must first be saved " & vbCrLf _
         & "before it can be sent as an attachment."
 
   'Check if the active workbook is saved or not
 
   'If the active workbook has not been saved at all.
   If Len(ActiveWorkbook.Path) = 0 Then
      MsgBox stMsg, vbInformation, stTitle
      Exit Sub
   End If
 
   'If the changes in the active workbook has been saved or not.
   If ActiveWorkbook.Saved = False Then
      If MsgBox("Do you want to save the changes before sending?", _
            vbYesNo + vbInformation, stTitle) = vbYes Then _
            ActiveWorkbook.Save
   End If
 
   'Get the name of the recipient from the user.
   Do
      vaRecipient = Application.InputBox( _
            Prompt:="Please add the name of the recipient such as:" & vbCrLf _
            & "excel@microsoft.com or just the name if it's internally.", _
            Title:="Recipient", Type:=2)
   Loop While vaRecipient = ""
 
   'If the user has canceled the operation.
   If vaRecipient = False Then Exit Sub
 
   'Get the message from the user.
   Do
      vaMsg = Application.InputBox( _
            Prompt:="Please enter the message such as:" & vbCrLf _
            & "Enclosed please find the weekly report.", _
            Title:="Message", Type:=2)
   Loop While vaMsg = ""
 
   'If the user has canceled the operation.
   If vaMsg = False Then Exit Sub
 
   'Add the subject to the outgoing e-mail which also can be retrieved from the users
   'in a similar way as above.
   stSubject = "Weekly report"
 
   'Retrieve the path and filename of the active workbook.
   stAttachment = ActiveWorkbook.FullName
 
   'Instantiate the Lotus Notes COM's Objects.
   Set noSession = CreateObject("Notes.NotesSession")
   Set noDatabase = noSession.GETDATABASE("", "")
 
   'If Lotus Notes is not open then open the mail-part of it.
   If noDatabase.IsOpen = False Then noDatabase.OPENMAIL
 
   'Create the e-mail and the attachment.
   Set noDocument = noDatabase.CreateDocument
   Set obAttachment = noDocument.CreateRichTextItem("stAttachment")
   Set EmbedObject = obAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment)
 
   'Add values to the created e-mail main properties.
   With noDocument
      .Form = "Memo"
      .SendTo = vaRecipient
      .Subject = stSubject
      .Body = vaMsg
      .SaveMessageOnSend = True
   End With
 
   'Send the e-mail.
   With noDocument
      .PostedDate = Now()
      .Send 0, vaRecipient
   End With
 
   'Release objects from the memory.
   Set EmbedObject = Nothing
   Set obAttachment = Nothing
   Set noDocument = Nothing
   Set noDatabase = Nothing
   Set noSession = Nothing
 
   'Activate Excel for the user.
   AppActivate "Microsoft Excel"
 
   MsgBox "The e-mail has successfully been created and distributed.", vbInformation
 
End Sub

Comments:
First of all, 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.

The whole example can easily be turned into an add-in where the creation- and deleting-procedure are then hooked up with the events-procedures Workbook_Open and Workbook_BeforeClose.

Instead of the above approach the same task can be achieved manually by:

Open Microsoft Explorer
Choose the command Tools | Internetoptions...| Tab Programs
Select Lotus Notes as the option for the E-mail program.

Ron de BruinExcel Automation

Lotus Notes: Send a workbook as attachment to a created e-mail

Important message to visitors of this page

Ron de Bruin
Excel Automation