The Publish application must connect to both the ODBC datasource you built earlier and the email system that will be used to send the newsletter. In this section you will use Remote Data Objects (RDOs) to connect to the ODBC datasource. These objects allow you to connect to a database, execute queries, and modify the information. RDOs are a feature of Visual Basic, Enterprise Edition, and are not available in the Professional or Standard Editions of Visual Basic. If you have the Professional Edition of Visual Basic, it is possible to interface with a database by using Data Access Objects (DAOs), but that type of imple mentation is not discussed here. The email system will be accessed through the MAPI controls that you have already placed on the form.
Before you can use Remote Data Objects to access the ODBC source, you must set a reference to the RDO component. You set a reference to any component by using the References dialog box that is accessed through the Visual Basic Tools/References command. Open the References dialog box, and set a reference to the component named Microsoft Remote Data Object 1.0.
The RDO component is designed specifically to communicate with ODBC datasources. It is made up of a set of objects that can interface directly with ODBC drivers. Table 6-2 lists the Remote Data Objects. Figure 6-18, on page 224, shows the Remote Data Object model.
Table 6-2.The Remote Data Objects
| Object | Description |
| rdoEngine | An object that represents the ODBC datasource itself. |
| rdoEnvironment | An object that represents a set of connections to various datasources for a particular user name. This object also provides for batch transactions between datasources. |
| rdoError | An object that contains information about errors that might occur while a datasource is being accessed. |
| rdoConnection | An object that represents an open connection to a specific database on a datasource. |
| rdoTable | An object that represents a table inside a datasource. |
| rdoPreparedStatement | An object that represents a predefined query. |
| rdoResultset | An object that represents the results of a query on the datasource. |
| rdoColumn | An object that represents a column of data in a table or result set. |
| rdoParameter | An object that represents a parameter associated with a predefined query. |
Figure 6-18.
The Remote Data Object model.
Open the code window associated with frmPublish by selecting the form in the Project window and clicking on the View Code button. In the code window, define two variables for accessing the ODBC datasource by adding the following code:
Option Explicit
Private dbSubscribers As RDO.rdoConnection
Private rsSubscribers As RDO.rdoResultset Note how these variables are defined. Each of the variables represents an object in the RDO component; you will use one to access the datasource and the other to return a set of records. The variables can have any name you want, but the data types are defined by the RDO component. The definitions use the syntax Component_Name.Object. The name of the component for Remote Data Objects is RDO, and the object names are those that were defined in Table 6-2. These variables will give you the necessary functionality to open the datasource and retrieve records.
Begin building your project in the Form_Load event procedure of the form. In this procedure, you will open the datasource and connect to the mail server. You will also handle routine functions, such as changing the mouse pointer and handling errors. Add the following code to the Form_Load procedure:
Private Sub Form_Load()
On Error GoTo LoadErr
Dim intPrevPointer As Integer
intPrevPointer = Screen.MousePointer
Screen.MousePointer = 11
This code sets up an error-handling structure for the function and changes the mouse to an hourglass while the form loads. Both error handling and mouse handling are critical to making the application robust. The error-handling structure simply identifies a line label that directs execution to an error-handling routine in the event of an error. You will build the actual error-handling routine later. The mouse-handling code saves the current value of the mouse pointer to an intermediate variable and changes the mouse pointer to an hourglass. Later in the code, you will restore the mouse pointer to its previous value.
Connecting to the datasource is a function of the RDO component. You will use the rdoConnection object to establish a connection to the datasource, and you will use the rdoResultset object to retrieve records from the source. Add the following code to the Form_Load procedure to establish a connection and run a query:
Set dbSubscribers = rdoEngine.rdoEnvironments(0). _
OpenConnection("subscribers")
Set rsSubscribers = dbSubscribers.OpenResultset _
("SELECT * FROM People", rdOpenDynamic) The OpenConnection method of the rdoEnvironment object is used to create a connection to the datasource. The argument for the OpenConnection method is the name of the datasource you want to open. If your datasource requires other information, such as a user ID and password, that can be specified here as well.
The OpenResultset method of the rdoConnection object is used to perform a query on the datasource. The arguments for the method allow you to specify a query, as well as the type of result set to return. In the preceding code, you are returning all the entries in the datasource and making the result set "dynamic," which means that you will be able to navigate the result set completely, without restriction.
You connect to the mail server with the MAPI controls. There are two different MAPI controls: MAPISession and MAPIMessages. The MAPISession control connects to the server, and the MAPIMessages control sends or receives messages. The MAPISession control has four commonly used properties and two commonly used methods. Table 6-3 lists the properties, and Table 6-4 lists the methods that you will use in this project.
Table 6-3.MAPISession Properties Used in This Project
| Property | Description |
| UserName | The name of the user account that will connect to the mail server |
| Password | The password for the given user account |
| SessionID | A property that returns a nonzero value identifying the connection if the connection is successful |
| DownloadMail | A Boolean property that determines whether mail is downloaded for the given account when a connection is made |
Table 6-4.MAPISession Methods Used in This Project
| Method | Description |
| SignOn | Connects to the mail server with the given user name and password |
| SignOff | Disconnects from the mail server |
To connect to the mail server under the administrator account, add the following code to the Form_Load procedure:
MAPISession1.DownloadMail = False
MAPISession1.UserName = "POSTMASTER"
MAPISession1.Password = "your password"
MAPISession1.SignOn Note that you would need to enter the appropriate user name and password for your mail server.
Once the connection is established, the mail is sent via the MAPIMessages control. The MAPIMessages control allows you to construct a message, add attachments, and send the message to a recipient. The commonly used MAPIMessages properties and methods are shown in Table 6-5 and Table 6-6, respectively.
Table 6-5.MAPIMessages Properties Used in This Project
| Property | Description |
| SessionID | A number that identifies the current session. This number comes from the MAPISession control when the connection is made. |
| MsgSubject | The text for the subject line of the message. |
| MsgNoteText | The body of the message. |
| RecipDisplayName | The name of the recipient as it is displayed in email. This is not the user account. For example, a user account called JOHNS might correspond to a RecipDisplayName of John Smith. User accounts can be accessed through the RecipAddress property. |
| RecipAddress | The address of the recipient—for example, JOHNS. |
| AttachmentPathName | The fully qualified path and filename of an attachment to the message. |
Table 6-6.MAPIMessages Methods Used in This Project
| Method | Description |
| Compose | Resets the message buffer and allows a new message to be created |
| Send | Sends the message and all attachments to the recipient |
Add the following code to the Form_Load procedure to enable the MAPIMessages control.
MAPIMessages1.SessionID = MAPISession1.SessionID
To complete the Form_Load event procedure, you must restore the mouse pointer and provide the error-handling routine. The mouse pointer is restored just before you exit the procedure, and the error-handling call is separated from the rest of the procedure. Add the following code to complete the Form_Load procedure:
LoadExit:
Screen.MousePointer = intPrevPointer
Exit Sub
LoadErr:
ErrorHandler Err.Number, Err.Description, "Form_Load"
Resume LoadExit
End Sub The Publish application allows the user to select a file to include as an attachment. This attachment is the newsletter itself. In this way, the user can create a newsletter in any application that the mail system supports and simply select it, using the file controls that you placed on the form.
The file controls used in this project are independent controls; however, you can make them work together by adding some simple code. The following code causes the drive box, folder box, and file box to coordinate their behavior so that they display the correct folders and files for any drive and folder combination:
Private Sub dirPublish_Change()
filPublish.Path = dirPublish.Path
End Sub
Private Sub drvPublish_Change()
dirPublish.Path = drvPublish.Drive
End Sub Publishing the newsletter is a matter of reading all the recipients' names from the database, composing a message, and mailing the newsletter. You will implement these steps in the Publish routine. You will also build error handling and mouse handling into the Publish routine. The publishing occurs when the user clicks on the Publish button. To begin the publish routine, add the following code to the cmdPublish_Click procedure:
Private Sub cmdPublish_Click()
On Error GoTo PublishErr
Dim intPrevPointer As Integer
intPrevPointer = Screen.MousePointer
Screen.MousePointer = 11 Before the user can send any messages, the Publish application must make sure that a file has been selected and that there are names of subscribers in the database. If a file has not been properly selected, or if there are no subscribers in the database, an error will result. Add the following code to check whether a file has been selected and whether the database has subscribers:
If filPublish.filename = "" Then
MsgBox "Please pick a file to mail.", 16, "Publisher"
GoTo PublishExit
End If
If rsSubscribers.BOF And rsSubscribers.EOF Then
MsgBox "No subscribers are in the database.", 16, "Publish"
GoTo PublishExit
End If The newsletter is published by means of a loop in which the user creates a series of messages to send. The names are retrieved from the database one by one. The Compose method is used to start the message, and then the MsgSubject and MsgNoteText are assigned. The RecipDisplayName and the RecipAddress properties are set to the Name and Email fields in the subscribers database. Note that the transport protocol, SMTP, was added to the RecipAddress property in the code below. (SMTP stands for Simple Mail Transfer Protocol.) This might not be required; it depends on your mailing system. Finally, the user attaches the newsletter, using the AttachmentPathName property, and mails it with the Send method. Add the following code to allow the user to send the newsletter to all recipients in the subscribers datasource:
rsSubscribers.MoveFirst
Do While Not rsSubscribers.EOF
MAPIMessages1.Compose
MAPIMessages1.MsgSubject = "Company Newsletter"
MAPIMessages1.MsgNoteText _
= "The Monthly Company Newsletter is attached!"
MAPIMessages1.RecipDisplayName = rsSubscribers!Name
MAPIMessages1.RecipAddress = "SMTP:" & _
rsSubscribers!email
MAPIMessages1.AttachmentPathName = filPublish.Path & _
"\" & filPublish.filename
MAPIMessages1.Send
rsSubscribers.MoveNext
Loop
MsgBox "Newsletter Published!", 0, "Publisher" You complete the Publish routine by adding error handling and mouse handling, just as for other routines. Add the following code to complete the cmdPublish_Click procedure:
PublishExit:
Screen.MousePointer = intPrevPointer
Exit Sub
PublishErr:
ErrorHandler Err.Number, Err.Description, "Publish"
Resume PublishExit
EndSub The ErrorHandler routine takes care of error handling. This routine simply displays a message box that describes any errors that occur. Add the following code to construct the error-handling procedure:
Private Sub ErrorHandler(lngNumber As Long, _
strDescription As String, strProcedure As String)
On Error GoTo 0
MsgBox "System Error!" & Chr$(10) & "Error #" & _
Format$(lngNumber) & Chr$(10) & strDescription & _
Chr$(10) & "In Procedure " & strProcedure, 16, "Publisher"
End Sub
Your project also supplies a Cancel button that can be used to exit the project without publishing the newsletter. This button simply unloads the form and terminates the project. Add the following code to the cmdCancel_Click procedure to allow the user to exit the project without publishing the newsletter:
Private Sub cmdCancel_Click()
Unload Me
End Sub The user can exit the application in two different ways: either by selecting the Cancel button or by clicking on the control box to close the form. In both cases, the Form_Unload event will trigger. In the Form_Unload procedure, you will close the database connection and the mail server connection. Add the following code to the Form_Unload procedure to complete the code for the Publish application:
Private Sub Form_Unload (Cancel As Integer)
MAPISession1.SignOff
rsSubscribers.Close
dbSubscribers.Close
Set rsSubscribers = Nothing
Set dbSubscribers = Nothing
End
End Sub Listing 6-2 shows the complete Visual Basic code, along with comments for the Publish application.
Listing 6-2.
The Visual Basic code for the Publish application.
Option Explicit
Private dbSubscribers As RDO.rdoConnection
Private rsSubscribers As RDO.rdoResultset
Private Sub Form_Load()
'Author: New Technology Solutions, Inc.
'Purpose: Connect to datasource and mail server
'6/10/96 Original
On Error GoTo LoadErr
'Change pointer to hourglass
Dim intPrevPointer As Integer
intPrevPointer = Screen.MousePointer
Screen.MousePointer = 11
'Connect to datasource
Set dbSubscribers = rdoEngine.rdoEnvironments(0). _
OpenConnection("subscribers")
Set rsSubscribers = dbSubscribers. _
OpenResultset("SELECT * FROM People", rdOpenDynamic)
'Connect to mail server
MAPISession1.DownloadMail = False
MAPISession1.UserName = "POSTMASTER"
MAPISession1.Password = "your password"
MAPISession1.SignOn
MAPIMessages1.SessionID = MAPISession1.SessionID
LoadExit:
Screen.MousePointer = intPrevPointer
Exit Sub
LoadErr:
ErrorHandler Err.Number, Err.Description, "Form_Load"
Resume LoadExit
End Sub
Private Sub dirPublish_Change()
filPublish.Path = dirPublish.Path
End Sub
Private Sub drvPublish_Change()
dirPublish.Path = drvPublish.Drive
End Sub
Private Sub cmdPublish_Click()
'Author: New Technology Solutions, Inc.
'Purpose: Publish the newsletter
'6/10/96 Original
On Error GoTo PublishErr
'Change pointer to hourglass
Dim intPrevPointer As Integer
intPrevPointer = Screen.MousePointer
Screen.MousePointer = 11
'Make sure a file has been selected
If filPublish.filename = "" Then
MsgBox "Please pick a file to mail.", 16, "Publisher"
GoTo PublishExit
End If
'Make sure there is someone to send it to
If rsSubscribers.BOF And rsSubscribers.EOF Then
MsgBox "No subscribers are in the database.", 16, "Publish"
GoTo PublishExit
End If
rsSubscribers.MoveFirst
Do While Not rsSubscribers.EOF
'Build the message to send
MAPIMessages1.Compose
MAPIMessages1.MsgSubject = "Company Newsletter"
MAPIMessages1.MsgNoteText _
= "The Monthly Company Newsletter is attached!"
'Specify the name as it will appear in the email
MAPIMessages1.RecipDisplayName = rsSubscribers!Name
'Specify the transport protocol and the
'email address. This may vary, depending on
'your mailing system.
MAPIMessages1.RecipAddress = "SMTP:" & _
rsSubscribers!email
'Attach the newsletter
MAPIMessages1.AttachmentPathName = filPublish.Path & _
"\" & filPublish.filename
'Send the message
MAPIMessages1.Send
'Move to next recipient
rsSubscribers.MoveNext
Loop
MsgBox "Newsletter Published!", 0, "Publisher"
PublishExit:
Screen.MousePointer = intPrevPointer
Exit Sub
PublishErr:
ErrorHandler Err.Number, Err.Description, "Publish"
Resume PublishExit
End Sub
Private Sub ErrorHandler(lngNumber As Long, _
strDescription As String, strProcedure As String)
'Author: New Technology Solutions, Inc.
'Purpose: Handle runtime errors
'6/10/96 Original
On Error GoTo 0
MsgBox "System Error!" & Chr$(10) & "Error #" & _
Format$(lngNumber) & Chr$(10) & strDescription & _
Chr$(10) & "In Procedure " & strProcedure, 16, "Publisher"
End Sub
Private Sub cmdCancel_Click()
Unload Me
End Sub
Private Sub Form_Unload(Cancel As Integer)
'Disconnect from mail server
MAPISession1.SignOff
'Close datasource
rsSubscribers.Close
dbSubscribers.Close
'Destroy objects
Set rsSubscribers = Nothing
Set dbSubscribers = Nothing
'End application
End
End Sub Now that the code for the Publish application is finished, you can make an executable file. First save the Publish project, and then select Make EXE File from the File menu. Name the file publish.exe, and click the OK button. You can test the application by entering some data in the subscribers database with the Visual Basic Data Manager and running the application. Combined with the HTML page you created previously, this project is a simple but complete intranet publishing system.