Can someone help I'm using the below macro but it will not recognise that I have blank rows.
I have due dates Column I some cells are blank because they are not due, I have subject in Column A, I have Body of Email in Column B & Column M
It works fine for the first 10 rows as they have dates but when there is a blank it just doesn't send any further emails. I think I need to change from range to cells but I have tried everything.
Sub emailTask()
Dim dateRow As Integer
With Sheets("Data1")
dateRow = .Range("I4").End(xlDown).Row
For i = 4 To dateRow
If .Range("I" & i).Value < Date And Not .Range("E" & i) = "SENT" Then
Call emailSue(i)
End If
Next i
End With
End Sub
Sub emailSue(i)
Dim OutApp As Object
Dim OutMail As Object
On Error GoTo errorKey
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.Subject = Sheets("Data1").Range("A" & i) & ""
.Body = Sheets("Data1").Range("B" & i) & " - " & Sheets("Data1").Range("L" & i) & ""
.Recipients.Add ("sue@safeplay.com.au")
.Recipients.ResolveAll
.Send 'direct send, if you want manual send, delete this row
End With
Sheets("Data1").Range("E" & i) = "SENT"
ContinueIt:
Set OutMail = Nothing
Set OutApp = Nothing
Exit Sub
errorKey:
MsgBox Err.Description
Resume ContinueIt
End Sub
Thanks in advance
http://www.mrexcel.com/forum/excel-q...ml#post3522523
http://www.ozgrid.com/forum/showthre...116#post675116
Bookmarks