Upload from Excel to Outlook Calendar using VBA - Change colour

Posted on 2009-05-08
Last Modified: 2013-11-29
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

Question by:TED_UBB
1 Comment

Accepted Solution

zipa72 earned 50 total points
Comment Utility
Try this solution (all you have to do is adapt the code to call the functions from Excel):

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Know what services you can and cannot, should and should not combine on your server.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now