Send email notification using after insert and ASPmail

CodeCharge Studio

Web Reporting

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> ASP

Send email notification using after insert and ASPmail

Author

Message

wardkent

Posts: 4

Posted: 11/16/2005, 7:18 AM

I have a message board of sorts for an engineering group. There are different people assigned to each project and when a message is posted I want to send an email to everyone associated with the project with the subject and the body of the message. I have been able to use ASPmail to send a single email to myself with the subject and body, I have even been able to use a look up to show the message coming from the person who did the post.

My problem is that I want to pull a list of email addressed from a database table and send this email to everyone on the list. I am not a programmer, I know enough to get myself into trouble. I also know enough to know this shouldn’t' be that hard but I can't figure it out.

Here is what I need to do:
I have a table I am filling in called [field_project_disscussion] this has all the discussion information. In the record builder I get the projectID from the URL and that is how I associate the correct discussion with the correct project. This has worked quite well. Now I am adding code to the after insert function to send the e mail. Like I said I can get it to send when I just fill in my email address in the Mail.AddAddress = "myemail@domain.com"
What I need to do is gather information from two other tables to get the list of email addresses. My list of users is in a table called [users] it contains the userID and the userEmail fields. I also have a table called [Field_Project_Assignment] This has a ProjectID field and a UserID field. I can create a grid and get this information listed on the page but I don't know how to loop around and add each one to a separate line like this.

Mail.AddAddress = "myemail@domain.com"
Mail.AddAddress = "bobemail@domain.com"
Mail.AddAddress = "jimemail@domain.com"
Mail.AddAddress = "fredemail@domain.com"

This is the format ASPmail requires to send this. The other option is to actually send the mail over and over again using a different email address each time.

Any help on this would be most appreciated.

wardkent

Posts: 4

Posted: 11/16/2005, 12:06 PM

I solved this myself. If anyone has other suggestions please feel free to comment. I am sure there could be a more streemlined approach but this is wroking.

    Dim rs  
  Dim strUserEmail   
  Dim UrlID  
    
  UrlID = CCGetFromGet("ProjectID",  0)  
     
 'instantiate the data source  
   If DBgeneral.state <> 1 Then DBgeneral.Open  
   Set rs = DBgeneral.Execute("SELECT UserEmail FROM Project_Email_Lists Where ProjectID="&UrlID)  
   While NOT rs.EOF  
   'get email addresses  
   strEmailAddr = rs("UserEmail")   
  
  Set Mail = Server.CreateObject("Persits.MailSender")  
  Mail.Host = "intramail.cis.cat.com"  
  
  Mail.From = CCDLookUp("UserEmail", "users", "usernum=" &_  
     DBgeneral.ToSQL(CCGetUserID(),ccsInteger), DBgeneral)  
  Mail.FromName = CCDLookUp("Name", "UserLookup", "usernum=" &_  
     DBgeneral.ToSQL(CCGetUserID(),ccsInteger), DBgeneral)  
  Mail.AddAddress strEmailAddr  
  Mail.Subject = "From project disucssion: " & CCDLookUp("ProjectName", "FieldProjects", "ProjectID="&UrlID, DBgeneral)  
  Mail.Body = "<b>Subject: </b> " & Field_project_discussion.Title.value & "<br><br><b>Message:</b> " & Field_project_discussion.Comment.value & "<p> </p> <p>This message was sent to you via the CIPL <a href='http://intranet.cosa.cat.com/lfcipl/start.asp'>website</a> project discussion board.<br>Please post reply on the website to capture the discussion.</p>"  
  Mail.IsHTML = True  
  Mail.Send ' send message  
  
   rs.MoveNext  
   Wend  
   'Clean Up  
   rs.close  
   Set rs = Nothing

These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

Internet Database

Visually create Web enabled database applications in minutes.
CodeCharge.com

Home | Search | Members | Register | Login