Here is my code to create outlook calendar reminders:
The issue I need help with is I need it so if it finds a blank cell in the 'start' and 'end' columns it ignores them and creates the appointments for the cells that do have dates.Basically, I have a formula in those boxes where it displays a date if another cell has been populated but will display "" (blank) if the other cell hasn't been populated.
How can I get this to work where it will ignore any cells with no dates in them? When the cells are blank, the macro just errors.
For example, in the screen shot, If cell E10 is empty because the formula has not found a date in the other sheet, how can I make it so this will ignore the empty cell and generate the appointments for the dates that are populated?
Many thanks,
Code:
Option Explicit
Public Sub CreateOutlookAppointments()
Sheets("Sheet1").Visible = True
Application.ScreenUpdating = False
Sheets("Sheet1").Select
On Error GoTo Err_Execute
Dim olApp As Outlook.Application
Dim olAppt As Outlook.AppointmentItem
Dim blnCreated As Boolean
Dim olNs As Outlook.Namespace
Dim CalFolder As Outlook.MAPIFolder
Dim i As Long
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 CalFolder = olNs.GetDefaultFolder(olFolderCalendar)
i = 2
Do Until Trim(Cells(i, 1).Value) = ""
Set olAppt = CalFolder.Items.Add(olAppointmentItem)
With olAppt
'Define calendar item properties
.Start = Cells(i, 5) + Cells(i, 6)
.End = Cells(i, 7) + Cells(i, 8)
.Subject = Cells(i, 1)
.Location = Cells(i, 2)
.Body = Cells(i, 3)
.BusyStatus = olBusy
.ReminderMinutesBeforeStart = Cells(i, 9)
.ReminderSet = True
.Categories = Cells(i, 4)
.Save
' For meetings or Group Calendars
' .Send
End With
i = i + 1
Loop
Set olAppt = Nothing
Set olApp = Nothing
Sheets("Sheet1").Visible = False
Application.ScreenUpdating = True
Exit Sub
Err_Execute:
MsgBox "An error occurred - Exporting items to Calendar."
End Sub
The issue I need help with is I need it so if it finds a blank cell in the 'start' and 'end' columns it ignores them and creates the appointments for the cells that do have dates.Basically, I have a formula in those boxes where it displays a date if another cell has been populated but will display "" (blank) if the other cell hasn't been populated.
How can I get this to work where it will ignore any cells with no dates in them? When the cells are blank, the macro just errors.
For example, in the screen shot, If cell E10 is empty because the formula has not found a date in the other sheet, how can I make it so this will ignore the empty cell and generate the appointments for the dates that are populated?
Many thanks,
Last edited by a moderator: