Hello folks
I'm hoping this is a quick fix but I cannot get this to work and it's driving me mad. I'm adapting some code I found to automate sending e-mails from an Excel file. So far that works fine. I've then added a column to say 'Yes' once an e-mail has been sent so that the macro doesn't keep sending e-mails (FYI once this code is fully working I'll use Task Scheduler to run the procedure every day, hence the need to check off rows that have had an e-mail previously). This works fine also.
What I want to do now though is tell this macro to skip a row if an e-mail has been sent and move to the next row, and I cannot get this to work. What piece of code do I need to do this? Any help is very much appreciated, I've attached my Excel file and the code I'm using is below:
Sub Check_Tasks()
Dim lastRow As Long, r As Long
With Sheets("todo")
lastRow = Cells(Rows.Count, "B").End(xlUp).Row
For r = 5 To lastRow
If Cells(r, "I").Value = "Yes" Then 'How do I skip and go to next line that doesn't contain a 'Yes' in column I?
If Cells(r, "E").Value = Date Then Send_Outlook_Email "Task Reminder", .Cells(r, "H").Value, .Cells(r, "G").Value
Cells(r, "I").Value = "Yes"
Next r
End With
End Sub
Private Sub Send_Outlook_Email(subject As String, body As String, ToEmail As String)
Dim OutlookApp As Object 'Outlook.Application
Dim objMail As Object 'Outlook.MailItem
On Error Resume Next
Set OutlookApp = GetObject(, "Outlook.Application")
On Error GoTo 0
If OutlookApp Is Nothing Then Set OutlookApp = CreateObject("Outlook.Application")
Set objMail = OutlookApp.CreateItem(0) 'olMailItem
objMail.subject = subject
objMail.body = body
objMail.To = ToEmail
objMail.Send
End Sub
Thanks
Kenny
Bookmarks