5.6. Automation Examples

So let's bring together all you've seen in this chapter with a few sample implementations of OLE automation servers.

5.6.1. Using Word as a Report Writer from VB

This first application demonstrates how you can seamlessly use Microsoft Word to print output from your VB program without the user knowing that you have actually used Microsoft Word:

Private Sub cmdWordDoc_Click()

   'create an error handler
   On Error GoTo cmdWordDoc_Err
        
      'create the local Early Bound object variables
   Dim oWord           As Word.Application
   Dim oWordActiveDoc  As Word.Document
   Dim oWordSel        As Word.Selection
    
   'Create a new instance of Word
   Set oWord = New Word.Application
   'Create a new document object
   Set oWordActiveDoc = oWord.Documents.Add
   Set oWordSel = oWord.Selection
            
      'Do some work with the Selection object
      oWordSel.TypeText "This is some text from the VB app."
      oWordSel.WholeStory
      oWordSel.Font.Name = "Arial"
      oWordSel.Font.Size = 12
      oWordSel.Font.Bold = wdToggle
                    
      'Now print out the doc
      oWordActiveDoc.PrintOut
          
   'always tidy up before you leave
   Set oWordSel = Nothing
   Set oWordActiveDoc = Nothing
            
   Set oWord = Nothing
        
   Exit Sub

cmdWordDoc_Err:
   MsgBox Err.Number & vbCrLf & Err.Description & vbCrLf _
          & Err.Source
    
End Sub

Because this example uses early binding, you'll have to use the References dialog to add a project reference to the Word 8 Object Model.

Note that this application appears seamless because the application's Visible property is False by default. If you wanted to show the Word application window in operation (which may be required while debugging), simply set the property to True.

5.6.2. Using Email Within VB

This application demonstrates how you can work with a late bound object. The OLE server in this instance is Windows MAPI. Using MAPI in this way uses Outlook sort of through the back door; you don't actually create an instance of Outlook, but this sample demonstrates how closely tied MAPI and Outlook are. In fact, the mail side of Outlook isn't much more than a nice GUI to the Windows MAPI. If you are connected to an Exchange server when this simple application runs, the mail is sent automatically; otherwise, the mail is placed in Outlook's outbox, ready for you to send. You may also have to change the profile name to match that on your own system.

The sample function shown below is called from a form containing a text box (txtDomain) that holds the domain name of the recipients, and a list box (lstEmails) that holds the individual addresses of the recipients. This example is in fact part of a working application used several times a day to send test messages to new email accounts:

Private Function SendReturnEMail() As Boolean

' create an error handler
On Error GoTo SendReturnEMail_Err

   'set the default return value
   SendReturnEMail = False
        
   'we're using late binding for this app
   Dim objSession   As Object
   Dim objMessage   As Object
   Dim objRecipient As Object
        
   'declare some other utility variables
   Dim i            As Integer
   Dim sSubject     As String
   Dim sText        As String
   Dim sName        As String
        
   'set up the email message text
   sText = "This is an automatic test message, " & _
           vbCrLf & _
           "Please reply to the sender confirming receipt."
   'and the subject
   sSubject = "Test Message"
               
   'start with the top of the mapi hierarchy —
   'the session object
   Set objSession = CreateObject("mapi.session")
      'use the local Outlook default profile
      objSession.LogOn profilename:="Microsoft Outlook"

      'this application will send a number of test messages
      'to the members of a particular domain
      For i = 0 To lstEmails.ListCount - 1
         'build the addresses from the names in the list
         'and the given domain name
         sName = Trim(lstEmails.List(i)) & "@" & _
                 Trim(txtDomain.Text)
         'now create a new message object
         Set objMessage = objSession.outbox.messages.Add
            'feed in the required property values for the
            'message
            objMessage.subject = sSubject
            objMessage.Text = sText
            'create a new recipient for this message
            Set objRecipient = objMessage.Recipients.Add
               'and set it's properties
               objRecipient.Name = sName
               objRecipient.Type = 1
               'make sure the email address is resolved
               objRecipient.resolve
               'now send the message
               objMessage.Send showdialog:=False
               'tidy up this message
            Set objRecipient = Nothing
         Set objMessage = Nothing
      'and go round again for the next one
      Next i
      'all done so off we go
      objSession.logoff
   'tidying up as always
   Set objSession = Nothing
   'set the success return value
   SendReturnEMail = True
    
   Exit Function

SendReturnEMail_Err:
   MsgBox Err.Number & vbCrLf & Err.Description & vbCrLf _
          & Err.Source
           
End Function

5.6.3. Output from VB to Excel

To finish with, here's an easy little application that places values from a VB application into an Excel spreadsheet. There are project-level (early bound) references created to both Excel and the ADODB 2.0 Reference Library. An ADO recordset has already been created and is passed as a parameter to the OutputToExcel function. The function creates an instance of a new Excel workbook and worksheet, then copies the values from the ADO recordset into the worksheet. Excel's functionality is used to perform a simple calculation on the data, the worksheet is saved, Excel is closed down, and all references are tidied up.

This example illustrates the power of a glue language such as Visual Basic. Here VB is acting as the glue between ADO, which is an ActiveX server, and Excel—controlling both to produce a simple yet patently powerful and seamless application:

Private Function OutputToExcel(oADORec As ADODB.Recordset) _
                 As Boolean

On Error GoTo cmdExcel_Err
    
   'set up the default return value
   OutputToExcel = False

   ' Declare the Excel object variables
   Dim oXLApp  As Excel.Application
   Dim oXLWBook As Excel.Workbook
   Dim oXLWSheet As Excel.Worksheet

   'start at the top of the model
   Set oXLApp = New Excel.Application
      'and work your way down
      Set oXLWBook = oXLApp.Workbooks.Add
         'until you get to the worksheet
         Set oXLWSheet = oXLWBook.Worksheets.Add
            
            oXLWSheet.Cells(1, 1).Value = oADORec!FirstValue
            oXLWSheet.Cells(2, 1).Value = oADORec!SecondValue
                
            ' do some stuff in Excel with the values
            oXLWSheet.Cells(3, 1).Formula = "=R1C1 + R2C1"
               
            ' save your work
            oXLWSheet.SaveAs "vb2XL.xls"
                
            'quit Excel
            oXLApp.Quit
            
            ' always remember to tidy up before you leave
         Set oXLWSheet = Nothing
      Set oXLWBook = Nothing
   Set oXLApp = Nothing
    
   OutputToExcel = True

   Exit Function

cmdExcel_Err:
   MsgBox Err.Description & vbCrLf & Err.Number & _
          vbCrLf & Err.Source
    
End Function