Create Outlook appointment (as .ics file) from Excel

**ttratl** · Sep 7th, 2009, 03:52 AM

Hi Everyone,

I want to be able to attach .ics appointment files to emails that I send out. The appointment file needs to be generated from Excel, using data in certain cells.

I have investigated trying to have Excel generate the .ics format required into a text file, to save with the .ics extension, but a few issues made it all seem too difficult.

So – if I can generate an Outlook appointment, then just save it with the .ics extension – this should work.

Here’s some code I have found with the bits that need changing:

Code:

Sub SetAppt()

    Dim olApp As Outlook.Application
    Dim olApt As AppointmentItem

    Set olApp = New Outlook.Application
    Set olApt = olApp.CreateItem(olAppointmentItem)

    With olApt
        .Start = Date + 1 + TimeValue("09:00:00") ‘This should = C25 on current Worksheet (cell contents e.g. 22/09/2009)
        .End = .Start + TimeValue("07:30:00")
        .Subject = "Piano lesson" ‘This should = C24 on current Worksheet 
        .Location = "The teachers house" ‘This should = C26 on current Worksheet
        .Body = "Don't forget to take an apple for the teacher"
        .BusyStatus = olBusy
        .ReminderMinutesBeforeStart = 4320 
        .ReminderSet = True
        .Display
        '.Save
‘SaveAs “Outlook Reminder For Your Course.ics” on Desktop (and overwrite any existing one automatically)
    End With

    Set olApt = Nothing
    Set olApp = Nothing

End Sub

Tweaking required for
.Start =
.Subject =
.Location =
and saving it as an .ics file

Unfortunately – all too advanced for me to work out...

Any help appreciated!

**John_w** · Sep 7th, 2009, 10:50 AM

Try this:

Code:

Option Explicit

'Requires reference to Microsoft Outlook Object Library


Sub SetAppt()

    Dim olApp As Outlook.Application
    Dim olApt As AppointmentItem

    Set olApp = New Outlook.Application
    Set olApt = olApp.CreateItem(olAppointmentItem)

    With olApt
        .Start = Range("C25").Value
        .End = .Start + TimeValue("07:30:00")
        .Subject = Range("C24").Value
        .Location = Range("C26").Value
        .Body = "Don't forget to take an apple for the teacher"
        .BusyStatus = olBusy
        .ReminderMinutesBeforeStart = 4320
        .ReminderSet = True
        .Display
        
        'Save the iCalendar file in a known folder
        
        .SaveAs "C:\temp\excel\Outlook Appointment.ics"
        
        'Use Close to retain the new appointment within the Outlook Calendar, or Delete to delete it.
        'Both options keep the just-created .ics file
        
        .Close False
        '.Delete
    
    End With

    Set olApt = Nothing
    Set olApp = Nothing

End Sub

It isn't clear whether or not you want to keep the new appointment within the Outlook Calendar, so I have provided code for both options - see the comments.

Also, saving (the .SaveAs) causes an Outlook warning to be displayed. If you don't want this, the code can be easily tweaked to suppress this warning by using Outlook Redemption 'safe' objects.

**ttratl** · Sep 7th, 2009, 05:19 PM

Hey John - this looks interesting. Need to try it at work tomorrow. Doubt I'll be able to do anything with Redemption as our systems are locked so tight at work - but will investigate anyway.

Thanks very much. I'll report back...

**ttratl** · Sep 8th, 2009, 08:06 AM

Ok this seems to be working pretty well - apart from a very strange thing.

I cannot double click on the resulting .ics file on my Desktop. An error msg appears: "Cannot import vCalendar file".

This is odd because if I stop the macro, by commenting out the ".SaveAs" instruction. Then save the file manually..File > Save As > Outlook Appointment > filetype .ics to the Desktop. I can double click and open it normally.

Exactly the same thing occurs if I change the file extension to .vcs.

Any ideas on what's happening here?

**ttratl** · Sep 9th, 2009, 09:51 AM

Changed direction with this.

Found this code which I've altered a bit for my use:

Code:

Sub iCal()
  Open "E:\profile\Desktop\Outlook Reminder for your course.ics" For Output As 1
  Sheets("iCal").Select
  For Each ce In Range("A1:A" & Range("A65536").End(xlUp).Row)
       If ce = "yes" Then
           Print #1, ce.Offset(0, 1)
       End If
  Next ce
  Close #1
  Sheets("Invite").Select 'this code gets back to correct sheet - may not need this 
  ActiveCell.Offset(0, 1).Select 'this code gets back to correct cell - may not need this 
     
End Sub

This writes all the data in column B, where the cell next to it (in column A) has a "yes" entered, to an existing .ics file on my Desktop. It just overwrites the existing one each time.

On my "iCal" worksheet I've formatted Column B with standard ical formatting (eg BEGIN:VCALENDAR etc, etc), typed "yes" into all the relevant cells in column A and referenced out the cells that need to change (DESCRIPTION:, LOCATION:, DTSTART: etc...)

The resulting .ics file can be emailed as an attachment, opened by end user, Saved and Closed into their own calendar as an appointment.

Works perfectly.

I'm rubbish with VB - so no doubt this is the long way round!

Hope it helps someone - as I couldn't find anything like this across the 'Net.

**Bobbles** · Feb 8th, 2010, 04:09 AM

Hi,
Could you attach the Excel file or the ICS file ?
Thanks,
Rob

**ttratl** · Mar 6th, 2010, 07:04 AM

Hi Rob,

Not sure if you're still interested...

The Outlook email message is generated via VB in Excel, and code also attaches the .ics file from my Desktop to the message.

**Bobbles** · Mar 6th, 2010, 07:23 AM

Originally Posted by ttratl

Hi Rob,

Not sure if you're still interested...

The Outlook email message is generated via VB in Excel, and code also attaches the .ics file from my Desktop to the message.

Hi,
Still interested.
Could you attach a sample Excel file with that working ?
Excel is not my strong area, but a working example would help me get my teeth into it.

Thanks,
Rob

**ttratl** · Apr 1st, 2010, 04:41 PM

Hi Rob,
Not been around for a while - sorry. Will get a sample file asap - be after Easter now.
Not sure how to attach a file - may have to be the vb code - but I'll sort something out.

**abod** · Sep 6th, 2011, 12:44 AM

Hi! What I'm after is akin to this but I'm trying to find something for a user:
she has to send out multiple appointments ('meetings') - 1 per person - can be tens or hundreds.

At present, once she has a list of the people who will need appoitments she makes one calendar appointment per person, invites the person to the appointment.

What I envisage as a way to save time would be to have a single Excel Sheet with the time slots that she's making available.
She can put the names of the people against the time slots.
Then I'd like a way - vba or mailmerge - to create an appointment per line item that is either sent to the person, or they can be imported to her calendar and then sent to the people as invitations.

What would you suggest as the best way/s to solve this?