Automating e-mails from the server

I am looking for any general advice on the best approach to sending due date driven e-mails from the MySQL server to specific users who have events that become due based on MySQL database records.

I'm familiar with what needs to happen, and when, I'm just trying to decide the application framework to make it happen on the server, and what applications might be available off the shelf or whether I need to build my own utility to run alongside MySQL to send the e-mails from the server, as the user might not go into my application in time to notice the assigned event has become due.

David SmithsteinCEOAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I notice you've included the Access topic in this question. Are you familiar with Access? If so, then you could certainly create an Access database that could be fired from a Scheduled Task, and run code to determine the emails you need to send. You'd essentially setup an AutoExec macro (which is fired each time the database is open), or set a Startup form, and have code in the Open or Load event of that form to trigger your email action.

There is also a commercial Access product that does this: I've not used it, but have heard very good things about the product.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bill BachPresident and Btrieve GuruCommented:
What I do in my own environment is run a simple ASP script to read Email addresses out of my database via straight ODBC access, then build an Email and send it from there.  It requires the IIS/SMTP services to be installed, but it seems to work very well for me, and the logic is pretty simple:

- Build a new SQL query to the database to figure out which Emails need to be sent:
        Set Conn=Server.CreateObject("ADODB.Connection")
      Conn.CursorLocation=3    'adUseClient
      Conn.Open connString
        strSQL = "Select * FROM Table WHERE ....."
      Set rstContacts=Conn.Execute(strSQL)

- Iterate over the returning data set (which has Email address already in it):
           do until rstContacts.eof
- Build the Email:
          Set objCDOMail = Server.CreateObject("CDO.Message")
          objCDOMail.From     = FromEmailAddess
          objCDOMail.To       = ToEMailAddress
          objCDOMail.Subject  = strSubject
          objCDOMail.TextBody = strBody
          objCDOMail.HTMLBody = strHTML
          Return = objCDOMail.Send
          Set objCDOMail = Nothing
- Loop Until all done!

If you didn't want to use IIS, then you could always use VBScript in the same manner, in which case you can call it from an executable or batch file, instead of from a web browser.  Done well, you'll want to add logging of some kind, and perhaps some other features to indicate that an Email was sent, but you get the idea...
Luke ChungPresidentCommented:
Thanks Scott, for recommending Total Access Emailer. We created Total Access Emailer, so let me know if you have any questions related to that.

As for running things on the server, one generally doesn't run processes like email directly from a database in a server. It's different from a process that is used to change data or manage the database.

An email process should be run from a front-end application that links to the database. The application can be anything (a web app, .NET, VB6, etc.). In this case, a Microsoft Access database application can be looking at a query or other trigger that indicates it should send the emails, then do so. We have a page on Continuous Emailing to show how this can be done with Total Access Emailer.

Hope this helps.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

David SmithsteinCEOAuthor Commented:
Thanks Luke,

So the professional version would allow me to add VBA code and queries needed to identify the e-mails to be sent and the users who need them from my database.

Once I have a master design set up, I could then spin off ,accde files for a customer's server and run it next the the MySQL database to send out e-mails as needed.  

I could also set it up to run as a daily event using a timer or scheduling setting?

This would all be possible with my single user license for the professional version, is that correct?

Luke ChungPresidentCommented:
Hi David,

Sorry for not closing the loop originally. I hope you already got the answer. For completeness, the answer is Yes. If you get the Professional Version, you'll have the royalty-free VBA library that lets you implement Total Access Emailer features in VBA.

You'll be able to invoke your email blast with a procedure call, and you can tie that to an event on a form or invoke from a macro using the RunCode command.

With the latter, you can launch your database and that macro on a schedule you specify with an external program, or within Access, you can but it in a timer loop and execute it at the times you want. Our Total Visual Agent program can also be used to schedule when database macros are launched.
David SmithsteinCEOAuthor Commented:
Thanks Luke, I appreciate the follow up.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.