Getting Excel Dates into Outlook's Calendar

by Allen Wyatt
(last updated May 3, 2018)


Kelly has a worksheet in which she has a table of dates used as closing dates for getting advertisements into the local Yellow Pages. She wants to import these dates into Outlook Calendar with a 72 hour reminder, but some of the dates keep changing to numbers. Kelly wonders how she can get the Excel dates into Outlook like she needs.

Working with Outlook is a bit "higher level" than your run-of-the-mill Excel macro because you need to understand not only how to access Excel data in the macro, but also how to manipulate Outlook data. Without knowing exactly what data you need to transfer from the worksheet to the Outlook appointment, let's examine a short scenario.

Let's assume that you have a worksheet that contains a series of rows, each of which represents a single appointment you want to create. Each appointment contains information in seven columns, as follows, from left to right:

  • Subject. Text that describes the event/appointment (for example, "Yellow Pages Reminder")
  • Location. Text that describes the location of the event, such as a meeting room or a conference call number (this is optional)
  • Start Date/Time. Enter the date and time the event should start using a standard Excel date format (you can display any way you like)
  • Duration. Integer that represents a number of minutes for the appointment
  • Busy Status. Integer that represents an optional value indicating if the time should show as Free (0), Tentative (1), Busy (2), or Out of Office (3)
  • Reminder Time. Integer that represents a number of minutes before the appointment that a reminder should pop-up (as in 4320 which is the number of minutes in 3 days)
  • Body. Text that describes any detail you might want to place in the body of the appointment

With this data in place, you can use a macro to loop through all the rows (starting with the second row, assuming the first row has headings) and create an appointment for each row.

Sub AddAppointments()
    ' Create the Outlook session
    Set myOutlook = CreateObject("Outlook.Application")

    ' Start at row 2
    r = 2

    Do Until Trim(Cells(r, 1).Value) = ""
        ' Create the AppointmentItem 
        Set myApt = myOutlook.createitem(1)
        ' Set the appointment properties
        myApt.Subject = Cells(r, 1).Value
        myApt.Location = Cells(r, 2).Value
        myApt.Start = Cells(r, 3).Value
        myApt.Duration = Cells(r, 4).Value
        ' If Busy Status is not specified, default to 2 (Busy)
        If Trim(Cells(r, 5).Value) = "" Then
            myApt.BusyStatus = 2
            myApt.BusyStatus = Cells(r, 5).Value
        End If
        If Cells(r, 6).Value > 0 Then
            myApt.ReminderSet = True
            myApt.ReminderMinutesBeforeStart = Cells(r, 6).Value
            myApt.ReminderSet = False
        End If
        myApt.Body = Cells(r, 7).Value
        r = r + 1
End Sub

The macro continues to loop through the rows until the Subject column is empty.


If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7349) applies to Microsoft Excel 97, 2000, 2002, and 2003.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...


Spell Checking Your Document

One of the final touches that many people perform is to check the spelling of their document. This can help improve the ...

Discover More

Displaying the Control Panel

The Control Panel is (or has been) the heart and soul of controlling Windows. How you display the control panel, however, ...

Discover More

Table Numbers are Skipped

What do you do if you add numbered captions to an element of your document (such as tables) and Word skips a number? ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!


FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is three less than 5?

2018-12-17 13:51:52


This worked well. I would like help making two modifications. 1) I would like to replace duplicates, rather than adding a new appointment each time I run the macro. 2) I would like to know how I can specify a calendar other than my main Outlook calendar. Thank you.

2018-06-12 11:54:11

Tony Yates

We use Office 365 / Outlook 2016 / Excel 2016. The macro worked a treat and placed appointments in my personal calendar.

Our company uses a shared calendar, how do we insert the appointments into the shared calendar, rather than my personal calendar?

My macro skills are almost non existent!!

2017-09-02 03:22:11


Great code! worked so easily.
Just wondering if there is a way to add them to non-default calendars?


2017-07-11 05:31:49


To stop it duplicating the 'Create the Outlook session code needs to be moved to after the Loop as below:

' Start at row 2
r = 2

' Create the Outlook session
Set myOutlook = CreateObject("Outlook.Application")

I've tested this and it works fine now.

Also added a little bit of code at the end after the Loop and before the End Sub to advise when it's complete.

MsgBox ("All appointments added.")

2017-06-26 15:25:02

Frances Stuart

How can I stop the macro from duplicating entries if they are already in Outlook?

2016-08-05 04:25:54


This is real good macro for my planing jobs.
But i also have question about duplicates: is there a way to stop the code producing duplicates?

2016-06-24 07:40:40

Matt P

How to create them in a non-default calendar ?

2016-02-11 06:46:56

Brian C

I also would like to know how to remove duplicate entries. Also Ive been getting entries in my calendar for every blank cell (Sat 30/12/1899). When I run the code all blank cells are entered by default to this date and its clogging up my memory. Can this also be coded out?

2016-01-28 01:28:53


I have the same question as Dave Bryant.
How to create them in a non-default calendar ?

2016-01-11 10:59:45

Dave Bryant


This is great and just what I'm looking for.

Is there a way to create the appointment in a calendar other than the default one?

Many thanks for your help

2015-07-15 12:45:34

Ben Hadfield


I think this is absolutely brilliant, and seems to be just what I have spent some time looking for!

However, I can't work out how to actually apply it on a click-by-click basis. I'm quite new to macros and coding and this is a bit beyond me which is a shame as it would be a godsend otherwise. Are you able to expand on how to actually create this? Many many thanks!

2015-07-15 08:59:36


How can I stop it from duplicating events if they are already in Outlook? I would like to keep a running list in the excel report but each time I add something new into the spreadsheet and press the macro button to run, it adds the previous events into Outlook over and over again.

2015-07-08 09:33:28



i'm looking to alter the value for start date and make it =TODAY()+5

Can you guide me of how to do this please?


2015-05-06 02:35:39


Any chance this could create multiple reminders? When I create events in Google for my Exchange account, it allows multiple reminders. Can this be done here too?

2015-04-21 10:34:04


Tonia: You will need to use VBA.


2015-04-21 09:21:07


Do you need VBA in order to do this or can it be done without?

2015-04-07 09:51:37


This was extremely helpful as a template. I was able to use it and manipulate what I needed to create a very efficient maintenance tracking schedule.

2015-02-17 07:52:21


Hi There

As per Nev's request, can I stop duplicate appointments coming up in Outlook?


2015-01-05 11:00:26



I jave this working a treat and have managed to append th subject with specific text.

I have two question if I may

1) is there a way to stop the code producing duplicates?

2) is there a way to set a fixed time for the appointment i.e. 09:00 and not 00:00 for 60 mins?


2014-12-03 08:11:30

Mike E


How can I get the macro instead of loop through the rows until the Subject column is empty.

To select a number of rows to read?


2014-10-17 12:08:56

Tonya Morris

I used this but receive a message Run-time error '429': ActiveX component can't create object
Do you know what causes this? Could it be because I'm using a Mac?


2014-05-23 23:32:06

Shano K

Thanks, I was able to create a calendar event using this macro. Like Esteban, I'd like to know how to select specific calendar. Also, how can I map attendees?

2014-05-19 12:16:03

Esteban Castiglioni

Hi! Great info!! But i was wondering, how i could save my new task or appointment to a specify calendar? One calendar that's shared with another users...
Thanks a lot

2013-11-05 08:24:38

Michael LENG

Great info.


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.