Solved

Upload from Excel to Outlook Calendar using VBA - Change colour

Posted on 2009-05-08
1
475 Views
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)

myRecipient.Resolve

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

        .Save

        End With
 

Set ol = Nothing

Set ns = Nothing

Set appt = Nothing

Next i

End Sub

Open in new window

0
Comment
Question by:TED_UBB
1 Comment
 
LVL 6

Accepted Solution

by:
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): http://www.mrexcel.com/forum/showthread.php?t=79379
0

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