Solved

Upload from Excel to Outlook Calendar using VBA - Change colour

Posted on 2009-05-08
1
476 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
ID: 24335168
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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
My experience with Windows 10 over a one year period and suggestions for smooth operation
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

895 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

13 Experts available now in Live!

Get 1:1 Help Now