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
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