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:
The code
The following code is part of the above form where only the most vital parts are showed.
Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Windows.Forms
When the form is initialized the following procedure populates some items of the form:
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:
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:
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’.
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:
‘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
Posting code? Use <pre> tags for VBA and <code> tags for inline.