Create Outlook appointment (as .ics file) from Excel

ttratl

Board Regular
Joined
Dec 21, 2004
Messages
168
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!
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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.
 
Upvote 0
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...
 
Upvote 0
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?
 
Upvote 0
Re: Create Outlook appointment (as .ics file) from Excel (Solved)

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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top