Application.OnTime

[pmidwest]

Can anyone help me... I have a form with drop down boxes for month, day, year, hour, and minute. I want a user to be able to set a time for the Application.OnTime also I want to use outlook to send a e-mail at the set time
.To = Form1.Totxt.Text
.CC = Form1.Cctxt.Text
.Subject = Form1.Subjetxt.Text
.Body = Form1.Bodytxt.Text
.Display
End ?

I don’t know if this will work but can someone give me the code that will... I need an e-mail sent at a certain time I posted earlier on this subject and didn’t get the answer I was looking for... I am new to vba and I would like to learn so if anyone can help me out here it would be greatly appreciated

Thank in advance

Paul

 

[StevePB]

Hi Paul,

The following code runs the sub sendAMail at the time specified, after checking that today is the right day. It might need a bit of fiddling with if your date format is not U.K. This should be put in the Workbook_Open event, so that it runs automatically when the workbook is opened:

''declarations

Public myDay
Public myMonth
Public myYear
Public myHour
Public myMin

Sub testDate()

myDay = Range("a2").Value
myMonth = Range("b2").Value
myYear = Range("c2").Value
myHour = Range("d2").Value
myMin = Range("e2").Value

''check that today is the day on which

''the message should be sent (dd/mm/yy)

If Left(Now, 2) = myDay And _
    Mid(Now, 4, 2) = myMonth And _
    Mid(Now, 7, 2) = myYear Then
    Call startOnTime
End If

End Sub

Sub startOnTime()

Dim myTime

''variable for time to send message

myTime = myHour & ":" & myMin & ":00"

''check whether time has passed

If Time > TimeValue(myTime) Then

''tell user time has passed

    MsgBox "Time Has Passed"
    Exit Sub
Else

''run sendAMail at the specified time

    Application.OnTime TimeValue(myTime), _
      "ThisWorkbook.sendAMail"

End If

End Sub

Sub sendAMail()

''this tells the time, but could run

''a procedure to send a mail message

MsgBox "The time is " & myMin & " minutes past " _
    & (myHour) & ".00"

End Sub

I hope this helps, SteveB.

 

[rmikesmith]

Paul,

If you are using Outlook you can specify a date/time before which the message will not be sent. Create a new mail message then click on Options and look for the checkbox titled "Do not send before" under Delivery Options. Enter a date & time in the edit box.

HTH
Mike