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("", _
        "Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
    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("name@domain.com") ' add a recipient
        Set ToContact = .Recipients.Add("name@company.com") ' add a recipient
        ToContact.Type = olCC ' set latest recipient as CC
        Set ToContact = .Recipients.Add("name@org.net") ' 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("", _
        "Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
    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, "dd.mm.yyyy hh:mm")
            Cells(EmailCount + 1, 3).Formula = .Attachments.Count
            Cells(EmailCount + 1, 4).Formula = Not .UnRead
        End With
    Wend
    Application.Calculation = xlCalculationAutomatic
    Set OLF = Nothing
    Columns("A:D").AutoFit
    Range("A2").Select
    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: http://www.outlookcode.com/

Daniel A. Johansen from Drammen, Norway wrote (2006-11-03 10:35:44 CET):

outlookappointments
Hey..

I'm just wondering..

outlookappointments worksheet.. How is it possible to make this compatible with MS Outlook that is in Exchange ?

pchard@gmail.com :)

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):

Subfolders
Hi,
How would I access a subfolder in Inbox?
Cheers

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
Hi,
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:
O=ABCDF/OU=XXXXXX/CN=RECIPIENTS/CN=somebody
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