Can email be generated when Access not open?

[sjl]

Good morning,

My boss wants an email notification to be sent from our
Access database when today's date is 70-, 28- and 7-days prior to a certain Due Date (in our database). (This Due Date is calculated in a query and viewed in a Form)

I have read most of the SendObject threads to this Forum, but I still have 2 questions. Would something like SendObject automatically email a notification on its own, even if no one is in the database? I am concerned that one of the trigger dates (70, 28 or 7 days prior) will occur on a weekend and the email would not be triggered because no one is in the database.

My second question from reading the Threads is: where would I place the VBA code for this?
*******in the Form's property, or the (Calculated) Field's property?
*******on what event? (things like OnClick and OnFocus would not play here, but if I placed SendObject code for the On Timer event (say at 24 hours) on the Form's property, would this run even if database is not open?

sjl

 

[George Too]

I am not sure this can be done while access is not running. One thing I would try is looking at other automated solutions outside access. Another way would be having the system turn on automatically and run the database on start. Once your first form opens you can run your code from the "on open" event for that form.

Hope this triggers better ideas...

 

[sjl]

Another way would be having the system turn on automatically and run the database on start. Once your first form opens you can run your code from the "on open" event for that form.

George,
thanks for your input. I have a question--what are you referring to when you say "having the system turn on automatically (sounds great)....do you mean Access? Is it possible to get it to start automatically?

I found these steps on http://www.tek-tips.com/viewthread.cfm?qid=1455384&page=5

is this something like what you are referring to?

Step 1.
Create a macro that will run your report using the actions "OutputTo" and "Quit".
When you select the OutputTo action format, file-and-path, and encoding, etc., be sure to set AutoStart to "NO"
When you select the Quit action, choose "EXIT" from its options.

Step 2.
Create a batch file (anyname.bat) with wordpad in the same directory as your database file containing the following information. Be sure that the info on the lines beginning with "path" and "start" does not word-wrap to a second line(turn off word-wrap).

c: (or drive name where your application resides)

path = %PATH%;<drive name>:\windows\command;<drive name>:\<path to your database file's location>;<drive name>:\<path to your msaccess.exe file>

start /wait msaccess.exe "<drive name>:\<path to your database file's location>\<your database file name>" /x "<name of Macro that will run your report>"

exit

Step 3.
Using whichever form of scheduler you have, set it up to call the batch file as often as needed.

You can test the batch file by clicking on it in the windows directory. The scheduler will give you an option to manually test the file as well.
​

Is there any other way to go about running Access automatically each day?

thanks,
sjl

 

[George Too]

Well, by system I meant the pc that is running the database. If your db resides in a server then you don't even need to worry about shutting down/turning on the system, but I get from your post that that is not the case. You would have to do your homework on finding ways to turn the pc on automatically. Once you accomplish that then a quick dirty way to have access run is to create a link to the startup folder in your windows system.

As for the batch file you found, it will run once the system is up and running.

 

[sjl]

George,

Actually, our database does sit on the server. So, if I am understanding correctly, I can insert the VBA code in the database (e.g. OnTimer or OnOpen) and it will run (even if no one opens the database for the day)....

thanks,
sjl

 

[Darth Vodka]

Good morning,

My boss wants an email notification to be sent from our
Access database when today's date is 70-, 28- and 7-days prior to a certain Due Date (in our database). (This Due Date is calculated in a query and viewed in a Form)

why not open it?

get it to open automatically in the scheduled tasks...Tools>Control Panel>Scheduled tasks

give it a form that pops up when it opens

use the code to send an email

make sure you have Redemption/ClickYes

 

[DJkarl]

The simple answer to your questions is no. The code inside an access database won't and cannot be run unless Access is open and running. Now that doesn't mean a person has to open the database. Other's suggestions of using the scheduler and a macro / timer event are your best bet.

 

[George Too]

Exactly, have the database open automatically just as Darth Vodka suggests and then on the startup form place code that executes on open and that'll do the trick.

 

[sjl]

Darth, DJ and George,
Thanks for your input.

I did get the scheduler to open Access; however, since the workplace has a screen saver/server login id AND the database is password protected, I cannot get into my actual database. I did not see the option to key another pwd into the scheduler.

Is there a way that this obstacle can be solved?

Also, for the suggestion "then on the startup form place code that executes on open and that'll do the trick".....where is the startup form?

thanks again,
sjl

 

[georgedwilkinson]

Create a separate front end database or Excel sheet to run the code automatically on opening and then close the db/spreadsheet. Put the Access or Excel file name as a task in the windows "tasks" folder, with the windows user/password. I've done this many times and it works like a charm.

 

[sjl]

GeorgeDWilkinson

Thanks! This is really stretching me, so let me take a stab at what y'all proposed....

1st. Create a separate front end database or Excel sheet to run the code automatically.
I need to write VBA code within Excel that will: a) open Access, b) open my database c) open the form that has a VBA event that checks for any due dates and will email notifications as necessary d) close my dbase, Access, then Excel

2nd. I place this Excel file into the Scheduled Tasks (of Windows), using my windows id/pwd.

If these are the general steps, I'll go about figuring out the specifics (not at all sure about how to do those!).

thanks again,
sjl

 

[georgedwilkinson]

1st. Create a separate front end database or Excel sheet to run the code automatically.
I need to write VBA code within Excel that will: a) open Access, b) open my database c) open the form that has a VBA event that checks for any due dates and will email notifications as necessary d) close my dbase, Access, then Excel

2nd. I place this Excel file into the Scheduled Tasks (of Windows), using my windows id/pwd.

If these are the general steps, I'll go about figuring out the specifics (not at all sure about how to do those!).

Generally, yes. A couple of other suggestions:
1. You should probably use an administrative user name/pw in the task and make sure it runs on a server that has Office installed. If you get hit by a bus, quit, get promoted/transferred, or get fired, it will continue to function. Also, if the administrators change permissions on the folders/files in question.
2. If you use Excel, create a module with "Sub Autpen()" where the work is performed or the subroutine that does the work is called. There is another approach to this but I know this approach works for all versions of Excel.
3. If you use the Excel approach, the database will close automatically, but Excel won't. You have to explicitly tell Excel to close, which makes troubleshooting a real pain. Initially, I just put a message box (vbyesno) in my code that lets me break out before closing Excel.
4. The VBA code can live inside of Access or Excel, not just Excel. Also, you can reference VBA subroutines in Access from Excel, which makes the whole thing much easier.
5. Try Googling VBA Automation.

More tips if you need them. GTG now.

 

[sjl]

Redemption and auto way around Outlook pop-up window

why not open it?

get it to open automatically in the scheduled tasks...Tools>Control Panel>Scheduled tasks

give it a form that pops up when it opens

use the code to send an email

make sure you have Redemption/ClickYes

thanks darth,

Question: where does the Redemption/ClickYes come in? Is that VBA code, or does this appear in the properties window?

Maybe that is related to my next question: Since I want this whole process (of opening Access, sending out emails if a date difference is equal to a value, and closing Access) to run when no one is around, is there a workaround for those Outlook E-mail Security pop-ups (where you have to click Yes or No)? (One prompts to access email, the other prompts to send a message). Is there an automatic way around this?

thanks,
sjl

 

[Darth Vodka]

thanks darth,

Question: where does the Redemption/ClickYes come in? Is that VBA code, or does this appear in the properties window?

Maybe that is related to my next question: Since I want this whole process (of opening Access, sending out emails if a date difference is equal to a value, and closing Access) to run when no one is around, is there a workaround for those Outlook E-mail Security pop-ups (where you have to click Yes or No)? (One prompts to access email, the other prompts to send a message). Is there an automatic way around this?

thanks,
sjl

http://www.contextmagic.com/express-clickyes/free-version.htm

ClickYes and Redemption are small programs to bypass the old "Access is trying to talk to Outlook" dialogue box

 

[sjl]

Darth,
Muchas gracias!

sjl

 

[sjl]

Automating with Window's Scheduled Tasks....

Create a separate front end database or Excel sheet to run the code automatically on opening and then close the db/spreadsheet. Put the Access or Excel file name as a task in the windows "tasks" folder, with the windows user/password. I've done this many times and it works like a charm.

GeorgeD,

Why do you suggest (and used) a SEPARATE FRONT END database or Excel sheet to run the code within the main database?

Why not just place the Access database into the Scheduled Tasks with a command-line macro switch (e.g. \X mcrOpenEmailFormVBA) in the RUN portion of the Scheduler. The macro could tell a Form to open and the OnLoad code of this Form would then run (and could also close down the database.)

Just wondering if you tried this route and ran into obstacles, and therefore have placed the separate front end database into scheduler...not the database itself.

thanks much,
sjl

 

[boblarson]

Just wondering if you tried this route and ran into obstacles, and therefore have placed the separate front end database into scheduler...not the database itself.

Think about it for a second. Do you really want the database to do the same, exact things when someone else opens it too? Or do you want the database to kick off certain actions that you aren't going to want to have happen everytime the database gets opened. That's why the separate frontend.

 

[georgedwilkinson]

What Bob said. I've never tried it the way you suggest but would like to try. In the future, though, I imagine I would keep it separate. I have dozens of front ends for some of my DBs, all with a different purpose/for a different audience.

 

[sjl]

problems with Window's Scheduled Task?

What Bob said. I've never tried it the way you suggest but would like to try. In the future, though, I imagine I would keep it separate. I have dozens of front ends for some of my DBs, all with a different purpose/for a different audience.

George,
I am having problems getting all my code to run IF my desktop is locked, when using Window's Scheduled Task. What happens is that when I unlock my desktop, my database has actually opened but the email was NOT sent...until I unlock my computer?(!). I have been trying to debug this for days now. It all runs fine, but if I as much as pull up the Windows Security window (don't even lock my computer), the email is not sent/VBA is not run. Have you experienced this? Do you have any ideas what may be going on?

in the Run: portion of the Scheduled Tasks I have:
"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" H:\Regulatory.mdb /WRKGRP "H:\security.mdw" /User "xxxx" /pwd "yyyyyy" /X mcrOpenEmailNotifNIEHS

The User and Pwd are for my database--it is secured.

The macro listed above merely tells Access to open a Form.
The Form has an OnLoad event, which tells it to analyze today's date with a due date and DoCmd.SendObject if the dates are the same.

As I said, it all works perfectly, unless I have the Window Security window open, and/or lock my computer.

thanks much,
Sarah

(In Scheduled Tasks, I run it "Only if Logged ON" (although I have tried it without this checked)).

The VBA code I have is:

Private Sub Form_Load()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim objwShell As Object
Set db = CurrentDb
Set rst = db.OpenRecordset("qryEmail_Notif")

Dim dtOne As Date
Dim dtDue As Date
Dim strproject As String
Dim strprotocol As String
Dim strstudymgr As String

'Turn on error handling
On Error GoTo Cleanup

dtOne = Date 'todays date

'Activate ClickYes
Set objwShell = CreateObject("wscript.shell")
objwShell.Run ("""C:\Program Files\Express ClickYes\ClickYes.exe"" -activate")

Do While Not rst.EOF
dtDue = rst!dtmNPacketDue
'strto = rst!strSMName
strproject = rst!strBrochureName
strprotocol = rst!strNIHProtocolNum
strstudymgr = rst!strSMName

If dtDue > dtOne + 120 Then

DoCmd.SendObject , , , "xein@xxxxx.yyy.zzz", , , "Due Date Approaching", _
"The packet due date for " & strproject & ", " & strprotocol & ", is: " & vbCrLf & vbCrLf & dtDue _
& vbCrLf & "which is XX weeks from today. Please make a note of it." _
& vbCrLf & vbCrLf & "IRB coordinator" & vbCrLf & "Elizabeth O'xxxx", False

End If
rst.MoveNext
Loop

'stop clickyes
objwShell.Run ("""C:\Program Files\Express ClickYes\ClickYes.exe"" -stop")

Cleanup:
'Release all objects
rst.Close 'this just closes the rst
CurrentDb.Close 'this just closes Reg dbase, not access
Set rst = Nothing
Set db = Nothing
Set objwShell = Nothing
On Error GoTo 0

DoCmd.Quit
End Sub
​

 

[georgedwilkinson]

Are you using Vista? If so, turn off security.

I have successfully gotten tasks to run in Windows NT 4, Windows 2000, and Windows XP while the computer was locked or not even logged in.

Are you providing a user name and password to the Scheduled Task Wizard? In the Wizard it says: "If a password is not entered, scheduled tasks might not run." I vaguely remember having a problem like this in NT a long time ago and it was because a password had changed after the user created the task. We changed the user/pw to an administrative account that was created for this purpose and it worked for years.

Also, in the task properties, de-select "Run only if logged on". Having that selected will disable the task under the circumstances you mentioned. Also, make sure the "Enabled" flag is set.

Make sure in the task properties that the "idle" time is not a problem. If the computer must be idle for 24 hours b4 the task will start and the computer is never idle, it will never run.

Make sure "wake the computer to run this task" is set. If not, it may not run under the circumstances you cite.

Double check your permissions. Try the task on a simple text file before graduating to opening a file with complex logic. Just set the schedule for a minute from now and watch to see if the txt file opens.

Also, remove all that Access stuff in the "run" command. Just reference the Access file name where your Autoexec is located or the Excel file name where your Autpen is located.

I hope this helps. Sorry it took me so long to reply. Feel free to ping me back.

Good luck!