PDA

View Full Version : Creating a new "All Day Event" In Outlook Using Excel 2010



bloodmilksky
05-23-2017, 02:44 AM
Hi Guys, I hope you are all well.

I was just wondering if anyone knows how to use VBA/Excel to Create a new all day event In A Specific Shared Calendar in outlook. The name of the calendar would be "UK Customer Services Calendar" and The Date Range would be Defined in Cells A1 And A2. Any Help Or Guidance would be great.

many thanks and all the best

BMS ^_^

GTO
05-24-2017, 04:09 AM
I think something like:

In a Standard Module:



Option Explicit

Public Sub CreateOutlookAppt()
Const olFolderCalendar = 9&
Const olAppointmentItem = 1&
' late-bound | early-bound
Dim OL As Object ' Outlook.Application
Dim olAppt As Object ' Outlook.AppointmentItem
Dim olNameSpc As Object ' Outlook.Namespace
Dim olCal As Object ' Outlook.Folder


On Error Resume Next
Set OL = GetObject(, "Outlook.Application")
On Error GoTo 0

If OL Is Nothing Then
MsgBox "You need to be logged into Outlook", vbInformation, "ERROR: Outlook not running"
Exit Sub
End If

Set olNameSpc = OL.GetNamespace("MAPI")
Set olCal = olNameSpc.GetDefaultFolder(olFolderCalendar).Folders.Item("UK Customer Services Calendar")
Set olAppt = olCal.Items.Add(olAppointmentItem)
With olAppt
.Categories = "Purple Category" '< whatever you have them saved as
.AllDayEvent = True
.Start = DateSerial(2017, 5, 25) + TimeSerial(0, 0, 0) '<---your cell reference
.Subject = "My Test"
'.Body = "Whatever text you want, if any"
.Save
End With

End Sub


Notes:

I have never figured out a good way to see if I am logged into Outlook. Sure, I can test if it's running, but not if I am signed-in and can send emails...

For adding an appointment, it appears I do not need to worry about this (as long as I am not sending notices...)


I would probably look for articles/threads/answers by Sue Mosher.


Hope that helps,

Mark

bloodmilksky
05-24-2017, 04:16 AM
Thank you so much for coming back to me. Is there a way that I can declare the calendar path?

\UK Public Folders\Customer Services\UK Customer Services Calendar

GTO
05-24-2017, 04:35 AM
NOT Tested:

I believe it's like:


Set olCal = olNameSpc.GetDefaultFolder(olFolderCalendar).Folders.Item("Subfolder1 name").Folders.Item("Subfolder2 name").Folders.Item("Your folder name")

bloodmilksky
05-24-2017, 05:59 AM
Hi Its come back to me saying

Runtime Error '-2147221233(8004010f)':

The Attempted Operation Failed. An Object could not be found


Set olCal = olNameSpc.GetDefaultFolder(olFolderCalendar).Folders.Item("\UK Public Folders").Folders.Item("\Customer Services").Folders.Item("\UK Customer Services Calendar")

GTO
05-24-2017, 06:25 AM
Try getting rid of the reverse slashes.

bloodmilksky
05-24-2017, 06:38 AM
Hi yeah I tried with and without the \ and no luck :S

GTO
05-24-2017, 10:04 AM
Try stepping through:



Set olCal = olNameSpc.GetDefaultFolder(olFolderCalendar)
Set olCal = olCal.Folders.Item("UK Public Folders")
Set olCal = olCal.Folders.Item("Customer Services")
Set olCal = olCal.Folders.Item("UK Customer Services Calendar")


See what line errors and if you have an errant space in the actual folder's name.

Mark

GTO
05-31-2017, 06:07 AM
BMS, was this resolved?