Auto emails from Access form (1 Viewer)

[gsrajan]

Can access send an auto email from the database at a specific day (Thursday ) and time ( 4 PM )? I have to send this email from the specific computer - without user intervention.

Thanks for your help.

 

[Minty]

Yes - but only if you can guarantee Access is running and the machine its running on is turned on.

 

[pbaldy]

I would create a db that sent the email when it opened, and run that db from Windows Scheduled Tasks.

 

[gsrajan]

Yes, Minty. Access will be running.

pbaldy: I do not want this to run from Scheduled Tasks.

Thanks for your replies.

 

[Minty]

How accurate does it need to be? is 16:00:30 okay or even 16:01:30 ?
Are you sending a attachment/report or is it just a reminder?

 

[gsrajan]

Thank you. It can be 16:00:30 or even 16:01:30 ?. Yes I will sending an attachment. A query.

 

[fvdb]

I would create a db that sent the email when it opened, and run that db from Windows Scheduled Tasks.

I should go this way, i do it to, so you always are sure it works. If somebody close the file then you don't know. If something go wrog you can add mail to error handling. If have an DB wich FE is used by many people but also one a server for task to update/import/mail reprts at specific times

Thank you. It can be 16:00:30 or even 16:01:30 ?. Yes I will sending an attachment. A query.

 

[Minty]

You will need a form that is open all the time on the database you are running this from (something either like a switchboard, or a hidden form that is automatically opened on start-up).

You will also need to determine if the event has already fired that day - I would probably create a new table called LocalVars with two field, datetime type called ThursRepDate. This will store the next date the report is due to run, and the time of day it should fire.

On the form that is open all the time use the OnTimer event set to do something like (this is untested and very simplified, but should give you an idea) every 120000 milli seconds (every two minutes - increase this as you see fit).

Dim NextThursday as Date
Dim sSql as String

On Error goto ErrorHand

If Now() > Dlookup("ThursRepDate","LocalVars") Then

	'Code goes here to send you email
	
	
	'Assuming your system is set so that weeks start on sunday (weekday 1):
	If Weekday(Date()) < 5 then
	     NextThursday = Date() + (5-Weekday(Date()))
	Else
	     NextThursday = Date() + (12-Weekday(Date())
	End If

	NextThursday = DateAdd("h", 16, NextThursday) ' Sets next thursday run time to 4:00pm
	
	sSql = "Update LocalVars Set ThursRepDate = #" & Format(NextThursday,"mm/dd/yyyy hh:mm" & "# ;"
	Currentdb.Execute sSql, dbSeeChanges
End If	

Exit Sub

On Rrror Goto 0
ErrorHand:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure AutoEmail Report - "

End Sub

I would still go the PBaldy recommended route my self though

 

[gsrajan]

Thank you very much to all. I will try this one. I do not know how to do what pbaldy said. Please let me know when time permits.

Have a good weekend to you all.

 

[pbaldy]

I do not know how to do what pbaldy said. Please let me know when time permits.

I create a new db that has only the objects/linked tables necessary for the task (in your case sending this email and attachment). I create an autoexec macro that calls a VBA function. That function does the desired task (sends your email), then closes the db. If a SendObject does everything you need, you could do it all from the autoexec macro.

This way I don't have code running in the background 24/7, don't have to worry about the db having to be left open, etc. I have a number of automated processes running this way. The computer is logged in but locked if I'm not on it, so it runs things like this as needed.

 

[fvdb]

i use batch files and vba so i can use windows task scheduler to perform task on the times i want

Batch:

start msaccess "c:\TD GBS\TD - Dienst GBS.accdb" /cmd "30_minuten"

VBA:

strCommand = Command()
If StrComp(strCommand, "30_minuten", vbBinaryCompare) = 0 Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "qry_Aeroscout_Temp_Update", acViewNormal, acReadOnly
DoCmd.OpenQuery "qry_Aerscout_Temp_Add", acViewNormal, acReadOnly
DoCmd.OpenQuery "qry_Aeroscout_hist_add_new_mac", acViewNormal, acReadOnly
DoCmd.OpenQuery "qry_Aeroscout_Hist_Add_Changed_mac", acViewNormal, acReadOnly

GoTo ENDSUB