Excel: Import Excel Info into Outlook

[ZZman]

I want to transfer information that's in an Excel spreadsheet into a calendar in outlook. I've written the code below to create new appointments but the appointments are created in my main calendar. How do I direct them to be written in another calendar named Schedule in my mailbox or into a calendar that is in a public folder. The preferred option would be a public folder with the following tree:

\\Public Folders\All Public Folders\Tech Schedule\

Here is the code that has been written:



Sub AddAppointments()


' Create the Outlook session

Set myOutlook = CreateObject("Outlook.Application")

' Start at row 2
r = 2

Do Until Trim(Cells(r, 1).Value) = ""
' Create the AppointmentItem
Set myApt = myOutlook.createitem(1)
' Set the appointment properties
myApt.Subject = Cells(r, 1).Value
myApt.Location = Cells(r, 2).Value
myApt.Start = Cells(r, 3).Value
myApt.Duration = Cells(r, 4).Value
' If Busy Status is not specified, default to 2 (Busy)
If Trim(Cells(r, 5).Value) = "" Then
myApt.BusyStatus = 2
Else
myApt.BusyStatus = Cells(r, 5).Value
End If
If Cells(r, 6).Value > 0 Then
myApt.ReminderSet = True
myApt.ReminderMinutesBeforeStart = Cells(r, 6).Value
Else
myApt.ReminderSet = False
End If
myApt.Body = Cells(r, 7).Value
myApt.Save
r = r + 1
Loop
End Sub

Thanks in advance for the help!

[westconn1]

code or highlight tags would be nice

Sub AddAppointments()
 
 
' Create the Outlook session
 
Set myOutlook = CreateObject("Outlook.Application")
 
' Start at row 2
r = 2
 
Do Until Trim(Cells(r, 1).Value) = ""
' Create the AppointmentItem
Set myApt = myOutlook.createitem(1)
' Set the appointment properties
myApt.Subject = Cells(r, 1).Value
myApt.Location = Cells(r, 2).Value
myApt.Start = Cells(r, 3).Value
myApt.Duration = Cells(r, 4).Value
' If Busy Status is not specified, default to 2 (Busy)
If Trim(Cells(r, 5).Value) = "" Then
myApt.BusyStatus = 2
Else
myApt.BusyStatus = Cells(r, 5).Value
End If
If Cells(r, 6).Value > 0 Then
myApt.ReminderSet = True
myApt.ReminderMinutesBeforeStart = Cells(r, 6).Value
Else
myApt.ReminderSet = False
End If
myApt.Body = Cells(r, 7).Value
myApt.Save
r = r + 1
Loop
End Sub

[ZZman]

I'm just starting out writing VBA so please be patient with me. Let me revise this to code that creates a single appointment. Need to direct creation of this appointment to a calendar that is in a public folder not my current mailbox calendar.

Sub AddAppointments()
' Create the Outlook session
Set myOutlook = CreateObject("Outlook.Application")
' Create the AppointmentItem
Set myApt = myOutlook.createitem(1)
' Set the appointment properties
myApt.Subject = "Matt"
myApt.Location = "Office"
myApt.Start = "4/27/2011  12:00:00 PM"
myApt.Duration = "30"
myApt.BusyStatus = 2
myApt.ReminderSet = True
myApt.ReminderMinutesBeforeStart = "0"
myApt.ReminderSet = False
myApt.Body = "Meeting With Boss"
myApt.Save
End Sub

I appreciate any assistance that you can provide.

[westconn1]

as i do not have outlook installed, i can not be sure how to get the correct folder object