How to schedule a email generation task.

[yimma216]

Hi,

This is an intranet. I wrote an ASP page to display a list of expired tasks. There is a form in this ASP page to send email notification to people who have expried tasks.

Now I want to the email to be automatically generated, by scheduled task or a trigger.

However, I do not know how to do it from here? I was told that I needed to write a script. Please help.

[yimma216]

The ASP script include an emailincl.asp and global.asa.

[jhardman]

ASP is great for writing tasks that will fire when someone visits a webpage, but to schedule a task they don't work so well. You can use a related scripting technology, Windows Scripts (also using VBScript as the language) to schedule tasks. We have decided to leave those questions in this forum, but I'm not really an expert. Let me think if I can remember which of our users are experts at Windows Scripts.

Jared

[NeoPa]

I am no expert at Windows Scripting Host work, although I have managed to get a few things to work from time to time.

Let me have a look at this for you and see if I can at least work out a basic outline for you.

I'm afraid I'm not very ASP literate though so I do need to as is this process something that you wish to run as a scheduled task on the server or on the client?

Scheduling is a whole different ball-game of course, but I've done a little work there too so we'll see what can be managed.

[yimma216]

Window Scripting

Thanks jhardman and NeoPa.
After some research, I have tried to write the asp into vbscirpt and windows script file. then use Task Scheduler to run the script. However both of them did not work.

file1: testing.vbs

<!--#include file="emailincl.asp" -->
'document.write("Hello from VBScript!")
dim counter
counter= 0
dim subject
dim message_text
 
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
conn.Open "Driver={SQL Native Client};Server=cslproxy;database=project_management;Trusted_Connection=yes;"
 
sql="select * from task_days when project=7"
rs.open sql, conn 
Do while not rs.eof 
message_text= "This task will be due in the following date"
message_text= message_text & vbcrlf & "Due Date: " & rs("task_due")
message_text= message_text & vbcrlf & "Desctiption: " & rs("description")
message_text= message_text & vbcrlf & "Assign: " & rs("assign")
subject= "Task is due in " & rs("days_left") & " days"
 
email_team rs("project"),subject,message_text
rs.movenext
counter = counter + 1
Loop 
rs.close
conn.close

file2: ws_include.wsf

<job id="includeEmail">
 <script src="emailincl.asp" language="VBScript"/>
  <script language="VBScript">
  dim counter
  counter= 0
 dim subject
 dim message_text
 
  Set conn = CreateObject("ADODB.Connection")
  Set rs = CreateObject("ADODB.Recordset")
  conn.Open  "Driver={SQL Native Client};Server=cslproxy;database=project_management;Trusted_Connection=yes;"
  
   sql="select * from task_days when project=7"
  rs.open sql, conn 
  Do while not rs.eof 
   message_text= "This task will be due in the following date"
   message_text= message_text & vbcrlf & "Due Date: " & rs("task_due")
   message_text= message_text & vbcrlf & "Desctiption: " & rs("description")
   message_text= message_text & vbcrlf & "Assign: " & rs("assign")
   subject= "Task is due in " & rs("days_left") & " days"
 
   email_team rs("project"),subject,message_text
   rs.movenext
   counter = counter + 1
  Loop  
  rs.close
  conn.close
 </script>
</job>

[NeoPa]

OK. Let's see what we can do with this then.

Let's try to be specific and precise though, as otherwise we'll waste lots of time confusing each other.

1. Let's start with the scheduling.
   When should it be scheduled?
2. The code.
   You say it doesn't work. What does that mean? What can you tell me about what it does or doesn't do that will take us towards understanding what is going wrong?

You should understand, that from my experience with the WSH (Windows Scripting Host), it is probably the least friendly debugging environment of anything I've seen in many years.

We may well have to write our own debugging info out in the code. It is therefore critically important that we know at all times what we think we know. We will need to focus on particular areas of code at any time so we need to know what of the rest we really can rely on.

[yimma216]

Hi NeoPa,

1. Both are scheduled to run in the next 10minutes to test if it send an email to my and two outlook account. The schedule will stop running if it runs for 10 minutes.

2. I did not receive any emails as I did with the .asp version of the script (which was with form and button)

The following code should call the include file emailincl.asp

email_team rs("project"),subject,message_text

My working asp file:

<!--#include file="emailincl.asp" -->
[I][B][U]'html..........etc[/U][/B][/I]
 
 
dim counter
counter= 0
dim a
 
[I][B][U]'ADO Connection...........[/U][/B][/I]
[I][B][U]'Recordset.....etc[/U][/B][/I]
[I][B][U]'Open database.....etc[/U][/B][/I]
 
sql="select * from task_days where project=7"
  rs.open sql, conn 
 
[U][I][B]'a= total count of the sql above[/B][/I][/U]
 
if request("task")="add" then
 dim subject 
 'need another counter to start from 0
 dim count
 count = 0
 
 Do while count < a
   subject= "Task is due in " & request("days_left_"&count) & " days"
   message_text="This task will be due in the following date: " & request("task_due_"&count)
   message_text=message_text & vbcrlf & "Desctiption: " & request("description_"&count)
   message_text=message_text & vbcrlf & "Assign To: " & request("assign_"&count)
   message_text=message_text & vbcrlf & "Please update the status if this task is completed"
 
   email_team request("project_"&count),subject,message_text
   count = count + 1
 Loop
   response.write vbcrlf & "<br>" & a & "Email submitted."  
else
 
   response.write vbcrlf & "<form action='task_notice2.asp' method='post'>"
   response.write vbcrlf & "<input type=hidden name='task' value='add'>"
 
[B][I][U]'Table header and columns[/U][/I][/B]
 
  Do while not rs.eof        
      [I][B] '[U]Display the recordset from database[/U][/B][/I]
   rs.movenext
   counter = counter + 1
  Loop  
    response.write vbcrlf & "</table>"
    response.write vbcrlf & "<input type=submit name=a value='Click'></form>"  
 
rs.close
conn.close
End if

[NeoPa]

I'm sorry Yimma. I don't think I can really help here without spending much more time and effort than I have available.

This appears to be designing a whole (pretty complicated) script from scratch. I can handle some basics, but what you have at the moment is not trivial, but it's not a script either. It's in ASP (which I have no experience in whatsoever). The scheduling job alone, as it is now-relative (not a particular time of day but a time relative to the now of when it's run) is complex enough. I may be able to manage it if tasked with the job, but leading someone else through it at a distance (no way I can test it) would not be practicable.

It is therefore with regret that I must admit that I cannot help you on this one.

[yimma216]

No problems. will do more research.