Getting Excel Dates into Outlook's Calendar
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.
' 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
If Cells(r, 6).Value > 0 Then
myApt.ReminderSet = True
myApt.ReminderMinutesBeforeStart = Cells(r, 6).Value
myApt.ReminderSet = False
myApt.Body = Cells(r, 7).Value
r = r + 1
The macro continues to loop through the rows until the Subject column is empty.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7349) applies to Microsoft Excel 97, 2000, 2002, and 2003.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Leave your own comment:
Comments for this tip:
Dainis 05 Aug 2016, 04:25
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?
Matt P 24 Jun 2016, 07:40
How to create them in a non-default calendar ?
Brian C 11 Feb 2016, 06:46
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?
Nick 28 Jan 2016, 01:28
I have the same question as Dave Bryant.
How to create them in a non-default calendar ?
Dave Bryant 11 Jan 2016, 10:59
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
Ben Hadfield 15 Jul 2015, 12:45
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!
Sharron 15 Jul 2015, 08:59
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.
Daryl 08 Jul 2015, 09:33
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?
Lincoln 06 May 2015, 02:35
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?
awyatt 21 Apr 2015, 10:34
Tonia: You will need to use VBA.
Tonia 21 Apr 2015, 09:21
Do you need VBA in order to do this or can it be done without?
Laughingman 07 Apr 2015, 09:51
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.
ANTHONY CREAMER 17 Feb 2015, 07:52
As per Nev's request, can I stop duplicate appointments coming up in Outlook?
nev 05 Jan 2015, 11:00
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?
Mike E 03 Dec 2014, 08:11
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?
Tonya Morris 17 Oct 2014, 12:08
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?
Shano K 23 May 2014, 23:32
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?
Esteban Castiglioni 19 May 2014, 12:16
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
Michael LENG 05 Nov 2013, 08:24