Sending email at certain Time

  • Thread starter Thread starter Fester
  • Start date Start date
F

Fester

Is it possible to have Excel automatically send an email at a specified
time during the day. I have sales numbers that I want emailed to me at
say 12 and 3 pm.

Fester
 
Fester,

The code below will send an email to (e-mail address removed)

It will send the sales numbers from cells A1, A2, and A3, from sheet "Sales
Data"

To make it work, you must set a reference to Outlook from the VBE, and Excel
must always be running.

HTH,
Bernie
MS Excel MVP


Dim NextTime As Date

Sub StartIt()
If Time() < 0.5 Then
NextTime = Int(Now()) + 0.5
ElseIf Time() > 0.625 Then
NextTime = Int(Now()) + 1.5
Else
NextTime = Int(Now()) + 0.625
End If
Application.OnTime NextTime, "Update"
End Sub

Sub Update()
MailIt
If Time() > 0.625 Then
NextTime = Int(Now()) + 1.5
Else
NextTime = Int(Now()) + 0.625
End If
Application.OnTime NextTime, "Update"
End Sub

Sub StopIt()
Application.OnTime NextTime, "Update", schedule:=False
End Sub

Sub MailIt()
Dim ol As Object, myItem As Object
Set ol = CreateObject("outlook.application")
Set myItem = ol.CreateItem(olMailItem)
myItem.To = "(e-mail address removed)"
myItem.Subject = "Sales Number..."
myItem.Body = "Hello Kevin, " & Chr(13) & Chr(13)
myItem.Body = myItem.Body & "Sales data from Cell A1: " & _
Worksheets("Sales Data").Range("A1").Value & Chr(13)
myItem.Body = myItem.Body & "Sales data from Cell A2: " & _
Worksheets("Sales Data").Range("A2").Value & Chr(13)
myItem.Body = myItem.Body & "Sales data from Cell A3: " & _
Worksheets("Sales Data").Range("A3").Value & Chr(13) & Chr(13)
myItem.Body = myItem.Body & "Fester" & Chr(13) & Format(Now, "mm/dd/yy
hh:mm:ss")
myItem.Send
Set ol = Nothing
Set myItem = Nothing
End Sub
 
Aaarg - forgot that the code was written for and works with Outlook 2000 -
the security settings of later versions won't allow unattended emailing.

HTH,
Bernie
MS Excel MVP
 
Back
Top