VBA Code to Send a reminder from Excel to Outlook

Topic

New Reply

[WSflupsie]

I have written code to send a reminder to outlook based on dates on Col C  (I use format dd/mm/yyyy)

The problem is that I have dates and text In Col C

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, reminders are sent to the outlook calender

It would be appreciated if someone can kindly amend my code

Code:

 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 

 

 

• This topic was modified 5 years, 11 months ago by WSflupsie.

Reply | Quote

Replies

[WSflupsie]

I had uploaded sample data, but due to format, it was never uploaded

• This reply was modified 5 years, 11 months ago by WSflupsie. Reason: uploading sample data
• This reply was modified 5 years, 11 months ago by WSflupsie.

Reply | Quote

[zeddy]

Hi

How about something like this:

Sub Outloook_Reminders()
Sheets("renewals").Select	'start on THIS sheet

startRow = 2
r = Cells(Rows.Count, 1).End(xlUp).Row

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For r = startRow To endRow
zDate = cells(r, "C")		'fetch cell value
If IsDate(zDate) Then		'set a reminder
With CreateObject("Outlook.application").createitem(1)
On Error Resume Next
.Start = DateValue(zDate) + TimeValue(zDate)
.Duration = cells(r, "D") 	'duration; e.g. 30
.Subject = Cells(r, "E")        'subject text
.ReminderSet = True
.Save
End With
end if
Next
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~

End Sub

zeddy
Excel Frontal Cortex Unit

• This reply was modified 5 years, 11 months ago by zeddy.

1 user thanked author for this post.

WSflupsie

Reply | Quote

I have resolved the issue. Sheet BR3 was not set up

Reply | Quote

Hi

AHA! You worked it out yourself. Ignore my replies to your other post, which your reply in this unrelated post relates to.

zeddy
Excel Surgical MsgBox Strike Team

Reply | Quote

All you need is an “if” test enclosing the “with CreateObject” section.
Something like: if Range(“C” & ctr).value <> “” then

cheers, Paul

1 user thanked author for this post.

WSflupsie

Reply | Quote

Thanks for the help Guys

Reply | Quote