Excel 2010 vba to post appts to different outlook calendars at the same time

[BigDawg15]

Hello,

Great site. I have learned a lot from browsing through here, but obviously not enough.

Attached is an xl workbook that works to add appoinments to different calendars if I manually change the number in the code. What I would like to do if possible is modify the code to use the calendar name or number listed in the worksheet to add the appoinments to different calendars all in one go.

I hope that is clear enough. Thank you in advance for your assistance.

BigDawg15

 

[Diane Poremsky]

Re: Excel 2010 vba to post appts to different outlook calendars at the same ti

Are the folders at the same level as the calendar or subfolders? If they are all at the same level, you can pass the folder name as a variable.

Set Appointments = olNs.GetDefaultFolder(olFolderCalendar).Folders(foldernamevariable)

 

[Diane Poremsky]

Re: Excel 2010 vba to post appts to different outlook calendars at the same ti

basic change:

dim arrCal as variant
arrCal = Range("A2").Value

Set MyFolder1 = Appointments.Folders(arrCal)

i'm not totally sure of the code needed to get the correct row. plus it dies on the appt creation.

 

[BigDawg15]

Re: Excel 2010 vba to post appts to different outlook calendars at the same ti

Are the folders at the same level as the calendar or subfolders? If they are all at the same level, you can pass the folder name as a variable.

Set Appointments = olNs.GetDefaultFolder(olFolderCalendar).Folders(foldernamevariable)

Diane,

Thank you for a reply. Yes they are at the same level as the calendar. When you say pass the folder name as a variable, I'm assuming I would do this for each calendar but I'm not sure how to pass each one to the row for each item?

Any example would be appreciated. Also, I have read a bit through different forums, would it be easier to assign everything to the main calendar and then move it to the designated calendar with code?

I am just starting out with Outlook and still trying to get my hands around default and non-default calendars.

Thank you for your time and assistance. You do a great job!

BigDawg15

 

[Diane Poremsky]

Re: Excel 2010 vba to post appts to different outlook calendars at the same ti

This is messy code but it works.

Option Explicit 
 
Public Sub ExportToCalendar1()
  Sheets("Sheet1").Select
   On Error GoTo Err_Execute
 
   Dim olApp As Outlook.Application
   Dim olApt As Outlook.AppointmentItem
   Dim blnCreated As Boolean
   Dim olNs As Outlook.Namespace
   Dim Folder As Outlook.MAPIFolder
   Dim Appointments As Outlook.MAPIFolder
   Dim MyFolder1 As Outlook.MAPIFolder
   Dim arrCal As String
 
   Dim range() As Variant, i As Long
 
  ' range = Range("A2", Cells(Rows.Count, "K").End(xlUp)).Value
   On Error Resume Next
   Set olApp = Outlook.Application
 
   If olApp Is Nothing Then
       Set olApp = Outlook.Application
        blnCreated = True
       Err.Clear
   Else
       blnCreated = False
   End If
 
   On Error GoTo 0
 
   Set olNs = olApp.GetNamespace("MAPI")
   Set Appointments = olNs.GetDefaultFolder(olFolderCalendar)
     
     i = 2
       Do Until Trim(Cells(i, "A").Value) = ""
          arrCal = Cells(i, "A").Value 
 
Set MyFolder1 = Appointments.Folders(arrCal)
   Set olApt = MyFolder1.Items.Add(olAppointmentItem)
      
   MsgBox MyFolder1, vbOKCancel, "Folder Name"
   With olApt
 
   'Define calendar item properties
       .Start = Cells(i, 6) + Cells(i, 7)     '+ TimeValue("9:00:00")
       .End = Cells(i, 8) + Cells(i, 9)       '+TimeValue("10:00:00")
       .Subject = Cells(i, 2)
       .Location = Cells(i, 3)
       .Body = Cells(i, 4)
      '.Body = "Test Body"
     '  .BusyStatus = olBusy
      ' .ReminderMinutesBeforeStart = range(i, 10)
       .ReminderSet = True
       .Categories = Cells(i, "E").Value
       .Save
 
   End With
             
       i = i + 1
       Loop
 
   Set olApt = Nothing
   Set olApp = Nothing
 
   Exit Sub
  
 
Err_Execute:
   MsgBox "An error occurred - Exporting items to Calendar."
  
 
End Sub

 

[BigDawg15]

Re: Excel 2010 vba to post appts to different outlook calendars at the same ti

Diane,

Thank you so much. Works as expected.

I very much appreciate your assistance.

Have a great rest of your weekend.

Regards,

BigDawg15

 

[BigDawg15]

Re: Excel 2010 vba to post appts to different outlook calendars at the same ti

Sorry,

I have played with the code a bit and I just have to say Thank you, Thank you, Thank you!

I am very excited to get this code working and be able to put it to use.

Thank you again.

BigDawg15