VBA coding

  • Thread starter Thread starter mwang
  • Start date Start date
M

mwang

I need to mail a reminder twice a day at intervals.this i have done as long
as my excel is open and the code which i had put in the code module,

Public RunWhen As Double
Public Const cRunIntervalhour = 5
Public Const cRunWhat = "mycode" ' the name of the procedure to run

Sub StartTimer()
RunWhen = Now + TimeSerial(cRunIntervalhour, 0, 0)
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
Schedule:=True
End Sub


Sub mycode()
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

With OutMail
.To = "..................." ' the ids to which i need to mail the
reminder
.CC = ""
.BCC = ""
.Subject = "Timesheet_remainder"
.HTMLBody = "Hi All" & "" & "Please finalize the TRS both in iPTS
and People Soft for this week" & " " & "Thank You"
.Send
End With
StartTimer
End Sub

this I have to run everyday each time opening my excel. I need suggestions
to make my macro run automatically when the excel is still closed.

also plz suggest me how to do this from my outlook
 
thanks 4 ur reply
but i dont want a macro trigger such as a reminder or any other event
handlers. but keeping my excel or keeping my outlook still how do i trigger
macro to be run weekly by itself
 
What is the difference between a timer event and an event raised by an
appointment item?

If you'd use the appointment, especially with such a long interval of once a
week, Outlook would handle the case for you that you turn off the machine
meantime.

Here's an example for an API timer that you can use in Outlook:
http://www.vboffice.net/sample.html?lang=en&mnu=2&smp=4&cmd=showitem

With that you'd have to handle the mentioned case yourself, except you could
ensure the computer never gets turned off. That handler would have to store
anywhere the next date for when it has to raise an event. That compare that
value at your chosen interval with the current time.

--
Best regards
Michael Bauer - MVP Outlook
Category Manager - Easily share your categories:
SAM - Automatically choose sending account, signature, and sent message
folder:
<http://www.vboffice.net/product.html?lang=en>


Am Fri, 21 May 2010 01:58:01 -0700 schrieb mwang:
 
Back
Top