Creating and deploying Managed COM add-ins with VB.NET 2005 – Part IV
This is the fourth post on the subject and You can find the earlier post at the following links:
Part I – Introduction
Part II – Connection
Part III – Notes Tool -Workbooks
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 worksheets
The way the tool handles worksheets is similar to how it handles workbooks. Therefore only code behind the form that is unique for worksheets is presented. Below is the form that that provides end users with an interface to populate required parameters:
Make sure that the following namespaces exist in the form:
Imports System.IO ‘To handle files.
The following snippet code is part of the Load event for the form:
For Each g_xlwbBook In g_xlApp.Workbooks
The below event is used to populate the list of worksheets:
‘Clear the list.
‘The selected workbook.
g_xlwbBook = _
‘Populate the list of worksheets.
For Each g_xlwsSheet In g_xlwbBook.Worksheets
Catch ex As Exception
If Not (g_xlwsSheet Is Nothing) Then g_xlwsSheet = Nothing
If Not (g_xlwbBook Is Nothing) Then g_xlwbBook = Nothing
The following code makes sure that only letters and numbers are used for the wanted filename:
If Not Char.IsLetterOrDigit(e.KeyChar) Then
e.Handled = True
Below is a snippet code for the Send e-mail Button:
‘Subfolder to store created attached workbooks.
Const stFolder As String = “c:LotusAttachments”
‘Check if the subfolder exists or not. If not then create it.
If Not (Directory.Exists(stFolder)) Then
‘The With statement is considered to be a good practice
‘however here it’s not possible to use it for the Me-object.
‘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)
‘Retrieve the wanted name of the file to be created and add the path.
Dim stFileName As String = stFolder & “” & _
Me.TextBox_TextFile.Text & “.xls”
‘Retrieve selected workbook’s name.
Dim stwbBook As String = Me.ListBox_Workbooks.SelectedItem.ToString
‘Grab the list of selected worksheets.
Dim alWsheets As New ArrayList
‘Grab the list of recipients of Send To.
Dim oSendTo(Me.ListBox_SendTo.Items.Count – 1) As Object
‘Grab the list of recipients of Copy To.
Dim oCopyTo(Me.ListBox_CopyTo.Items.Count – 1) As Object
‘Call a function in a standardmodule to create and save the workbook.
If Create_Save_Workbook( _
stwbBook, stFileName, alWsheets) = True Then
‘Call a function in a standardmodule to reate and send the e-mail.
If Create_Email_Notes( stPriority, stSubject, oSendTo, _
oCopyTo, stMsg, True, stFileName) = True Then _
(“The e-mail was successfully created and has been sent.”, _
Catch ex As Exception
In the next post the functions etc in the standard module are presented which ends the presentation of the Notes Tool.