Email Notification

[TaskMaster]

Hi,

I'm wondering if anyone can help me. I have thrown together a simple spreadsheet that would list a series of tasks assigned to a person. When the task is overdue I'm wanting it to send an email reminder to that person. Unfortunately I get an error when using the following "There must be at least one name or contact group in the To, Cc, or Bcc box". Once I get to a stage where I can send emails, I would like if possible to have a dynamic body which lists the task(s) that are overdue for each person and when the due date is or by how many days its overdue.

Thanks in advance.

Sub SendEmailReminder()

Dim OutlookApp As Object
Dim OutlookMailItem As Object
Dim iCounter As Integer
Dim MailDest As String

Set OutlookApp = CreateObject("Outlook.application")
Set OutlookMailItem = Outlook.CreateItem(0)

With OutlookMailItem
MailDest = ""
For iCounter = 1 To WorksheetFunction.CountA(Columns(12))
If MailDest = "" And Cells(iCounter, 12).Offset(0, -5) = "OVERDUE" Then
MailDest = Cells(iCounter, 12).Value
ElseIf MailDest <> "" And Cells(iCounter, 12).Offset(0, -5) = "OVERDUE" Then
MailDest = MailDest & ";" & Cells(iCounter, 12).Value

End If

Next iCounter

.To = MailDest
.Subject = "Overdue Tasks"
.Body = "Please review the checklist and complete overdue tasks"
.Send

End With

SetOutlookItem = Nothing
SetOutlookApp = Nothing

End Sub

 

[Dan_W]

Have you checked to make sure that the value of MailDest is a valid string of email addresses?
Rather than automatically sending with the email with .Send, it's usually best to replace that with .Display while you're trying iron out any bugs in the code. If you displayed the the resulting email rather than send it, it might show you where the problem is when you manually go to send it.

 

[TaskMaster]

Hi, Dan apologies I thought I had attached the spreadsheet previously.

The display was a good idea, I can see that no email addresses are populated in the email. Is there something obvious that I have got wrong?

TASK	PRIORITY	ASSIGNED TO	PROGRESS	START	DUE	EST HOURS	STATUS	DAYS TO COMPLETE	DONE	COMMENTS	EMAIL
TASK 1 Title
Dashboard	Low	Test1	100%	08/10/2021	08/10/2021	1	COMPLETED	0	Y		Test1@outlook.com
Task 1.2	High	Test1	10%	02/10/2021	07/10/2021	5	OVERDUE	-89			Test1@outlook.com
Task 1.3	High	Test1	100%	03/10/2021	12/10/2021	5	COMPLETED	0	Y		Test1@outlook.com
Task 1.4	Medium	Test2	100%	04/10/2021	13/10/2021	9	COMPLETED	0	Y		Test2@outlook.com
TASK 2 Title	Medium	Test2	20%	04/10/2021	19/10/2021	8	OVERDUE	-77			Test2@outlook.com

 

[Dan_W]

I tried out your code, and stepped through it on a line-by-line basis. This approach to debugging is useful (and will save you headaches), because you can see how the values of variables change and you can start to see where things go wrong. You can do this by pressing F8 to execute the code (rather than F5). There are plenty of guides online on how to do go about doing this, and I'm certain it will save you plenty of time in future.

Anyway, what I learnt from stepping your code was this:

1. Cells(iCounter, 12).Offset(0, -5) - this references the wrong column. It's looking at the value of Estimated Hours, and not the status. This is easily solved by changing the -5 to -4.
2. After fixing that, your code still didn't fully work. It only returned Test1@outlook.com, and not both that and Test2@outlook.com, which I suspect was the intended result? The reason for that was the use of WorksheetFunction.CountA(Columns(12)) to get the upper limit of the loop combined with the use of 1 as the lower boundary. COUNTA tells you how many cells in a range have some content in them. In your spreadsheet, you've designated the range to be the entire column. That includes the header row and the empty row 2. So it's return 6 cells as having content, so the loop is looking at rows 1 to 6 (i.e,. the header row and the empty row 2 + the next 4). Which means the final row with Test2 falls out of the loop entirely. You can fix this by clarifying the lower and upper bounds of the loop. You may want to consider using a function to find the last row of a data set, rather than using COUNTA, on the off chance your column 12 has other blank cells in it.

I hope that makes sense, but let me know if I've only made things more confusing!

After making those corrections, the MailDest returned: Test1@outlook.com;Test2@outlook.com

 

[TaskMaster]

Hi Dan,

Appreciate you looking into this for me and identifying my errors. From this I came up with the following which brings back Test1@outlook.com;Test2@outlook.com as expected. Thank you for pointing me in the right direction!

Sub EmailReminder()

Application.DisplayAlerts = False

Dim apOutlook As Object

Dim aEmail As Object

Dim RangeAddresses As Range, RangeCell As Range, Assigned_To As String

Set apOutlook = CreateObject("Outlook.Application")

Set aEmail = apOutlook.CreateItem(0)
 

'find e-mails address's to send reminder

Set RangeAddresses = ThisWorkbook.Worksheets("Task Tracker").Range("L:L")

For Each RangeCell In RangeAddresses.Cells

If Cells(RangeCell.Row, "H").Value = "OVERDUE" Then

Assignee = Assignee & ";" & RangeCell.Value

End If

Next
 
Application.DisplayAlerts = True

'write the email

With aEmail

'Set Subject

.Subject = "Overdue Tasks"

'Set Body for mail

.Body = "Please review the checklist and complete overdue tasks"

'Set Recipient and send

.To = Assignee

.Display

End With

MsgBox ("Reminder Email Sent")

End Sub

 

[Dan_W]

That's great - I'm glad you got it working. Does it send the emails ok now?
Thank you for the update!

 

[TaskMaster]

Hi Dan,

Yes, I forgot to change it back from .Display to .Send but works perfectly!

 

[Dan_W]

Excellent! That's really good to hear. Have a great weekend!