VBA ignore blank cells

Ninnut85

New Member
Joined
Apr 30, 2019
Messages
1
Here is my code to create outlook calendar reminders:

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?

thumbnail

Ea4hqxxJjfhNjsML9ocH7NEBIwdzriISlqKqIR8k3F1wbg


Ea4hqxxJjfhNjsML9ocH7NEBIwdzriISlqKqIR8k3F1wbg

Many thanks,
 
Last edited by a moderator:

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Forum statistics

Threads
1,215,051
Messages
6,122,871
Members
449,097
Latest member
dbomb1414

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top