Visual Basic for Applications/Outlook

This lesson introduces Outlook macros.

Objectives and Skills

Objectives and skills for Outlook scripting include:

• Using Microsoft Outlook objects

Readings

1. Microsoft: Set Reference to a Type Library
2. Microsoft: Automating Outlook from a Visual Basic Application
3. Microsoft: Folder Items
4. Microsoft: MailItem Object
5. Microsoft: MailItem Object Members
6. Microsoft: How to Obtain the Email Address of a Recipient

Examples

'This macro displays information from the first message in the Outlook Inbox.

Option Explicit

Sub OutlookFromExcel()
    Dim Outlook As Outlook.Application
    Dim Namespace As Outlook.Namespace
    Dim MAPIFolder As Outlook.MAPIFolder
    Dim MailItem As Outlook.MailItem
    
    Set Outlook = New Outlook.Application
    Set Namespace = Outlook.GetNamespace("MAPI")
    Namespace.Logon
    Set MAPIFolder = Namespace.GetDefaultFolder(olFolderInbox)
    Set MailItem = MAPIFolder.Items(1)
    
    Debug.Print "Received: " & MailItem.ReceivedTime
    Debug.Print "From: " & MailItem.SenderEmailAddress
    Debug.Print "Subject: " & MailItem.Subject
    
    Namespace.Logoff
End Sub

Activities

In these activities you will create macros which interact with Outlook messages.

1. Outlook Inbox
   1. Create a macro that pulls Inbox data from the Outlook Inbox and saves it in the active worksheet. Include columns for SenderName, SenderEmailAddress, Recipients, CC, BCC, Subject, Size, SentOn, and ReceivedTime. Note that Recipients is a collection of Recipient objects. You'll need to use a For or For Each loop to retrieve each of the Recipient items. Your module should include Option Explicit, Dim, and use appropriate data types for variables.
2. Generate Email
   1. Create an Excel macro that generates email messages based on data in the active worksheet. Create a new worksheet with columns for Recipient, Subject, and Message. Add one row of data for testing using yourself as the recipient, and any subject and message you like.
   2. Create a macro that pulls data from the worksheet and sends a new mail message to the given recipient with the given subject and message.
   3. Extend the macro so that it will send a new message for each row of data in the worksheet. Test the macro with at least three rows of data.
3. Outlook Calendar
   1. Create an Excel macro that generates calendar entries based on data in the active worksheet.
   2. Create a new worksheet with columns for Subject, Start Date, Start Time, End Date, and End Time. Add several rows of data. Include appointments, such as a class meeting schedule, and events, such as holidays when classes are not in session. Events will have only a start date, and no ending date or times.
   3. Create a macro that pulls data from the worksheet and generates calendar entries for each row of data in the worksheet. Rows with dates and times should be created as appointments. Rows with only a starting date should be created as all day events.

References

Type classification: this is a lesson resource.

← PowerPoint

Visual Basic for Applications

Access →