VBA to send email via outlook even when workbook is closed

Steve1208

New Member
Joined
Nov 10, 2017
Messages
28
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.
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,096
The workbook may be closed, but Excel should be open:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,841
Messages
5,598,388
Members
414,234
Latest member
grlevesq

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top