Macro to Generate Reminder in Outlook Calendar

[howard]

I have code below to generate a reminder in Outlook Calendar, which generates a reminder to the microsoft outlook calendar

I need the code amended to check if the reminder is already in the calendar. If it is , then exit the sub or continue if not already in the calendar

Your assistance is most appreciated

 Sub SendReminderToOutlook()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim olApp As Outlook.Application
    Dim olApt As Outlook.AppointmentItem
    Dim reportName As String
    Dim dueDate As Date
    
    ' Set the worksheet containing data
    Set ws = ThisWorkbook.Sheets("Reports Outstanding")
    
    ' Set Outlook Application
    On Error Resume Next
    Set olApp = GetObject(, "Outlook.Application")
    On Error GoTo 0
    
    If olApp Is Nothing Then
        Set olApp = CreateObject("Outlook.Application")
    End If
    
    ' Find the last row with data in column A and D
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through the data and create reminders
    For i = 2 To lastRow
        reportName = ws.Cells(i, "A").Value
        dueDate = ws.Cells(i, "D").Value
        
        ' Check if due date is in the future
        If dueDate >= Date Then
            ' Create an appointment item for the due date
            Set olApt = olApp.CreateItem(olAppointmentItem)
            With olApt
                .Start = dueDate
                .End = dueDate
                .AllDayEvent = True
                .Subject = reportName
                .Location = "None" ' Replace with appropriate location
                .ReminderSet = True
                .ReminderMinutesBeforeStart = 30 ' Adjust the reminder time (in minutes) as needed
                .Body = "This is a reminder for the report: " & reportName
                .Save
            End With
        End If
    Next i
    
    ' Release Outlook objects
    Set olApt = Nothing
    Set olApp = Nothing
    
  
    
End Sub

 

[Micron]

You probably could examine every appointment under a particular account but it would need to have the exact subject value as what is in your sheet. If anyone altered the subject (or a value given to any of the parameters you'd check) in the sheet or the appointment itself you'll get a dupe. IMO, the best approach would be to use a flag column (helper column) in the sheet that you are looping over. That could be anything, but Now() might be more useful since it would tell you when the item was created. In your loop you'd check that the row/column has no value. If it does, you skip it - or do something else.

 

[howard]

Thanks for your valuable Input Micron. Will set up a flag column as per your suggestion

 

[Micron]

You're welcome. Post back here if you get stuck.