Automatic email notices from classic asp

I have a classic asp application with a SQL Express back-end. I need to have the application automatically send email notifications when items in the database are about to expire. I can send an SMTP email when someone clicks a button, but no idea how to set something up so that the emails automatically go out based on the dates in the database. Any help will be greatly appreciated. Thanks.
MzLibertyAsked:
Who is Participating?

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

x
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.

Lee W, MVPTechnology and Business Process AdvisorCommented:
You can code into a page a check so that every time a user accesses a web page it searches for expiring items... but this would be a bad idea.

You have a SQL database - use a VBS or another program on a scheduled task to run the query and send the e-mail.  Don't limit yourself to classic ASP - in this case, that's not the best solution in my opinion.
0
MzLibertyAuthor Commented:
If you know of a way to send automatic emails from SQL Express, I would love to try it.
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Since you know asp/vb, I would create an asp page (no html, just asp/vb code) that will do hit recordset that displays all the rows that are about to expire.  In the row of data, create your view so you can match it with an email.   If you know there will be multiple rows of data, then group by an email address.  Or even have one recordset of just emails, then another that can list the rows of data using the email as a filter.

From there, build your email like you are used to.  

Your test should be when you surf to the page, it will do what you need.  

Once that works, add an if then statment looking for some type of passcode to prevent you from accidentally hitting this page.  It could look for a form post or querystring.

Next create a scheduled task to run once each day (or whatever interval you need).  I have my scheduled tasks hit a vbs page that posts to the asp page like this

Call RunFile() 
 Sub RunFile() 

     'Force the script to finish on an error. 
     On Error Resume Next 

     Declare variables 
     Dim objRequest 
     Dim URL 

     Set objRequest = CreateObject("Microsoft.XMLHTTP") 
     theData="passcode=abc123" 
     URL = "http://www.mydomain.com/scheduledtask/somepage.asp" 

     objRequest.open "POST", URL , false  
	 objRequest.setRequestHeader "lastCached", now() 
	 objRequest.setRequestHeader "Content-Type", "application/x-www-form-urlencoded" 
     objRequest.Send theData
     Set objRequest = Nothing 

 End Sub 

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Lee W, MVPTechnology and Business Process AdvisorCommented:
I don't understand why you would do this with ASP.  Do this in VBScript and create a scheduled task that runs it nightly.
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
You are right. I sometimes use the same asp/vb page in multiple ways for both internal functions like this automated mail and as a page to call via ajax or direct post from an asp page. But you can just as easily do it all in vbs and even place it outside the public folder.

The end result is all the same.
0
MzLibertyAuthor Commented:
Thanks. You guys are obviously quite a bit more advanced at this than I am. The send mail I'm using is a bit of code I got from somewhere else. I do not know how to create a scheduled task. I don't really know what I'm doing, but I'm pretty good at figuring out how to make something work once I have a place to start. I hope you guys know how great you are, and how much your input helps me. Thank you.
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Thank you MzLiberty!

Are you on a shared hosting plan or do you manage your own server?  Do you have panel software such as Plesk or does your host have their own custom panel?  

You can either create a scheduled task with plesk or your custom panel if you are on shared hosting or your dedicated box has plesk.  Otherwise, you can create a scheduled task by going to your Server Manager > Configuration > Task Scheduler.  

In plesk you would go to the domain, click on the advanced area and you will see a task scheduler.  

You can point the "task" to the vbs code.
0

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
MzLibertyAuthor Commented:
Are you talking about the SQL Server (SQL Express, limited functionality) or the web server? The application is installed on the user's network server. I don't have access to the server, but I can send a script for them to run. I will look into this on my server and see what I can come up with. Thanks!
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
I am talking about the webserver.  Sending them the script will work fine!
0
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
ASP

From novice to tech pro — start learning today.