Automatic Emails

[Waldin]

GoodMorning/Afternoon

I have a function on my Db that sends emails however i want to automate the entire process. So what do i need to do to get the email to send at 10:00 every morning automatically (what is the code to achieve this).

 

[pr2-eugin]

You need to use the FormTimer property to achieve this, however the Access application needs to be placed open if you wish to use this property.

 

[Waldin]

Thanks Eugin, so does this mean that the email wont execute unless access is open? so if i set the time at around 10:00 this would be fine right because many users will be signed in already seeing that work starts at 07:30

 

[pr2-eugin]

Yes, if the Access application is not shut down at this time (10.00) then you can automate the process.. All you need is make sure that one form is kept open (and maybe kept hidden) throughout the lifetime of the application, this form has no buttons or any controls. Then set the timer interval to be 120000 (that is 2 minutes, you can change this if you want 1000 is 1 second) in the OnTimer event of the Form have the code something like..

Private Sub Form_Timer()
    If TimeValue(Now) > CDate("10:00") Then 
        Call subToSendEmails
        Me.TimerInterval = 0
    End If
End Sub

However if there are more than one user using the database at the time and you wish to trigger this only once, you might need to device a strategy... maybe trigger the code only from a managers version of the DB.. think of this..

 

[Waldin]

This is just magnificent, could you please explain to me what the code is saying

 

[pr2-eugin]

Sure, as we have made it clear earlier the Timer Interval is set for two minutes, from the time the Form is opened every two minutes the Form_Timer() method is called/triggered.. So it checks if the current system time TimeValue(Now) is anywhere greater than 10 am, if so call the function to send email. Since we do not want emails to be sent every 2 minutes after 10 am you set the interval back to 0, thus it will not be called again..

 

[Waldin]

Thanks Eugin, Thanks

 

[pr2-eugin]

Most welcome Waldin..

 

[Waldin]

Good Morning Eugin what Function is "subToSendEmails" and is it my best option working with an automatic email?

 

[pr2-eugin]

That is the function you had for sending email replace "subToSendEmails" with the function you already have..

 

[Waldin]

i see, thanks

 

[Waldin]

could i please bother you with this specific task that i have, you seem quite comfortable with the whole automated email Subject and i got everything i need to put it in place (i think) but im toggling back and fo0rth between forms almost clueless

 

[Waldin]

Since we do not want emails to be sent every 2 minutes after 10 am you set the interval back to 0, thus it will not be called again..[/QUOTE]


when do i set the time interval to 0?

 

[pr2-eugin]

Once you have sent the email with the procedure you have in place you will set the Time Interval to 0 as in my code..

Private Sub Form_Timer()
    If TimeValue(Now) > CDate("10:00") Then 
        Call [B][COLOR=Blue]subToSendEmails[/COLOR][/B]
        [COLOR=Red][B]Me.TimerInterval = 0[/B][/COLOR]
    End If
End Sub

 

[Waldin]

what a bummer, so much for Automated hey.

thanks again Eugin.