Send email depending on cell value

[ramonjr17]

Hi

I have a spreadsheet that contains:

A mobile phone serial number
B email address of user it is assigned to
C return date (Entered manually)
D Due/Overdue (looks at column C and compares it to current date to determine if overdue)
E Returned(Y/N)

When a mobile phone is returned, I manually enter "YES" within cell Column E, when we do this, the row becomes hidden using a button assigned with a macro.

What i would like to do is - send an email to everyone who has a mobile phone that is "Overdue (within D)" and when the "Returned (within E)" column is blank - but only unhidden rows.

Not automatic, run macro manually.

this is what i was using ( got from somewhere ) and tried to muck around with it, but in the end i couldnt get it going - it only looks at the last item and it's not looking at any variables.. but i thought i'd paste it here so you can see what i was workin with..

Sub Mail_Outlook()
Dim OutApp As Object
Dim OutMail As Object
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

If Cells(LastRow, 1).Value <> "" Then
MailTo = Cells(LastRow, 1).Offset(0, 1).Value

MailSubject = Cells(LastRow, 1).Offset(0, 1).Value & " - Mobile overdue notification"
MailBody = "Hi," & vbNewLine & vbNewLine & _
"This is a friendly reminder that Laptop" & Cells(LastRow, 1).Value & " is overdue, please return the mobile to when convenient." & _
vbNewLine & vbNewLine & "Regards," & vbNewLine & "Ramon"

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(o)
With OutMail
.Subject = MailSubject
.To = MailTo
.body = MailBody
'.Attachments.Add FileNme
.Display
'.Send
End With

Set OutMail = Nothing
Set OutApp = Nothing
End If

End Sub



appreciate your time and effort.

cheers

 

[Danerida]

This works:

Sub Mail_Outlook()
Dim OutApp As Object, OutMail As Object
Dim MailTo As String, MailSubject As String, MailBody As String, c
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row


For Each c In Cells(1, 4).Resize(LastRow)
    If c.Value = "Overdue" And c.Offset(0, 1).Value <> "YES" Then
        MailTo = c.Offset(0, -2).Value
        MailSubject = c.Offset(0, -3).Value & " - Mobile overdue notification"
        MailBody = "Hi," & vbNewLine & vbNewLine & _
            "This is a friendly reminder that Laptop " & c.Offset(0, -3).Value & _
            " is overdue, please return the mobile to when convenient." _
            & vbNewLine & vbNewLine & "Regards," & vbNewLine & "Ramon"


Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(o)
With OutMail
    .Subject = MailSubject
    .To = MailTo
    .body = MailBody
    '.Attachments.Add FileNme
    .Display
    '.Send
End With


Set OutMail = Nothing
Set OutApp = Nothing
End If
Next c

End Sub

 

[ztodd]

Time to learn some vba Look up the help or do a web search on how to use a loop in vba. You'll want to declare a "range" type variable and set that to either the first row or the last row, then loop to the other end setting your range variable up or down by one each time. You can use the offset property of the range type to move it up or down. The Offset property can also help you look at the columns you need to look at in the row you're in.

Have some patience with yourself as you learn- don't expect people to do your coding for you- (Not everyone will be as nice as Danerida every time. You need to learn these things for yourself.)

Expect that it will take time to figure it out- research, trial and error. That's how we all do it. It takes time.