Add Code in the After Insert Event to Send Emails

1. Select the tasks form of the tasks_maint page by selecting it in the Project Explorer, or clicking anywhere within the form's caption.
2. In the Properties window click on the Events tab.
3. Select the After Insert event.
4. Click on the [+] button.
5. Select Add Code...

   

6. Once you are in the Code view, replace the generated comment:

   ' Write your own code here.

   with the code below:

   Dim Mailer
     Set Mailer = Server.CreateObject("Persits.MailSender")
     Mailer.From = CCDLookUp("email", "employees", "emp_id=" &_
            DBIntranetDB.ToSQL(CCGetUserID(),ccsInteger), DBIntranetDB)
     Mailer.FromName = CCDLookUp("emp_name", "employees", "emp_id=" &_
            DBIntranetDB.ToSQL(CCGetUserID(), ccsInteger), DBIntranetDB)
     Mailer.AddAddress CCDLookUp("email", "employees", "emp_id=" &_
            DBIntranetDB.ToSQL(tasks.user_id_assign_to.Value, ccsInteger), DBIntranetDB)
     Mailer.Host = "mysmtphost.com"
            Mailer.IsHTML = True
     Mailer.Subject = "New task for you"
     Mailer.Body = "The following task was submitted:<br><br>" &_
            "Task ID: " & CCDLookUp("max(task_id)", "tasks", "user_id_assign_by=" &_
            DBIntranetDB.ToSQL(CCGetUserID(), ccsInteger), DBIntranetDB) &_
            "<br><br>" & tasks.task_desc.Text 
     Mailer.Send
     set Mailer = Nothing

   
   

   As you may have realized by now, the above code sends emails to users to whom the new tasks are assigned. Here is additional information you should be aware of:

   1. The above code requires that you install on your server the free Email component "ASPEmail", which you can obtain from http://www.aspemail.com/. There are many other email components and you can modify the above program by reading the documentation covering the component you choose to use.
   2. You need to replace the parameter "mysmtphost.com" with a SMTP server that you are authorized to use. This usually may be the same server that you configure as "Outgoing Mail Server (SMTP)" in your email client, like MS Outlook or Outlook Express.
   
   

   The following is an explanation of the above code:
   
   Dim Mailer

   Defines the Mailer object, which later will initialize the ASPEmail component.
   
   Set Mailer = Server.CreateObject("Persits.MailSender")

   Creates Mailer object and initializes the ASPEmail component.
   
   Mailer.From = CCDLookUp("email", "employees", "emp_id=" & DBIntranetDB.ToSQL(CCGetUserID(),ccsInteger), DBIntranetDB)

   Sets the From email address to the value of the email field in the employees table where emp_id matches the current user. The CCDLookUp function is used to retrieve a database value, while CCGetUserID retrieves the id of the currently logged in user.
   
   Mailer.FromName = CCDLookUp("emp_name", "employees", "emp_id=" & DBIntranetDB.ToSQL(CCGetUserID(), ccsInteger), DBIntranetDB)

   Sets the From name to the value of the emp_name field for the current user.
   
   Mailer.AddAddress CCDLookUp("email", "employees", "emp_id=" & DBIntranetDB.ToSQL(tasks.user_id_assign_to.Value, ccsInteger), DBIntranetDB)

   Sets the To email address to the email of the person that is assigned to the task. The CCDLookUp function is used here to retrieve the appropriate email address.
   
   Mailer.Host = "mysmtphost.com"

   Specifies the SMTP server that will be sending the email. (Replace this value with an SMTP host that you are authorized to use)
   
   Mailer.IsHTML = True

   Specifies that the email will be sent in HTML format (as opposed to plain text).
   
   Mailer.Subject = "New task for you"

   The subject of the email to be sent.
   
   Mailer.Body = "The following task was submitted:<br><br>" & "Task ID: " & CCDLookUp("max(task_id)", "tasks", "user_id_assign_by=" & DBIntranetDB.ToSQL(CCGetUserID(), ccsInteger), DBIntranetDB) & "<br><br>" & tasks.task_desc.Text 

   The body of the email which consists of the task description and the task id. The last inserted task id can be obtained using different methods with different databases. Unfortunately, MS Access doesn't support the retrieval of the last inserted record, therefore you will need to use the CCDLookUp function to retrieve the largest task id submitted by the current user (assuming that task ids are created incrementally).
   
   Mailer.Send

   Sends the email.
   
   set Mailer = Nothing

   Disposes of the Mailer object to free computer resources.

Next: Use the After Update Event to Send Emails