Have excel send an email to Outlook if something is due

[ChrisME]

I have a tracking file that remains open at all times.
This tracking file has links to other files including status and due dates.

I am looking for a tool that would run every morning and check the due date column.
If it found todays date in the due date column, it would send an email to outlook with the name of the item

 

[Trevor G]

Welcome to the forum ChrisME. Im not sure if you have a solution, but can you provide a little more information:

What is the name of the worksheet that holds the information
Which column has the Due Date, will there be more than one row of data that has todays date in it
Which Column has the email address (or is there a set email address to email)
What must be added in the body of the email
Would you want a column to indicate that an email has been sent

 

[ChrisME]

Thank you for your reply. I hope you can help. Please find my reply below to your questions.

Welcome to the forum ChrisME. Im not sure if you have a solution, but can you provide a little more information:

What is the name of the worksheet that holds the information - Glove & Sock Development

Which column has the Due Date, will there be more than one row of data that has todays date in it - Column Q has the due date. The same due date (column Q) would be on multiple tabs for different product groups. For example, Tactical Gloves, Riding Gloves, Socks, etc.

Which Column has the email address (or is there a set email address to email) - There is only one email address that the system would be sending to.

What must be added in the body of the email - I would not need any body in the email, but I would like the subject of the email to contain points of detail tied to the due date. For example:
Re: Style # & Style Name (data pulled from column A & column B) "from" (data pulled from column C) "is due today."

Would you want a column to indicate that an email has been sent

- No. This would not be necessary

 

[Trevor G]

Try this out. Somethings you will need to change and I have commented them.

Sub sendReminder1A()
Dim OutApp As Object
Dim OutMail As Object
Dim strTo As String
Dim strSub As String, strBody As String
Dim myRange As Range
Dim Cell As Range
Dim iRow As Integer

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

Set myRange = Range("A2:A2500") 'Change the range
strTo = "email address" 'Change the email address
strSub = "Please check "

strBody = "Hi Please check the following:" & vbCrLf & vbCrLf

iRow = 2
For Each Cell In myRange
iRow = iRow + 1
If Cells(iRow, "Q").Value = Date Then
strBody = strBody & Cells(iRow, "A").Value & " " & Cells(iRow, "B").Value
strSub = strSub & Cells(iRow, "A").Value & " " & Cells(iRow, "B").Value
End If
Next Cell
strBody = strBody

With OutMail
.To = strTo
.Subject = strSub
.Body = strBody
.Display ' Change .Display to.Send once checked
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[ChrisME]

Thank you for your help with this.
- I did not see any wording in the code that you provided that told me it was sheet or file specific, so I copied a couple of rows from my tracking sheet to a new document and pasted what you provided into Visual Basic. I assume that is what I am supposed to use. (it is my very first time ever using Visual Basic it)
- I made the changes that you noted.
- I changed the date in the file to today. Nothing happened, so I looked through the menu of options and selected Run. I received a yellow arrow and highlighted text on line 1 Sub sendReminder1A ()

 

[Trevor G]

In VBA click in the routine (below the first line) and then try the run button.

 

[ChrisME]

Thank you for helping me with this little project.

So, I tried what you advised and it gave me a syntax error and some text turned red.
- it was telling me that something was incorrect with the information for myRange.
- I google'd myRange and found out that I needed to set myRange in Line 11 as ActiveSheet.Range and add quotes around the range ("A2:A50")
Then when I hit run, I received an email. - YAY!
- The email only stated:
Subject: Please Check
Body: Please check the following

It seems that the code failed to go back and pull the info from cells (columns A, B & C) that correspond to the date for why the message was sent.

In the attached image you will see the updates to line 11 for ActiveSheet.Range etc.

Now, if we can just get the code to pull the date from columns A, B & C.

 

[ChrisME]

Hi Trevor,

Were you still able to help me with the next function of this problem?

Thanks again,
Chris