hi,
im new to excel VBA and coding all together, i wrote a simple function to read dates from a spreadsheet, compare the date with today and generate an email and send if it matches.
it starts at cell R5, compares to today's date and supposed to move down to the next, i.e.. R6 and keep going. but i cant get it to go further than R5
heres what i got so far:
Code:
Function mailer()
'Application.OnTime timevalue("09:00:00"), "mailer"
Dim i As Long
Dim d As Date
Dim n As Range
Dim r As Range
Dim proj As String
Dim body As String
Dim email As String
Dim url As String
Dim subj As String
Set r = Cells(5, 18)
Set n = Range("d5")
d = Date
email = "[email protected]"
For i = 5 To 2000
With r = Cells(i, 18)
With n = Cells(i, 5)
Select Case (r.Value)
Case Is <> d
subj = n.Value
body = "Project " & n.Value & " completed on the " & r.Value
url = "mailto:" & email & "?" & "&subject=" & subj & "&" & "body=" & body
MsgBox url
ActiveWorkbook.FollowHyperlink (url)
End Select
End With
End With
Next i
Application.Wait (Now + timevalue("0:00:05"))
Application.SendKeys "%s"
End Function
Please Help! Comparing date and generating hyperlink
