VBA Code to generate Outlook Reminder

[howard]

I have written code to send a reminder to outlook based on a date on Col C

I have dates and text In Col C

https://www.dropbox.com/s/sh4uinn570nd3ds/VBA Code to Generate Outlook Reminders.xlsm?dl=0



I need the code amended so that where there is either no date or text, the reminder is not send to outlook. Where there is text, it must send a reminder to the outlook calender on 1 July 2019 (I use format dd/mm/yyyy)

 Sub Outloook_Reminders()
Sheets("renewals").Select
     Dim startRow As Long, endRow As Long, ctr As Long
   
     startRow = 2
     endRow = Cells(Rows.Count, 1).End(xlUp).Row
     
     For ctr = startRow To endRow
        With CreateObject("Outlook.application").createitem(1)
        On Error Resume Next
            .Start = DateValue(Range("C" & ctr)) + TimeValue(Range("C" & ctr))
            .Duration = CLng(Range("D" & ctr)) ' 30
            .Subject = CStr(Range("E" & ctr)) ' subject text
            .ReminderSet = True
            .Save
        End With
     Next
End Sub

It would be appreciated if someone can kindly amend my code

I have also posted on link below


https://www.excelforum.com/excel-programming-vba-macros/1281158-vba-code-outlook-reminder.html

 

[zoog25]

I don't know if this will work but try something like this

Sub Outloook_Reminders()
Sheets("renewals").Select
     Dim startRow As Long, endRow As Long, ctr As Long
   
     startRow = 2
     endRow = Cells(Rows.Count, 1).End(xlUp).Row
     
     For ctr = startRow To endRow
        With CreateObject("Outlook.application").createitem(1)
        On Error Resume Next
	If Not Cells(Ctr, "C").Value is Nothing then
            	.Start = DateValue(Range("C" & ctr)) + TimeValue(Range("C" & ctr))
            	.Duration = CLng(Range("D" & ctr)) ' 30
            	.Subject = CStr(Range("E" & ctr)) ' subject text
            	.ReminderSet = True
            	.Save
	End If
        End With
     Next
End Sub

This will only check to see if there is any information in column C. Let me know if this works, I use similar coding in my stuff to check.

 

[howard]

Thanks for your code , much appreciated