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
' requires a reference to the Microsoft Outlook 8.0 Object Library
' 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
.Subject = "Subject for the new e-mail message" ' message subject
Set ToContact = .Recipients.Add("email@example.com") ' add a recipient
Set ToContact = .Recipients.Add("firstname.lastname@example.org") ' add a recipient
ToContact.Type = olCC ' set latest recipient as CC
Set ToContact = .Recipients.Add("email@example.com") ' 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)
Set ToContact = Nothing
Set olMailItem = Nothing
Set OLF = Nothing
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"
.Bold = True
.Size = 14
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%") & "..."
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
Application.Calculation = xlCalculationAutomatic
Set OLF = Nothing
ActiveWindow.FreezePanes = True
ActiveWorkbook.Saved = True
Application.StatusBar = False
Document last updated 2000-04-12 12:49:32
Ole P. from Norway wrote (2006-11-03 16:32:20 CET):
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):
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):
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):
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