Creating a new "All Day Event" In Outlook Using Excel 2010

[bloodmilksky]

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]

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]

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]

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]

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]

Try getting rid of the reverse slashes.

[bloodmilksky]

Hi yeah I tried with and without the \ and no luck :S

[GTO]

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]

BMS, was this resolved?