Loading
Excel.Tips.Net ExcelTips (Menu Interface)

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.

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
        Else
            myApt.BusyStatus = Cells(r, 5).Value
        End If
        If Cells(r, 6).Value > 0 Then
            myApt.ReminderSet = True
            myApt.ReminderMinutesBeforeStart = Cells(r, 6).Value
        Else
            myApt.ReminderSet = False
        End If
        myApt.Body = Cells(r, 7).Value
        myApt.Save
        r = r + 1
    Loop
End Sub

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:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

Dainis    05 Aug 2016, 04:25
Hi!
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?
Thanks!
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
Hi,

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
Hi,

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
Hi,

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?

Thanks,
D
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.

-Allen
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
Hi There

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

regards,
nev    05 Jan 2015, 11:00
Hi,

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?

Thanks
Mike E    03 Dec 2014, 08:11
Hi!

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?

Thanks!
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?

Thanks
Tonya
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
Great info.

TQI,
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.