Send E-Mail From Outlook If Date Is Overdue In Excel

[excelbytes]

I have a spreadsheet that has a list of due dates in column B. In column D is where the project is marked complete. Column A has the project name. I would like an e-mail sent via Outlook each time the workbook is opened for each project where the date in column B is past today and column D is not marked "Complete". I found this code, but it doesn't seem to generating an e-mail:

Sub Send_Email()
Dim OutApp As Object
Dim OutMail As Object
Dim lLastRow As Long
Dim lRow As Long
Dim sSendTo As String
Dim sSendCC As String
Dim sSendBCC As String
Dim sSubject As String
Dim sTemp As String

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon

' Change the following as needed
sSendTo = "runninrep@outlook.com"
sSendCC = ""
sSendBCC = ""
sSubject = "Project(s) Past Due!"

lLastRow = Cells(Rows.Count, 1).End(xlUp).Row
For lRow = 2 To lLastRow
If Cells(lRow, 4) <> "COMPLETED" Then
If Cells(lRow, 2) <= Date Then
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = sSendTo
If sSendCC > "" Then .CC = sSendCC
If sSendBCC > "" Then .BCC = sSendBCC
.Subject = sSubject

sTemp = "Hello!" & vbCrLf & vbCrLf
sTemp = sTemp & "The due date has passed! "
sTemp = sTemp & "for this project:" & vbCrLf & vbCrLf
' Assumes project name is in column B
sTemp = sTemp & " " & Cells(lRow, 1)
sTemp = sTemp & "Please take the appropriate"
sTemp = sTemp & "action." & vbCrLf & vbCrLf
sTemp = sTemp & "Thank you!" & vbCrLf

.Body = sTemp
' Change the following to .Send if you want to
' send the message without reviewing first
.Send
End With
Set OutMail = Nothing

Cells(lRow, 6) = "S"
Cells(lRow, 7) = "E-mail sent on: " & Now()
End If
End If
Next lRow
Set OutApp = Nothing
End Sub

I also found this code:

Option Explicit
Sub SendEmail03()
Dim Date_Range As Range
Dim rng As Range
Set Date_Range = Range("B4:B203")
For Each rng In Date_Range
If rng.Value <= Date Then
Dim Subject, Send_From, Send_To, _
Cc, Bcc, Body As String
Dim Email_Obj, Single_Mail As Variant
Subject = "See Past Due Dates"
Send_From = "runninrep@outlook.com"
Send_To = "runninrep@outlook.com"
Body = "Check file for past due dates"
On Error GoTo debugs
Set Email_Obj = CreateObject("Outlook.Application")
Set Single_Mail = Email_Obj.CreateItem(0)
With Single_Mail
.Subject = Subject
.To = Send_To
.Body = Body
.send
End With
End If
Next
Exit Sub
debugs:
If Err.Description <> "" Then MsgBox Err.Description
End Sub


Sadly, I'm not sure if there is an issue with the code or if I just don't have the connection from Excel to Outlook set up properly. Do you know of any instructions to ensure this is done correctly?

Thanks in advance for any help you can provide.

 

[Micron]

Your first code works for me and the email body is:
Hello!

The due date has passed! for this project:

Project1Please take the appropriateaction.

Thank you!

So some work needed there? I would avoid using On Error Resume Next unless you are sure that every possible error could safely be ignored after that statement. I think an error handler would be better at least until you get this working as it might reveal what your issue is. You can go back to what you had before, but I think I'd keep the handler but maybe modify it to handle certain errors in suitable ways. I've combined Dim lines to shorten code height - that's just my style and you don't have to adapt. I used .Display to test. Your issue might be automated emails being prevented by IT policy. If it displays, that should at least tell you that the code works (or not).
NOTE - please enclose code within code tags (vba button on posting toolbar) to maintain indentation and readability.

Sub Send_Email()
Dim OutApp As Object, OutMail As Object
Dim lLastRow As Long, lRow As Long
Dim sSendTo As String, sSendCC As String, sSendBCC As String
Dim sSubject As String, sTemp As String

On Error GoTo errHandler
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon

' Change the following as needed
sSendTo = "myEmailAddressWasHere" '"runninrep@outlook.com"
sSendCC = ""
sSendBCC = ""
sSubject = "Project(s) Past Due!"

lLastRow = Cells(Rows.count, 1).End(xlUp).Row
For lRow = 2 To lLastRow
   If Cells(lRow, 4) <> "COMPLETED" Then
      If Cells(lRow, 2) <= Date Then
         Set OutMail = OutApp.CreateItem(0)
         'On Error Resume Next
         With OutMail
            .To = sSendTo
            If sSendCC > "" Then .CC = sSendCC
            If sSendBCC > "" Then .BCC = sSendBCC
               .Subject = sSubject
               sTemp = "Hello!" & vbCrLf & vbCrLf
               sTemp = sTemp & "The due date has passed! "
               sTemp = sTemp & "for this project:" & vbCrLf & vbCrLf
               ' Assumes project name is in column B
               sTemp = sTemp & " " & Cells(lRow, 1)
               sTemp = sTemp & "Please take the appropriate"
               sTemp = sTemp & "action." & vbCrLf & vbCrLf
               sTemp = sTemp & "Thank you!" & vbCrLf
               .Body = sTemp
               ' Change the following to .Send if you want to
               ' send the message without reviewing first
               '.Send
               .Display
           End With
           Set OutMail = Nothing
           Cells(lRow, 6) = "S"
           Cells(lRow, 7) = "E-mail sent on: " & Now()
       End If
   End If
Next lRow
exitHere:
Set OutApp = Nothing
Exit Sub

errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere

End Sub

 

[excelbytes]

Micron, thank you so much for your reply. I do know better regarding the tips you suggested and will make sure I do so in the future.

I Pasted your code as is into my workbook with the exception of my e-mail address. Is the code designed to run automatically when the file is opened? If so, nothing happened when I saved it, closed it and reopened it.

I then went into the VBA editor and ran the code manually. It spun for a while, then I got this error: "error code 2146959355 server execution failed"

That suggests to me that I just don't have the connection between Excel and Outlook set up properly? Any thoughts on this or how to ensure it is set up correctly?

 

[Micron]

Not intended to run on wb opening. That would require code to be in Workbook.Open event, which I don't think I'd do. Circumstances are not always likely to be what you want them to be when that happens. For one, is the email address you're using to test a valid one for an Outlook account that exists on the machine running Outlook? If not, I do believe you'll raise an error; not sure which one. Also, with Outlook not open, you could get prompts re what account to use with that code, or perhaps it is the reason for your error (different Office/Windows versions might raise different messages) and that you don't want. If yours was closed retry with it open or research vba send outlook email if outlook closed or similar, to see how to deal with that scenario.
One result:

Test if Outlook is open and open Outlook with VBA

At least you should now be able to see the problems that On Error Resume Next can cause with unproven code.

 

[excelbytes]

I'm almost there. I think part of my issue was that I had the web page for Outlook open but not the App of Outlook open. Once I did that, it created the e-mails but did not auto send them. Is the code supposed to send them or just create them? I need them to auto send.

Also, I created another module with this code to auto run the code when the file is opened, but it doesn't seem to run it. However, I can run it manually. Is there something I'm missing?:

Private Sub Workbook_Open()
Send_Email
End Sub

(Concerning your comment "NOTE - please enclose code within code tags (vba button on posting toolbar) to maintain indentation and readability", I don't know how to do that, can you guide me?)

 

[Micron]

You were trying to use that code with Outlook web app? Cannot.

Is the code supposed to send them or just create them? I need them to auto send.

Covered that in post 2? Supporting comments are in the code:

' Change the following to .Send if you want to
' send the message without reviewing first
'.Send
.Display

Is there something I'm missing?:

Yes; user written sheet module code is not visible to workbook level event(s) AFAIK. You would have to move code to a standard module and try calling it that way or move it into the workbook open event. Either way, the current version will not run because it will not know what sheet A2 refers to. You would have to fix that or perhaps create a wb macro that you can activate by keyboard key combination or sheet command button or userform command button or invoking the macro from the ribbon and leave the code on a sheet (assuming that's where you have it). Should work if that macro calls the sub by name. However if going the ribbon/macro route you might have to create a new macro then paste the code within it in order to get it recognized via the ribbon. In my case, I cannot find Send_Email in the macro list but if I type its full reference (including sheet index name) in the macro dialog I can get to it if I select Edit there. So it's there, but not connected for some reason.

Code tags: click the button, paste in code. Couldn't be simpler.

 

[Micron]

NOTE: I did some research and found that it is possible to call sheet code from workbook event:

Private Sub Workbook_Open()
Application.Run "FileNameHere.xlsm!Sheet15.Send_Email"
End Sub

Sheet15 is the project name in the sheets collection - not the name that you see on the sheet tab.

Google is my friend - make it yours too?
Don't forget to fix your email body text before sending out multiple emails.
EDIT - some things I don't get about that code - like making .CC equal some variable when the variable is an empty string.

 

[Micron]

Sheet15 is the project name in the sheets collection

Correction: in the picture example, it is the sheet codename.

As long as I had to come back here, I might as well pass on some thoughts about the whole idea.

Considerations for future:
- if not using bcc or cc, compress email header space by not showing empty fields
- what if user1 has 2 overdue, user2 has 3 overdue; send 5 emails when you could send 2?
if sticking with 5, body will never contain more than 1 project so would title be Project(s) Past Due! or singular of Projects?
- if recipient/cc/bcc info is in cells, pass these to sub instead
- why write S to sheet? If it means email sent, the date already makes that obvious.
- using htmlbody would allow font formatting
- "Email Sent" might as well be the column header and just insert the date?

 

[excelbytes]

Micron, thanks for all your help. It is working fine now. I just have one other question. For example, if there are 3 projects that are past due, it is sending out 4 e-mails: one for each project PLUS one that lists the worksheet name. I changed the worksheet name and it still sends out a 4th with whatever the worksheet name is. Can you see anything in the code that would cause this? I confirmed that the worksheet name is not hidden somewhere in column A.

 

[Micron]

Cannot see why based on post 2 code. If not exactly the same, post new version. Sample data might help but the only reason I could think of is that the sheet name is in the column but you've checked that out already. Also suggest you step through the code and make sure some other procedure you forgot about isn't getting called. Posting wb somewhere might be an option also if you can't find the problem.