Results 1 to 10 of 10

Create Outlook appointment (as .ics file) from Excel

This is a discussion on Create Outlook appointment (as .ics file) from Excel within the Excel Questions forums, part of the Question Forums category; Hi Everyone, I want to be able to attach .ics appointment files to emails that I send out. The appointment ...

  1. #1
    Board Regular
    Join Date
    Dec 2004
    Location
    Devon
    Posts
    163

    Default Create Outlook appointment (as .ics file) from Excel

    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!

  2. #2
    Board Regular
    Join Date
    Oct 2007
    Posts
    2,995

    Default Re: Create Outlook appointment (as .ics file) from Excel

    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.

  3. #3
    Board Regular
    Join Date
    Dec 2004
    Location
    Devon
    Posts
    163

    Default Re: Create Outlook appointment (as .ics file) from Excel

    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...

  4. #4
    Board Regular
    Join Date
    Dec 2004
    Location
    Devon
    Posts
    163

    Default Re: Create Outlook appointment (as .ics file) from Excel

    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?

  5. #5
    Board Regular
    Join Date
    Dec 2004
    Location
    Devon
    Posts
    163

    Talking 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.

  6. #6
    New Member
    Join Date
    Feb 2010
    Posts
    2

    Default Re: Create Outlook appointment (as .ics file) from Excel

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

  7. #7
    Board Regular
    Join Date
    Dec 2004
    Location
    Devon
    Posts
    163

    Default Re: Create Outlook appointment (as .ics file) from Excel

    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.

  8. #8
    New Member
    Join Date
    Feb 2010
    Posts
    2

    Default Re: Create Outlook appointment (as .ics file) from Excel

    Quote Originally Posted by ttratl View Post
    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

  9. #9
    Board Regular
    Join Date
    Dec 2004
    Location
    Devon
    Posts
    163

    Default Re: Create Outlook appointment (as .ics file) from Excel

    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.

  10. #10
    New Member
    Join Date
    Sep 2011
    Posts
    1

    Default Re: Create Outlook appointment (as .ics file) from Excel

    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?

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com