Visual Basic for Applications/Outlook
Jump to navigation Jump to search
This lesson introduces Outlook macros.
Objectives and Skills
Objectives and skills for Outlook scripting include:
- Using Microsoft Outlook objects
- Microsoft: Set Reference to a Type Library
- Microsoft: Automating Outlook from a Visual Basic Application
- Microsoft: Folder Items
- Microsoft: MailItem Object
- Microsoft: MailItem Object Members
- Microsoft: How to Obtain the Email Address of a Recipient
'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
In these activities you will create macros which interact with Outlook messages.
- Outlook Inbox
- 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.
- Generate Email
- 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.
- 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.
- 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.
- Outlook Calendar
- Create an Excel macro that generates calendar entries based on data in the active worksheet.
- 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.
- 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.