‘Namespaces used in the module:
Imports Domino ‘To access the objects in the Domino class.
Imports Excel = Microsoft.Office.Interop.Excel ‘To access Excel’s objects.
Imports System.Windows.Forms ‘To use the Messagebox objects.
Imports System.IO ‘To handle files.
Imports System.Diagnostics ‘Open URL and show helpfile (.CHM)

Module NotesMod
    ‘Friend makes the variables available for the whole project.
   Friend Const g_CONST_TITLE As String = “Send e-mail with Lotus Notes”
    ‘Excel variables.
   Friend g_xlApp As Excel.Application
    Friend g_xlRng As Excel.Range
    Friend g_xlcell As Excel.Range
    Friend g_xlwsSheet As Excel.Worksheet
    Friend g_xlwbBook As Excel.Workbook

    Sub Show_Help()
        ‘Show the helpfile.
       Process.Start(My.Application.Info.DirectoryPath & “Notes_Tool.chm”)
    End Sub

    Sub Visit_Link()
        ‘Visit my english site.
       Process.Start(“http://www.excelkb.com”)
    End Sub

    ‘Function to add the selected worksheets to a new workbook.
   Function Create_Save_Workbook(ByVal stBook As String, _
                                  ByVal stName As String, _
                                  ByVal alSheets As ArrayList) As Boolean
        Dim stSheetItem As String
        ‘If the file already exist then delete the file.
       If File.Exists(stName) = True Then File.Delete(stName)
        ‘Prevent the screen from flickering and surpress the message when deleting the worksheet.
       With g_xlApp
            .ScreenUpdating = False
            .DisplayAlerts = False
        End With
        ‘Add a new workbook with only one worksheet.
       Dim xlNewBook As Excel.Workbook = g_xlApp.Workbooks.Add( _
                                          Excel.XlWBATemplate.xlWBATWorksheet)
        ‘Grab the selected workbook from the form.
       g_xlwbBook = CType(g_xlApp.Workbooks(stBook), Excel.Workbook)
        ‘Copy the worksheets to the new workbook.
       For Each stSheetItem In alSheets
            If Not (g_xlwsSheet Is Nothing) Then g_xlwsSheet = Nothing
            g_xlwsSheet = CType(g_xlwbBook.Worksheets(stSheetItem.ToString), Excel.Worksheet)
            g_xlwsSheet.Copy(After:=xlNewBook.Worksheets(1))
        Next stSheetItem
        ‘Delete the first worksheet in the new workbook.
       If Not (g_xlwsSheet Is Nothing) Then g_xlwsSheet = Nothing
        g_xlwsSheet = CType(xlNewBook.Worksheets(1), Excel.Worksheet)
        g_xlwsSheet.Delete()
        ‘Save the created workbook.
       xlNewBook.Close(SaveChanges:=True, Filename:=stName)
        ‘Make objects available for the Garbish Collector.
       If Not (g_xlwsSheet Is Nothing) Then g_xlwsSheet = Nothing
        If Not (xlNewBook Is Nothing) Then xlNewBook = Nothing
        If Not (g_xlwbBook Is Nothing) Then g_xlwbBook = Nothing
        ‘Restore settings.
       With g_xlApp
            .DisplayAlerts = True
            .ScreenUpdating = True
        End With
        ‘Set the status for the function to the calling sub.
       Create_Save_Workbook = True
    End Function

    ‘Function to create e-mail, add attachments and send.
   Function Create_Email_Notes(ByVal stPriority As String, _
                                ByVal stSubject As String, _
                                ByVal oSendTo As Object, _
                                ByVal oSendCopy As Object, _
                                ByVal stMessage As String, _
                                ByVal bFlag As Boolean, _
                                Optional ByVal stFile As String = “”, _
                                Optional ByVal oFiles As Object = “”) As Boolean
        ‘Create and instantiate the Lotus Notes session.
       Dim notSession As New Domino.NotesSessionClass
        CType(notSession, Domino.NotesSession).Initialize(“”)
        ‘Open the local mail database.
       Dim notMailDb As Domino.NotesDatabase = _
        CType(notSession, Domino.NotesSessionClass).GetDbDirectory(“”).OpenMailDatabase
        ‘Grab the profile that holds the signature which will be added to the outgoing e-mail.
       Dim notProfile As Domino.NotesDocument =  _
       CType(notMailDb.GetProfileDocument(“CalendarProfile”), Domino.NotesDocument)
        ‘Grab the signature, which only can be done with signatures in plain text.
       Dim stSignature As String = CStr(notProfile.GetFirstItem(“Signature”).Text)
        ‘Create the outgoing e-mail.
       Dim notDoc As Domino.NotesDocument = _
        CType(notMailDb.CreateDocument, Domino.NotesDocument)
        ‘Create the body space in the outgoing e-mail.
       Dim notItem As Domino.NotesRichTextItem = _
        CType(notDoc.CreateRichTextItem(“Body”), Domino.NotesRichTextItem)
        ‘Create the attachment space in the outgoing e-mail.
       Dim notAttach As Domino.NotesRichTextItem = _
        CType(notDoc.CreateRichTextItem(“STFILE”), Domino.NotesRichTextItem)
        ‘Declare the variable for attached file(s).
       Dim notEmbedded As Domino.NotesEmbeddedObject

        If bFlag Then
            ‘Add the created Excel file as attachment to the outgoing e-mail.
           notEmbedded =   _
            CType(notAttach.EmbedObject(EMBED_TYPE.EMBED_ATTACHMENT, _
            “”, stFile), Domino.NotesEmbeddedObject)
        Else
            ‘Add the selected Excel files as attachments to the outgoing e-mail.
           Dim oItem As Object
            For Each oItem In CType(oFiles, Array)
                notEmbedded =   _
               CType(notAttach.EmbedObject(EMBED_TYPE.EMBED_ATTACHMENT, _
                “”, CStr(oItem)), Domino.NotesEmbeddedObject)
            Next
        End If
        Try
            ‘Populate the outgoing e-mail’s main properties.
           With notDoc
                .AppendItemValue(“Form”, “Memo”)
                .AppendItemValue(“Subject”, stSubject)
                .AppendItemValue(“SendTo”, oSendTo)
                .AppendItemValue(“CopyTo”, oSendCopy)
                .AppendItemValue(“DeliveryPriority”, stPriority)
                With notItem
                    .AppendText(stMessage & vbCrLf & vbCrLf)
                    .AppendText(stSignature & vbCrLf & vbCrLf)
                End With
                .SaveMessageOnSend = True
                .Send(True, “”)
            End With
            ‘Set the status for the function to the calling sub.
           Create_Email_Notes = True
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
            ‘Set the status for the function to the calling sub.
           Create_Email_Notes = False
        Finally
            ‘Place variables to be handled by the Garbage Collector (GC).
           notEmbedded = Nothing
            notAttach = Nothing
            notItem = Nothing
            notProfile = Nothing
            notDoc = Nothing
            notMailDb = Nothing
            notSession = Nothing
        End Try
    End Function
End Module