VBA to send email via outlook even when workbook is closed

[Steve1208]

I am using Microsoft excel
I am using excel version 2013
Question relates to VBA

I have a code that I am going to use, which I will need to modify slightly to suit the situation I require (when I come to implimenting it).

My spreadsheet still needs a lot of work and this task I am referring to is just some forward planning, and of course that's if someone is kind enough to offer their expertise and advice.

I need the VBA code to perform the following task:

When a date on the spread sheet exceeds 6 months, I need the code to automatically instruct outlook to send an email to a specific email address.

Let me clarify further, for example:
Cell E5 contains a date "01/01/2018".
On the date of 01/06/2018 I need the code to instruct outlook to send an email to a specified email address with the information containing the contents of row 5 and informing the email contact that the information on this row is 6 months old.

However this worksheet may be closed. The computer will be switched on every day, but the sheet in question may not be in use for sometimes weeks at a time.

So finally my question is:
Can the Vba code perform the task I required when the workbook is closed?

What would I need to modify on the below code to ensure this will suit the task I require?


'Working in Office 2000-2016
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

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

strbody = "Hi there" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2" & vbNewLine & _
"This is line 3" & vbNewLine & _
"This is line 4"

On Error Resume Next
With OutMail
.To = "ron@debruin.nl"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = strbody
'You can add a file like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub


Any help or information would be greatly appreciated.

Please let me know if more information is required.

 

[Worf]

The workbook may be closed, but Excel should be open:

Dim runwhen As Date


Sub StartTimer()                        ' execute this one
runwhen = Now + TimeSerial(0, 0, 20)    ' will run in 20 seconds...
'runwhen = CDate([e5] + 180)            ' desired date
Application.OnTime EarliestTime:=runwhen, Procedure:="test", Schedule:=True
End Sub


Sub test()
MsgBox "mail code here"
End Sub