Creating and deploying Managed COM add-ins with VB.NET 2005 – Part III

Creating and deploying Managed COM add-ins with VB.NET 2005 – Part III

This is the third post on the subject and You can find the earlier post at the following links:
Part I – Introduction
Part II – Connecting

For all code posted here I have intentionally tried to avoid a) the core .NET-approach and b) the use of classes. Instead I have tried to focus on clarity and to use a simple classic VB/VBA -approach.

The Notes Tool – Handle workbooks
With this post I will start to cover the tool itself which target on sending workbooks or worksheets as attachments to outgoing e-mails with Lotus Notes. The first option is to send workbooks as attachments and below is the form that provides end users with an interface to populate required parameters:

Notes Workbooks

The code
The following code is part of the above form where only the most vital parts are showed.

‘The following namesspaces are required for the code behind the form.
Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Windows.Forms

When the form is initialized the following procedure populates some items of the form:

Private Sub frmwbook_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            ‘Create the collection of items for the priority status.
           ‘I have not discovered any smooth way to align items in a combobox…
           Dim Priority() As String = _
                            New String() {”    Low”, ”    Normal”,  ”    High”}
            With Me.ComboBoxPriority
                ‘Populate the combobox with the collection.
               .Items.AddRange(Priority)
                ‘Select ‘Normal’ as default value.
               .SelectedIndex = 1
            End With
            ‘Title
           Me.Text = g_CONST_TITLE
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

Below is the code for adding workbooks and we also need to add the ‘OpenFileDialog’ control to the form:

Private Sub ButtonAttachFiles_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonAttachFiles.Click
        Try
            ‘Let the users select wanted Excel files
           ‘to be attached to the outgoing e-mail.
           With Me.OpenFileDialog_Workbooks
                .Title = “Select workbook(s) to send as attachment(s) to e-mail”
                .Multiselect = True
                .Filter = “Excel files (*.xls)|*.xls|Excel files (*.xls)|*.xls”
                If .ShowDialog() = Windows.Forms.DialogResult.OK Then
                    Me.ListBox_Files.Items.AddRange(.FileNames)
                End If
            End With
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

To remove workbooks the following procedure is used:

Private Sub ButtonRemoveFiles_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonRemoveFiles.Click
        Dim iCounter As Integer
        For iCounter = Me.ListBox_Files.SelectedItems.Count – 1 To 0 Step -1
            Me.ListBox_Files.Items.Remove(Me.ListBox_Files.SelectedItems(iCounter))
        Next iCounter
    End Sub

To add recipients the following procedure is used. A similar code is used to add the list of recipients for ‘Copy To’.

    Private Sub ButtonAddFromRangeSendTo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonAddFromRangeSendTo.Click
        Try
            ‘The RefEdit object is not available in .NET
           ‘so instead the Inputbox is used.
           ‘The Excel variables are declared in a standardmodule.
           g_xlRng = CType(g_xlApp.InputBox( _
           “Please select the range that contain the list of recipients as Copy To:”, _
                                 g_CONST_TITLE, , , , , ,   8), Excel.Range)
            g_xlcell = CType(g_xlRng.Cells, Excel.Range)
            Try
                ‘Add recipients to the list of Send To.
               If Not g_xlRng.Columns.Count > 1 Then
                    With Me.ListBox_SendTo
                        .Items.Clear()
                        For Each g_xlcell In g_xlRng
                            If Not (g_xlcell.Value) Is Nothing Then _
                                          .Items.Add(g_xlcell.Value)
                        Next g_xlcell
                    End With
                Else
                    MsgBox(“The list of recipients can only be in one column.”, _
                                      MsgBoxStyle.Information, g_CONST_TITLE)
                    Exit Sub
                End If
            Catch ex As Exception
                MessageBox.Show(ex.ToString, g_CONST_TITLE)
            End Try
        Catch ex As Exception
            ‘Leave empty – it takes care of the cancel for the inputbox.
       Finally
            ‘Release the objects.
           If Not g_xlcell Is Nothing Then g_xlcell = Nothing
            If Not g_xlRng Is Nothing Then g_xlRng = Nothing
        End Try
    End Sub

The following code is associated with the Send e-mail button:

Private Sub ButtonSendEmail_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonSendEmail.Click
        ‘Check that all entries are done before continuing the process.
       If Me.ListBox_Files.Items.Count = 0 Then
            MsgBox(“Please add at least one Excel file to be sent with the e-mail.”, _
            MsgBoxStyle.Information, g_CONST_TITLE)
            Me.ButtonAttachFiles.Focus()
            Exit Sub
        ElseIf Len(Me.TextBox_Subject.Text) = 0 Then
            MsgBox(“Please enter a subject.”, MsgBoxStyle.Information, g_CONST_TITLE)
            Me.TextBox_Subject.Focus()
            Exit Sub
        ElseIf Me.ListBox_SendTo.Items.Count = 0 Then
            MsgBox(“Please add at least one reciepent to send the e-mail to.”,  _
            MsgBoxStyle.Information, g_CONST_TITLE)
            Me.ButtonAddFromRangeSendTo.Focus()
            Exit Sub
        ElseIf Len(Me.TextBox_Message.Text) = 0 Then
            MsgBox(“Please add a comment to the e-mail.”, _
            MsgBoxStyle.Information, g_CONST_TITLE)
            Me.TextBox_Message.Focus()
            Exit Sub
        End If
        Try
            ‘Retrieve the subject for the e-mail.
           Dim stSubject As String = Me.TextBox_Subject.Text
            ‘Retrieve the message of the e-mail.
           Dim stMsg As String = Me.TextBox_Message.Text
            ‘Retrieve the priority of the e-mail.
           Dim stPriority As String = LTrim(Me.ComboBoxPriority.Text)
            ‘Grab the list of workbooks.
           Dim oFiles(Me.ListBox_Files.Items.Count – 1) As Object
            Me.ListBox_Files.Items.CopyTo(oFiles, 0)
            ‘Grab the list of recipients of Send To.
           Dim oSendTo(Me.ListBox_SendTo.Items.Count – 1) As Object
            Me.ListBox_SendTo.Items.CopyTo(oSendTo, 0)
            ‘Grab the list of recipients of Copy To.
           Dim oCopyTo(Me.ListBox_CopyTo.Items.Count – 1) As Object
            Me.ListBox_CopyTo.Items.CopyTo(oCopyTo, 0)
            ‘Create and send the e-mail, calls a function in a standardmodule.
           If Create_Email_Notes( _
            stPriority, stSubject, oSendTo, oCopyTo, stMsg, False, , oFiles) _
            = True Then
            MessageBox.Show(“The e-mail was successfully created and has been sent.”, _
                g_CONST_TITLE, MessageBoxButtons.OK)
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, g_CONST_TITLE)
        Finally
            Me.Close()
        End Try
    End Sub

By adding a LinkLabel control to the form we can add a Web-style link to the form.

In the next post I will cover the solution for worksheets in detail.

Kind regards,
Dennis

Posted in Uncategorized

Leave a Reply

Your email address will not be published. Required fields are marked *