• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 493
  • Last Modified:

Upload from Excel to Outlook Calendar using VBA - Change colour

Hi all

I have some VBA code that I have taken from one of the forums that uploads a list of appointments from excel into MS Outlook Calendar, and played around with it to fit my needs.  However, In addition to the properties I have in the code below, I also want to add to colour to the appointment blocks showing in Outlook to distiguish different areas of the business.  Is this possible?  I tried adding 'Label' as a property in the 'appt' block below, but this was not recognised.

Also, is it possible to automate deletion of specified appointments if necessary

Thanks in advance
Sub ScheduleAppts()
Dim ol As New Outlook.Application
Dim ns As Outlook.Namespace
Dim olFolder As Outlook.MAPIFolder
Dim appt As Outlook.AppointmentItem
Dim myRecipient As Outlook.Recipient
NumberofRecipients = Range("L3").Value
For i = 2 To NumberofRecipients
test = Environ("username")
Set ns = ol.GetNamespace("MAPI")
Set ol = CreateObject("Outlook.Application")
Set ns = ol.GetNamespace("MAPI")
Set myRecipient = ns.CreateRecipient(test)
Set olFolder = ns.GetSharedDefaultFolder(myRecipient, olFolderCalendar)
Set appt = olFolder.Items.Add
iDate = Range("A" & i & "").Value
oDate = Range("B" & i & "").Value + 1
With appt
        .Start = iDate
        .End = oDate
        .Subject = Range("C" & i & "").Value
        .BusyStatus = olBusy
        .AllDayEvent = True
        .ReminderMinutesBeforeStart = 120
        .ReminderSet = True
        End With
Set ol = Nothing
Set ns = Nothing
Set appt = Nothing
Next i
End Sub

Open in new window

1 Solution
Boris PetrovicConsultantCommented:
Try this solution (all you have to do is adapt the code to call the functions from Excel): http://www.mrexcel.com/forum/showthread.php?t=79379
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now