Hi there, welcome to the board!
I moved your post to its own thread instead of piggy-backing on another.
NB: the thread referenced is
http://www.mrexcel.com/forum/showthread.php?t=474092
To answer your questions:
For the
No answer, there are workarounds. You could loop through a range of cells and add them to the calendar if you wish. I would recommend checking to see if they exist first, and also adding a boolean (True/False) variable to check if you opened a new instance of Outlook or not, that way if you did you can close it and clean up after yourself.
Please post back some details about your data structure, where your header rows are, where your data starts, where your data ends, where the information fields are (which columns are what, i.e. dates, start times, subject data, location, notes, etc), as much information as you can. This will help anybody who looks at your thread be able to supply you with a working solution. The more detailed you are, the quicker you will get a suitable solution.
Some things to note about the referenced thread. It uses Early Binding, which means you must select it from the VBE, go to
Tools, References, and pick
Microsoft Outlook xx.0 Object Library (where the xx is your version number, should only be one if you have one Office version installed). It also assumes you do not have to login when you open Outlook (i.e. a single profile on a local client). If you do need to, you'll need to specify that, but we can code around that. We can build off that code though.
This example assumes you have set a reference as described above, start date in col A, end date in col B, subject in col C, headers in row 1, data from row 2 to 20 (set the differences in the code, you should be able to see where it's commented below)...
Code:
Option Explicit
Sub CreateAppointmentsFromList()
Dim WB As Workbook
Dim WS As Worksheet
Dim rCell As Range
Dim rCheckCells As Range
Dim olApp As Outlook.Application
Dim olApt As Outlook.AppointmentItem
Dim NS As Outlook.Namespace
Dim olFolder As Outlook.Folder
Dim olItemsList As Outlook.Items
Dim i As Long
Dim bOLOpen As Boolean
Dim dStart As Date
Dim dEnd As Date
Dim strFilter As String
Dim iApptCount As Long
Const iReminderTime As Long = 5
'///////////////////////////////////////////////////////////////////////////
'Set Excel variables here
Set WB = ActiveWorkbook
Set WS = WB.ActiveSheet
'/// going to assume col A is Start Date, col B is End Date, col C is Subject
'/// range is only looking at col A, and offsets the other two cols
Set rCheckCells = WS.Range("A2:A20")
' ReDim arrExists(1 To 19) 'to match the size of the range to check (above)
'///////////////////////////////////////////////////////////////////////////
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
bOLOpen = True
If olApp Is Nothing Then
Set olApp = New Outlook.Application
bOLOpen = False
End If
On Error GoTo 0
Set NS = olApp.GetNamespace("MAPI")
Set olFolder = NS.GetDefaultFolder(olFolderCalendar)
For Each rCell In rCheckCells.Cells
dStart = CDate(Int(rCell.Value))
dEnd = CDate(Int(rCell.Offset(0, 1).Value))
strFilter = "[Start] = '" & Format(dStart, "m/d/yy h:mm AM/PM") & "' and " & "[End] = '"
strFilter = strFilter & Format(dEnd + 1, "m/d/yy h:mm AM/PM") & "' and [Subject] = '"
strFilter = strFilter & rCell.Offset(0, 2).Value & "'"
Set olItemsList = olFolder.Items.Restrict(strFilter)
iApptCount = olItemsList.Count
If iApptCount = 0 Then
Set olApt = olApp.CreateItem(olAppointmentItem)
olApt.Start = dStart
olApt.End = dEnd + 1
olApt.Subject = rCell.Offset(0, 2).Value
olApt.AllDayEvent = True
'olApt.Location = rCell.Offset(0, 3).Value '?
'olApt.Body = rCell.Offset(0, 4).Value '?
olApt.BusyStatus = olBusy
olApt.ReminderMinutesBeforeStart = iReminderTime
olApt.ReminderSet = True
olApt.Save
End If
Next rCell
If bOLOpen = False Then olApp.Quit
End Sub
I'm primarily interested if this code has the ability of updating/changing information that is already in an Outlook Calendar.
