ERLANDSEN DATA CONSULTING Excel & VBA Tips   Informasjon på norsk / Information in Norwegian

These pages are no longer updated and are only available for archive purposes.

Click here to visit the pages with updated information.

Control Outlook from Excel

The two example macros below demonstrates how you can send information to Outlook (e.g. sending an e-mail message) and how you can retrieve information from Outlook (e.g. retrieving a list av all messages in the Inbox).

Note! Read and edit the example code before you try to execute it in your own project!

' requires a reference to the Microsoft Outlook 8.0 Object Library
Sub SendAnEmailWithOutlook()
' creates and sends a new e-mail message with Outlook
Dim OLF As Outlook.MAPIFolder, olMailItem As Outlook.MailItem
Dim ToContact As Outlook.Recipient
    Set OLF = GetObject("", _
    Set olMailItem = OLF.Items.Add ' creates a new e-mail message
    With olMailItem
        .Subject = "Subject for the new e-mail message" ' message subject
        Set ToContact = .Recipients.Add("") ' add a recipient
        Set ToContact = .Recipients.Add("") ' add a recipient
        ToContact.Type = olCC ' set latest recipient as CC
        Set ToContact = .Recipients.Add("") ' add a recipient
        ToContact.Type = olBCC ' set latest recipient as BCC
        .Body = "This is the message text" & Chr(13) 
        ' the message text with a line break
        .Attachments.Add "C:\FolderName\Filename.txt", olByValue, , _
            "Attachment" ' insert attachment
'        .Attachments.Add "C:\FolderName\Filename.txt", olByReference, , _
             "Shortcut to Attachment" ' insert shortcut
'        .Attachments.Add "C:\FolderName\Filename.txt", olEmbeddedItem, , _
             "Embedded Attachment" ' embedded attachment
'        .Attachments.Add "C:\FolderName\Filename.txt", olOLE, , _
             "OLE Attachment" ' OLE attachment
        .OriginatorDeliveryReportRequested = True ' delivery confirmation
        .ReadReceiptRequested = True ' read confirmation
        '.Save ' saves the message for later editing
        .Send ' sends the e-mail message (puts it in the Outbox)
    End With
    Set ToContact = Nothing
    Set olMailItem = Nothing
    Set OLF = Nothing
End Sub

Sub ListAllItemsInInbox()
Dim OLF As Outlook.MAPIFolder, CurrUser As String
Dim EmailItemCount As Integer, i As Integer, EmailCount As Integer
    Application.ScreenUpdating = False
    Workbooks.Add ' create a new workbook
    ' add headings
    Cells(1, 1).Formula = "Subject"
    Cells(1, 2).Formula = "Recieved"
    Cells(1, 3).Formula = "Attachments"
    Cells(1, 4).Formula = "Read"
    With Range("A1:D1").Font
        .Bold = True
        .Size = 14
    End With
    Application.Calculation = xlCalculationManual
    Set OLF = GetObject("", _
    EmailItemCount = OLF.Items.Count
    i = 0: EmailCount = 0
    ' read e-mail information
    While i < EmailItemCount
        i = i + 1
        If i Mod 50 = 0 Then Application.StatusBar = "Reading e-mail messages " & _
            Format(i / EmailItemCount, "0%") & "..."
        With OLF.Items(i)
            EmailCount = EmailCount + 1
            Cells(EmailCount + 1, 1).Formula = .Subject
            Cells(EmailCount + 1, 2).Formula = Format(.ReceivedTime, " hh:mm")
            Cells(EmailCount + 1, 3).Formula = .Attachments.Count
            Cells(EmailCount + 1, 4).Formula = Not .UnRead
        End With
    Application.Calculation = xlCalculationAutomatic
    Set OLF = Nothing
    ActiveWindow.FreezePanes = True
    ActiveWorkbook.Saved = True
    Application.StatusBar = False
End Sub


Document last updated 2000-04-12 12:49:32      Printerfriendly version

User comments:
Ole P. from Norway wrote (2006-11-03 16:32:20 CET):
Re: outlookappointments
Take a look at this website for detailed Outlook programming tips:
Daniel A. Johansen from Drammen, Norway wrote (2006-11-03 10:35:44 CET):

I'm just wondering..

outlookappointments worksheet.. How is it possible to make this compatible with MS Outlook that is in Exchange ? :)
Ole P. from Norway wrote (2006-07-10 08:47:38 CET):
Re: Subfolders
You can access a subfolder in the Outlook Inbox like this:

Set OLF = GetObject("", "Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
Set SubFolder = OLF.Folders("InboxSubFolderName")

Alan from UK wrote (2006-07-06 14:51:53 CET):
How would I access a subfolder in Inbox?
Ole P. from Norway wrote (2006-05-29 11:15:29 CET):
Re: How would I transfer info to the Outlook Calendar
Declare a variable like this:
Dim olAppointmentItem As Outlook.AppointmentItem

Use something like this to get the calendar folder:
Set OLF = GetObject("", "Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderCalendar)

And then something like this to add a new appointment:
Set olAppointmentItem = OLF.Items.Add ' creates a new appointment item

Look up the AppointmentItem in the built-in VBA help to se details about setting the Start, Duration, Subject and Body properties.

Rob from Melbourne wrote (2006-05-27 05:33:19 CET):
How would I transfer info to the Outlook Calendar
i was hoping someone had a variation of this that transfers info from excel into Outlook Calendar using visula basic?
Greg Maloney from England wrote (2005-11-16 16:34:13 CET):
Changing the 'From' Data in an Outlook E-Mail
Using the example macro at the top of the page, is there any way of manipulating the properties of the sender of the E-Mail (i.e. removing/changing the data in the 'From' field of the e-mail) ?
Sketchit wrote (2005-11-16 00:27:46 CET):
Sub ListAllItemsInInbox()
When retrieving the Senders email address, I have 36 characters that are in front of the persons name like the following:
I would like to remove those characters and only store the persons name.

I know you can do this with the mid function in excel like this:
=MID(A12,37,100) This function starts with the 37th character and allow 100 characters so that only somebody would show.

How can I do this programmatically without using functions with
the below code?

Cells(EmailCount + 1, 1).Formula = .SenderEmailAddress

Thanks for your help

Sketchit wrote (2005-11-13 22:58:40 CET):
Re: Control Outlook from Excel
I found my error!
I really appreciate your and I really enjoy site!
Ole P. from Norway wrote (2005-11-13 11:47:55 CET):
Re: Control Outlook from Excel "Sub ListAllItemsInInbox()"
If you take a look at the MailItem object you will find that the property "SenderName" returns the name of the sender (From).
The property "SenderEmailAddress" will return the e-mail address.
If you want to filter the list by subject you will have to add a line like this after the line with "With OLF.Items(i)":
If .Subject Like "Test*" Then


Erlandsen Data Consulting   
Excel & VBA Tips   Copyright ©1999-2018    Ole P. Erlandsen   All rights reserved
E-mail Contact Address