Results 1 to 8 of 8

Thread: Automatic Email

  1. #1
    Join Date
    Oct 2003
    Posts
    43

    Question Unanswered: Automatic Email

    OK, I have a question for all of you. Is it possible to have an email sent from access with X info from what ever form, out to a person when a given date has passed without ever opening access or the form? If so what event will this be under? If you guys have any sites to look at on this that would be great because I am just looking for a start on it on the event type I am not worried about the coding for the date and all.



    Thank you for your time and answers.

    Billy

  2. #2
    Join Date
    Dec 2003
    Posts
    172
    Hi Billy,

    Depends on what you mean by not having Access open. If you are planning on using Access objects, it is not possible to avoid the Access object library and some sort of runtime to use it.

    However, you have alternatives to actually opening the MDB file:

    If you use the Access runtime version you can run Access objects without having to have the Access product and license installed on the user's machine.

    If you use automation, you can call Access (and any of its forms, reports, macros, etc) from another application using VBA such as from Excel.

    And you can always use VB to create a standalone executable that would run in the system tray (for example, like your anti-virus program runs in the background to watch for viruses) and this could execute Access objects via ADO, DAO, or the Access automation object.

    For example, I have an application I wrote for a client that runs a nightly update routine from VB in the system tray. Once a night, keyed to the passing of midnight, it opens up an automation session with Access and runs some queries on it to download data from a SQL Server database.

    Access does have timer controls on the form that you can use and you can also add a 3rd party component with a timer control on it but again you would have to run Access at some point.

    Maybe you could explain why you are trying to run an Access form without Access? Is it a security issue?

    I was also thinking that maybe you have a database containing emails in it and want to mass-mail from this list. Is this what you wish to do?

    Joe G

  3. #3
    Join Date
    Oct 2003
    Posts
    43
    Reason I want to not have to get into access is because we have part of the DB setup with information on contracts etc, and when thoese contracts are with in say 3 Months of expiration then the boss will get an email automaticly with out some one having to remeber to go into access every day and run the script.


    Thanks for the advice.

    Billy

  4. #4
    Join Date
    Dec 2003
    Posts
    172
    Billy,

    Got it. So you will need something that, on a regular basis, polls the contracts table in the Access database and when necessary generates emails.

    Some solutions:

    You could create the application in Visual Basic. (Do you have VB?) And then you could do something like I described in my last post. I could give you some ideas on the VB thing if you need it.

    You could use a utility such as Windows Task Scheduler to automatically run your Access application. In the Access project, you could use a startup form or the Autoexec macro to check the contract dates when Access starts up and when completed and emails are sent, tell Access to quit.

    You could run a copy of Access and leave it running on your server full time, in a minimized window. In the Access project you would use a timer that you could set to check the dates every so often and if the dates are correct, process the contracts and send the emails.

    Alternately, you can use Outlook for your program. Since Outlook has VBA in it, and oftentimes is opened several times a day, there are a lot of things you could do creatively with Outlook. For example, you could key some code to an Outlook recurring task such as "Check Contract Dates" and this would call up Access through automation. Or you could write some code to execute and check Access dates when Outlook starts each time.

    Which solution interests you? (I'm sure there are others out there!)

    Joe G

  5. #5
    Join Date
    Oct 2003
    Posts
    43
    Joe,

    Thank you for your responses. They have helped alot. I personally like the Outlook solution. Due to I do not have control to the server. Two I do not have VB so this all has to be done in VBA...OH how I wish we were using VB...but of course power's that be are not alowing it. And everyone opens their email everyday so that works.



    Billy

  6. #6
    Join Date
    Dec 2003
    Posts
    172
    Billy,

    Agreed, it would be no different than you checking your daily calendar to see what meetings were on that day. People who use the calendar and scheduling features of Outlook usually check Outlook several times (but at least once a day). It does make sense.

    Now, how do we implement this in Outlook? Two approaches:

    1. Build the solution in Access. Then put an Access shortcut in Outlook. When Outlook starts, use a recurring task or a piece of Outlook startup event code that gives the user the reminder and a link or button to press when it's time to run the process. The shortcut will open Access (at which point an Autoexec macro or startup Access form will take over, process the emails and then quit Access).

    The advantage of method 1 is that you can basically work only in Access and the only part that involves Outlook is working with a single recurring task or just a shortcut.

    The disadvantage of method 1 is that (like the TV commercial) when Outlook executes your Access MDB with the Autoexec code in it, you are basically "setting it and forgetting it". You exercise no control over Access at this point - Outlook is just a bystander. You have to ensure Access can take off and land the plane without a pilot.

    2. Build the solution in Outlook and use automation to control Access from Outlook.

    The advantages of method 2 is that you gain more control over the process since Outlook basically owns Access and it runs cleaner because only one application is running. Also, you are able (most times) to set the visible property of the automation object to false so the user will never even see Access open up.

    In this method, Access is only used for the email and contract data - Outlook messaging is controlled within Outlook.

    Big disadvantage of method 2 is that you will run into the dreaded Microsoft Security Patch on the Outlook object model. If you are using any of the newer versions of Outlook (or an older version with the latest service patches) any attempts to process and send an email from Outlook will generate a 5-second security message that will have to be clicked off manually by the user for each email you send.

    Now there are workarounds and solutions to handle this warning message. If you have Microsoft Exchange you can turn this warning off with a special form on the server. There are some third-party solutions that will bypass the security patch. Some solutions provide their own email object library; others are geared to watch for this popup dialog and click it closed when it appears (but you still have 5 seconds of delay per message).

    Other alternatives include using non-Outlook email processing such as the default send-mail, SMTP service, and SQL Server mail drop folders.

    Some alternatives don't give you as much flexibility (for example allowing custom messages or attachments).

    So you see that generating email is going to require some planning and some comprimises, even if you didn't bother with Outlook at all.

    Joe G

  7. #7
    Join Date
    Oct 2003
    Posts
    43

    Thank you

    Thank you for all your help Joe I got it.




    Billy

  8. #8
    Join Date
    Jul 2003
    Posts
    5

    Actually....

    This is the best tool I've ever seen for just this reason.

    All you have to do is set the utility up for the times you disignate...for the program you designate....

    It will open Access, the code for Email will run wiithin Access (AutoExec), then Access closes. All this is done virtually unnoticeable to the user!!!!!

    My suggestion would be to use a standard Email code in vba in an AutoExec macro......you're done.

    You can have this run daily, weekly, or as you wish.


    http://www.mvps.org/access/modules/mdl0042.htm

    Enjoy!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •