How do I get this code to stop emails of the cell next to it is marked complete

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

jz75455

Thread Starter
Joined
Jan 14, 2016
Messages
15
Please help if you can, I know very little about excel and I have a very complicated spreadsheet to build.

If I can pull this off, it will help me fight the coming layoffs.


Problem

I need excel to send automatic email when the spread sheet is opened to the supervisor every time a check in date hits and to keep emailing them everyday until they mark the cell next to the date as completed. I need to learn VB for this and I am running out of time!

Same thing for the shift manager complex manager and HR.


Am working on the small stuff but am struggling with the emails.

I really need some help and fast, I can repay the favor with translations, building basic spreadsheet or let me know what I can do to pay you back for helping. I really help please. I will give you my cell number in PM if needed.


If possible, I need to have this done by 3pm Monday 01/08/2016.

Spreadsheet update
 

Attachments

jz75455

Thread Starter
Joined
Jan 14, 2016
Messages
15
I found this code in an excel spreadsheet that does sort of what I want it to do but, I don't now how to apply it to what I want. I can change the Email notice in the code, but how to get it to send an email to a specific person on a spefic day and to keep sending them until it has been complted. How do I do that?

Excel Sheet that has the code attached.


Sub SendEmailUsingOutlook(Subject As String, ToEmailID As String, EmailBody As String)

Dim OlApp As New Outlook.Application
Dim myNameSp As Outlook.Namespace
Dim myInbox As Outlook.MAPIFolder
Dim myExplorer As Outlook.Explorer
Dim NewMail As Outlook.MailItem
Dim OutOpen As Boolean

' Check to see if there's an explorer window open
' If not then open up a new one
OutOpen = True
Set myExplorer = OlApp.ActiveExplorer
If TypeName(myExplorer) = "Nothing" Then
OutOpen = False
Set myNameSp = OlApp.GetNamespace("MAPI")
Set myInbox = myNameSp.GetDefaultFolder(olFolderInbox)
Set myExplorer = myInbox.GetExplorer
End If
'myExplorer.Display ' You don't have to show Outlook to use it

' Create a new mail message item.
Set NewMail = OlApp.CreateItem(olMailItem)
With NewMail
'.Display ' You don't have to show the e-mail to send it
.Subject = Subject
.To = ToEmailID
.Body = EmailBody

End With

NewMail.Send
If Not OutOpen Then OlApp.Quit

'Release memory.
Set OlApp = Nothing
Set myNameSp = Nothing
Set myInbox = Nothing
Set myExplorer = Nothing
Set NewMail = Nothing

End Sub


Sub auto_open()

Dim iRow
iRow = 4
Sheets("Sheet1").Activate

On Error Resume Next

If Sheet1.Label1.Caption <> VBA.Date Then
While Range("C" & iRow).Value <> ""

If Range("G" & iRow).Value <> "Completed" And Range("H" & iRow).Value < VBA.Date Then

ToName = Range("E" & iRow).Value
ToEmailID = Range("F" & iRow).Value
Subject = "Task Not Completed"
EmailBody = "Hi " & ToName & "," & vbNewLine & vbNewLine

EmailBody = EmailBody & "Following Task is still not Completed. The task completion date was on :" _
& Range("H" & iRow).Value & vbNewLine & vbNewLine
EmailBody = EmailBody & Range("C" & iRow).Value & " : " & Range("D" & iRow).Value & vbNewLine & vbNewLine
EmailBody = EmailBody & "Regards," & vbNewLine

Call SendEmailUsingOutlook(Subject, ToEmailID, EmailBody)


End If

iRow = iRow + 1

Wend
Sheet1.Label1.Caption = CStr(VBA.Date)
ThisWorkbook.Save
ThisWorkbook.Close

Else

End If
End Sub
 

Attachments

jz75455

Thread Starter
Joined
Jan 14, 2016
Messages
15
Figured out this gem How to count text in a row of cells.

=COUNTIF(AI3:AI400,"*Completed*")
 

jz75455

Thread Starter
Joined
Jan 14, 2016
Messages
15
Used in sheet

Use conditional formatting and add two rules

for 2007, 2010 or 2013 excel version
Conditional Formatting

Highlight applicable range >>

J6


Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:


=K6 = "Incomplete"


Format… [Number, Font, Border, Fill] > choose fill RED
choose the format you would like to apply when the condition is true
OK >> OK

Then repeat above with a new rule
=K6 = "complete"
and use fill Green
 

jz75455

Thread Starter
Joined
Jan 14, 2016
Messages
15
Any advise on how to get the sheet to send emails on multiple dates and to continue to send them everyday until the cell next to it is checked completed?
 

jz75455

Thread Starter
Joined
Jan 14, 2016
Messages
15
I found this code, I can make it work but how do I get it to stop emails of the cell next to it is marked a completed or to keep sending emails until marked completed?



Sub datesexcelvba()
Dim myApp As Outlook.Application, mymail As Outlook.MailItem
Dim mydate1 As Date
Dim mydate2 As Long
Dim datetoday1 As Date
Dim datetoday2 As Long

Dim x As Long
lastrow = Sheets(“Sheet1”).Cells(Rows.Count, 1).End(xlUp).Row
For x = 2 To lastrow

mydate1 = Cells(x, 6).Value
mydate2 = mydate1

Cells(x, 9).Value = mydate2

datetoday1 = Date
datetoday2 = datetoday1

Cells(x, 10).Value = datetoday2

If mydate2 – datetoday2 = 3 Then

Set myApp = New Outlook.Application
Set mymail = myApp.CreateItem(olMailItem)
mymail.To = Cells(x, 5).Value

With mymail
.Subject = “Payment Reminder”
.Body = “Your credit card payment is due.” & vbCrLf & “Kindly ignore if already paid.” & vbCrLf & “Dinesh Takyar”
.Display
‘.send
End With

Cells(x, 7) = “Yes”
Cells(x, 7).Interior.ColorIndex = 3
Cells(x, 7).Font.ColorIndex = 2
Cells(x, 7).Font.Bold = True
Cells(x, 8).Value = mydate2 – datetoday2
End If
Next
Set myApp = Nothing
Set mymail = Nothing
End Sub
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top