Sending E-mail in Outlook

Once all of the Office documents selected in the form frmMain are automatically generated, you can send them as an e-mail message created in Outlook. This link gives you a convenient way to update people about a project.

Create an Outlook E-mail Message

  1. In the Visual Basic Editor, click Module on the Insert menu to create a new code module.

  2. Add the following SendEmail procedure:

    3. Sub SendEmail(bReport As Boolean, bSheet As Boolean, _
       bPres As Boolean)
    Dim appOL As New Outlook.Application
    Dim itemUsageEmail As Outlook.MailItem 
	  
    Set itemUsageEmail = appOL.CreateItem(olMailItem)
End Sub

    The three Boolean variables you pass into the SendEmail procedure indicate which check boxes were selected in the form frmMain. You use these variables to determine which file attachments should be added to the e-mail message. Within the SendEmail procedure, you declare two variables. The first, the variable appOL, is the declaration of a new instance of the Outlook Application object. You use the keyword New in the declaration statement to indicate that the first time the variable appOL is used in code, you create a new instance. The second, the variable itemUsageEmail, declares the variable as an Outlook MailItem object, which you set in the Set statement following the declaration.

  3. After the Set statement in the SendEmail procedure, add the following With…End block:

    4. With itemUsageEmail
    .To = "Energy Committee"
    .Subject = "New energy usage documents"
    .Body = "The following documents reflect" & _
        " the energy usage for the circuit: " & _
        g_sCircuit & "." & vbCrLf
    .Display
End With

    The With…End block sets the main properties of the e-mail message and then displays the message on the screen. If you want to send the e-mail message automatically, you can replace the Display method with the Send method in the last line of the With…End block. The message will go to the alias "Energy Committee."

  4. Before the line used to display the Outlook message, add the following If…Then condition blocks to add attachments to the message:

    5. If bReport = True Then
    .Attachments.Add g_sDBProjectPath & "Report.doc"
End If
If bSheet = True Then
    .Attachments.Add g_sDBProjectPath & "DataAnalysis.xls"
End If
If bPres = True Then
    .Attachments.Add g_sDBProjectPath & "EnergyPres.ppt"
End If

    The three If…Then condition blocks evaluate the Boolean values representing the items selected in the form frmMain. If an item is selected and the appropriate Office document is generated, the document is attached to the e-mail message.

  5. In the Properties window in the Visual Basic Editor, change the Name property of the code module to modOutlook.

  6. Click the Save button on the Standard toolbar in the Visual Basic Editor to save changes to the code.

You've now added all the code needed to create an Outlook e-mail message. Later, when you run the integrated Office solution and select the item to create an Outlook e-mail message along with the other Office documents, the Outlook e-mail message should look like this:

Click to view at full size.

NOTE
Before running the integrated solution, remove the apostrophe at the beginning of the Option Explicit statement in the modMain module if you previously added an apostrophe. The Option Explicit statement will enable you to find coding errors more easily (if any exist).

See the Solution in Action

  1. In the MyEnergy: Database window, click Forms in the Objects bar, select the item frmMain, and then click Open on the Database window toolbar. You'll see the custom Energy Management form.

  2. Select all four check boxes in the Energy Management form and click OK.

    3. Depending on your computer system's hardware and software configuration, it may take a few minutes to iterate through the data in the Access database and generate each Office document. You can watch the progress by reading the Number Of Records Read indicator in the dialog box. Once the Main procedure finishes running, you'll see the new Word report, the Excel worksheet and chart, the PowerPoint presentation, and the Outlook e-mail message with the Office documents attached. Each window displayed on your screen should look similar to the graphic shown at the end of the example that created that particular Office document.

TIP
If your code has errors you can't easily resolve, or if you didn't have time to complete this chapter, you can run the solution from the Energy.mdb file provided in the Chapter 8 folder of the practice files. This file contains the complete working code for all the sections in this chapter.

  1. Close the Energy database and exit Access.

  2. After examining the generated contents in Word, Excel, and PowerPoint, close each application without saving any changes.