Forums
This topic is locked
CDO Email on update or insert
Posted 30 Oct 2001 16:16:54
1
has voted
30 Oct 2001 16:16:54 niraj sharma posted:
How would I send an email using CDO when a new record is inserted or a current record is modified???
Replies
Replied 30 Oct 2001 17:12:23
30 Oct 2001 17:12:23 Owen Eastwick replied:
This bit of code from a registration page should give you the general idea:
<pre id=code><font face=courier size=2 id=code>
<%
' *** Edit Operations: declare variables
MM_editAction = CStr(Request("URL")
If (Request.QueryString <> "" Then
MM_editAction = MM_editAction & "?" & Request.QueryString
End If
' boolean to abort record edit
MM_abortEdit = false
' query string to execute
MM_editQuery = ""
%>
<%
' *** Redirect if username exists
MM_flag="MM_insert"
If (CStr(Request(MM_flag)) <> "" Then
MM_dupKeyRedirect="usernametaken.asp"
MM_rsKeyConnection=MM_GBsounds2OLE_STRING
MM_dupKeyUsernameValue = CStr(Request.Form("Username")
MM_dupKeySQL="SELECT UserName FROM users WHERE UserName='" & MM_dupKeyUsernameValue & "'"
MM_adodbRecordset="ADODB.Recordset"
set MM_rsKey=Server.CreateObject(MM_adodbRecordset)
MM_rsKey.ActiveConnection=MM_rsKeyConnection
MM_rsKey.Source=MM_dupKeySQL
MM_rsKey.CursorType=0
MM_rsKey.CursorLocation=2
MM_rsKey.LockType=3
MM_rsKey.Open
If Not MM_rsKey.EOF Or Not MM_rsKey.BOF Then
' the username was found - can not add the requested username
MM_qsChar = "?"
If (InStr(1,MM_dupKeyRedirect,"?" >= 1) Then MM_qsChar = "&"
MM_dupKeyRedirect = MM_dupKeyRedirect & MM_qsChar & "requsername=" & MM_dupKeyUsernameValue
Response.Redirect(MM_dupKeyRedirect)
End If
MM_rsKey.Close
End If
%>
<%
' *** Insert Record: set variables
If (CStr(Request("MM_insert") <> "" Then
'Send an Email to the new User
'To Correct Server Time in U.S.A 6 hours behind UK time
ServerTime = Now()
CorrectTime = DateAdd("h",6,ServerTime)
MessageTo = Request("Email Address"
FirstName = Request("First Name"
LastName = Request("Last Name"
Username = Request("Username"
Password = Request("Password"
ContactName = Request("Contact Name"
EmailAddress = Request("Email Address"
DayTel = Request("Day Tel"
EveTel = Request("Evening Tel"
Mobile = Request("Mobile"
If DayTel = "" Then
DayTel = "No Number Provided"
End If
If EveTel = "" Then
EveTel = "No Number Provided"
End If
If Mobile = "" Then
Mobile = "No Number Provided"
End If
'The header/footer for the email
Header1 = "Hello "& FirstName &", many thanks for registering with GBsounds"
Const Header2 = "GBsounds is completely free to use, you can find all sorts of music information and you may add as much information to the site as you wish, the more the merrier."
Const Header3 = "GBsounds relies on visitor input to make the site, the more information visitors provide the more useful it becomes."
Const Header4 = "GBsounds doesn't have a huge marketing budget so if you like the site please take a couple of minutes to fill out the Tell a Friend form and spread the word. In addition if you have any ideas, suggestions or would just like to give us your comments please use the Feedback form, we would love to hear from you."
Const Header5 = "Here are your registration details:"
Const Footer1 = "Regards GBsounds"
Const Footer2 = "This is an automaticly generated message, please don't reply or send Email to the sending address, the server just isn't that chatty. If you would like to send a message to GBsounds use the Contact Us page at www.GBsounds.co.uk/contact.asp"
mail_Body = Header1 & vbCrLf & vbCrLf
mail_Body = mail_Body & Header2 & vbCrLf & vbCrLf
mail_Body = mail_Body & Header3 & vbCrLf & vbCrLf
mail_Body = mail_Body & Header4 & vbCrLf & vbCrLf & vbCrLf
mail_Body = mail_Body & Header5 & vbCrLf & vbCrLf
mail_Body = mail_Body & "First Name: " & FirstName & vbCrLf
mail_Body = mail_Body & "Last Name: " & LastName & vbCrLf
mail_Body = mail_Body & "Username: " & Username & vbCrLf
mail_Body = mail_Body & "Password: " & Password & vbCrLf
mail_Body = mail_Body & "Contact Name: " & ContactName & vbCrLf
mail_Body = mail_Body & "Email Address: " & EmailAddress & vbCrLf
mail_Body = mail_Body & "Daytime Telephone: " & DayTel & vbCrLf
mail_Body = mail_Body & "Evening Telephone: " & EveTel & vbCrLf
mail_Body = mail_Body & "Mobile: " & Mobile & vbCrLf
mail_Body = mail_Body & vbCrLf & vbCrLf & Footer1
mail_Body = mail_Body & vbCrLf & vbCrLf & vbCrLf & Footer2
'Response.Write mail_body
'Create the mail object and send the mail
Dim objCDO
Set objCDO = Server.CreateObject("CDONTS.NewMail"
objCDO.To = MessageTo
objCDO.From = " "
objCDO.Subject = "GBsounds Registration Details"
objCDO.Body = mail_Body
objCDO.Send
Set objCDO = Nothing
'Then Insert the new records
MM_editConnection = MM_GBsounds2OLE_STRING
MM_editTable = "users"
MM_editRedirectUrl = "regthanks.asp"
MM_fieldsStr = "First Name|value|Last Name|value|Username|value|Password|value|Contact Name|value|Email Address|value|Day Tel|value|Evening Tel|value|Mobile|value|AccessLevel|value"
MM_columnsStr = "FirstName|',none,''|LastName|',none,''|UserName|',none,''|UserPassword|',none,''|ContactName|',none,''|Email|',none,''|DayTel|',none,''|EveTel|',none,''|Mobile|',none,''|AccessLevel|none,none,NULL"
' create the MM_fields and MM_columns arrays
MM_fields = Split(MM_fieldsStr, "|"
MM_columns = Split(MM_columnsStr, "|"
' set the form values
For i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_fields(i+1) = CStr(Request.Form(MM_fields(i)))
Next
' append the query string to the redirect URL
If (MM_editRedirectUrl <> "" And Request.QueryString <> "" Then
If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString <> "" Then
MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
Else
MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
End If
End If
End If
%>
<%
' *** Insert Record: construct a sql insert statement and execute it
If (CStr(Request("MM_insert") <> "" Then
Session("MM_Username" = cStr(Request("Username")
' create the sql insert statement
MM_tableValues = ""
MM_dbValues = ""
For i = LBound(MM_fields) To UBound(MM_fields) Step 2
FormVal = MM_fields(i+1)
MM_typeArray = Split(MM_columns(i+1),","
Delim = MM_typeArray(0)
If (Delim = "none" Then Delim = ""
AltVal = MM_typeArray(1)
If (AltVal = "none" Then AltVal = ""
EmptyVal = MM_typeArray(2)
If (EmptyVal = "none" Then EmptyVal = ""
If (FormVal = "" Then
FormVal = EmptyVal
Else
If (AltVal <> "" Then
FormVal = AltVal
ElseIf (Delim = "'" Then ' escape quotes
FormVal = "'" & Replace(FormVal,"'","''" & "'"
Else
FormVal = Delim + FormVal + Delim
End If
End If
If (i <> LBound(MM_fields)) Then
MM_tableValues = MM_tableValues & ","
MM_dbValues = MM_dbValues & ","
End if
MM_tableValues = MM_tableValues & MM_columns(i)
MM_dbValues = MM_dbValues & FormVal
Next
MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & " values (" & MM_dbValues & ""
If (Not MM_abortEdit) Then
' execute the insert
Set MM_editCmd = Server.CreateObject("ADODB.Command"
MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = MM_editQuery
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close
If (MM_editRedirectUrl <> "" Then
Response.Redirect(MM_editRedirectUrl)
End If
End If
End If
%>
</font id=code></pre id=code>
Bsically put your CDONTS stuff after:
If (CStr(Request("MM_insert") <> "" Then
Regards
Owen.
Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo
<pre id=code><font face=courier size=2 id=code>
<%
' *** Edit Operations: declare variables
MM_editAction = CStr(Request("URL")
If (Request.QueryString <> "" Then
MM_editAction = MM_editAction & "?" & Request.QueryString
End If
' boolean to abort record edit
MM_abortEdit = false
' query string to execute
MM_editQuery = ""
%>
<%
' *** Redirect if username exists
MM_flag="MM_insert"
If (CStr(Request(MM_flag)) <> "" Then
MM_dupKeyRedirect="usernametaken.asp"
MM_rsKeyConnection=MM_GBsounds2OLE_STRING
MM_dupKeyUsernameValue = CStr(Request.Form("Username")
MM_dupKeySQL="SELECT UserName FROM users WHERE UserName='" & MM_dupKeyUsernameValue & "'"
MM_adodbRecordset="ADODB.Recordset"
set MM_rsKey=Server.CreateObject(MM_adodbRecordset)
MM_rsKey.ActiveConnection=MM_rsKeyConnection
MM_rsKey.Source=MM_dupKeySQL
MM_rsKey.CursorType=0
MM_rsKey.CursorLocation=2
MM_rsKey.LockType=3
MM_rsKey.Open
If Not MM_rsKey.EOF Or Not MM_rsKey.BOF Then
' the username was found - can not add the requested username
MM_qsChar = "?"
If (InStr(1,MM_dupKeyRedirect,"?" >= 1) Then MM_qsChar = "&"
MM_dupKeyRedirect = MM_dupKeyRedirect & MM_qsChar & "requsername=" & MM_dupKeyUsernameValue
Response.Redirect(MM_dupKeyRedirect)
End If
MM_rsKey.Close
End If
%>
<%
' *** Insert Record: set variables
If (CStr(Request("MM_insert") <> "" Then
'Send an Email to the new User
'To Correct Server Time in U.S.A 6 hours behind UK time
ServerTime = Now()
CorrectTime = DateAdd("h",6,ServerTime)
MessageTo = Request("Email Address"
FirstName = Request("First Name"
LastName = Request("Last Name"
Username = Request("Username"
Password = Request("Password"
ContactName = Request("Contact Name"
EmailAddress = Request("Email Address"
DayTel = Request("Day Tel"
EveTel = Request("Evening Tel"
Mobile = Request("Mobile"
If DayTel = "" Then
DayTel = "No Number Provided"
End If
If EveTel = "" Then
EveTel = "No Number Provided"
End If
If Mobile = "" Then
Mobile = "No Number Provided"
End If
'The header/footer for the email
Header1 = "Hello "& FirstName &", many thanks for registering with GBsounds"
Const Header2 = "GBsounds is completely free to use, you can find all sorts of music information and you may add as much information to the site as you wish, the more the merrier."
Const Header3 = "GBsounds relies on visitor input to make the site, the more information visitors provide the more useful it becomes."
Const Header4 = "GBsounds doesn't have a huge marketing budget so if you like the site please take a couple of minutes to fill out the Tell a Friend form and spread the word. In addition if you have any ideas, suggestions or would just like to give us your comments please use the Feedback form, we would love to hear from you."
Const Header5 = "Here are your registration details:"
Const Footer1 = "Regards GBsounds"
Const Footer2 = "This is an automaticly generated message, please don't reply or send Email to the sending address, the server just isn't that chatty. If you would like to send a message to GBsounds use the Contact Us page at www.GBsounds.co.uk/contact.asp"
mail_Body = Header1 & vbCrLf & vbCrLf
mail_Body = mail_Body & Header2 & vbCrLf & vbCrLf
mail_Body = mail_Body & Header3 & vbCrLf & vbCrLf
mail_Body = mail_Body & Header4 & vbCrLf & vbCrLf & vbCrLf
mail_Body = mail_Body & Header5 & vbCrLf & vbCrLf
mail_Body = mail_Body & "First Name: " & FirstName & vbCrLf
mail_Body = mail_Body & "Last Name: " & LastName & vbCrLf
mail_Body = mail_Body & "Username: " & Username & vbCrLf
mail_Body = mail_Body & "Password: " & Password & vbCrLf
mail_Body = mail_Body & "Contact Name: " & ContactName & vbCrLf
mail_Body = mail_Body & "Email Address: " & EmailAddress & vbCrLf
mail_Body = mail_Body & "Daytime Telephone: " & DayTel & vbCrLf
mail_Body = mail_Body & "Evening Telephone: " & EveTel & vbCrLf
mail_Body = mail_Body & "Mobile: " & Mobile & vbCrLf
mail_Body = mail_Body & vbCrLf & vbCrLf & Footer1
mail_Body = mail_Body & vbCrLf & vbCrLf & vbCrLf & Footer2
'Response.Write mail_body
'Create the mail object and send the mail
Dim objCDO
Set objCDO = Server.CreateObject("CDONTS.NewMail"
objCDO.To = MessageTo
objCDO.From = " "
objCDO.Subject = "GBsounds Registration Details"
objCDO.Body = mail_Body
objCDO.Send
Set objCDO = Nothing
'Then Insert the new records
MM_editConnection = MM_GBsounds2OLE_STRING
MM_editTable = "users"
MM_editRedirectUrl = "regthanks.asp"
MM_fieldsStr = "First Name|value|Last Name|value|Username|value|Password|value|Contact Name|value|Email Address|value|Day Tel|value|Evening Tel|value|Mobile|value|AccessLevel|value"
MM_columnsStr = "FirstName|',none,''|LastName|',none,''|UserName|',none,''|UserPassword|',none,''|ContactName|',none,''|Email|',none,''|DayTel|',none,''|EveTel|',none,''|Mobile|',none,''|AccessLevel|none,none,NULL"
' create the MM_fields and MM_columns arrays
MM_fields = Split(MM_fieldsStr, "|"
MM_columns = Split(MM_columnsStr, "|"
' set the form values
For i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_fields(i+1) = CStr(Request.Form(MM_fields(i)))
Next
' append the query string to the redirect URL
If (MM_editRedirectUrl <> "" And Request.QueryString <> "" Then
If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString <> "" Then
MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
Else
MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
End If
End If
End If
%>
<%
' *** Insert Record: construct a sql insert statement and execute it
If (CStr(Request("MM_insert") <> "" Then
Session("MM_Username" = cStr(Request("Username")
' create the sql insert statement
MM_tableValues = ""
MM_dbValues = ""
For i = LBound(MM_fields) To UBound(MM_fields) Step 2
FormVal = MM_fields(i+1)
MM_typeArray = Split(MM_columns(i+1),","
Delim = MM_typeArray(0)
If (Delim = "none" Then Delim = ""
AltVal = MM_typeArray(1)
If (AltVal = "none" Then AltVal = ""
EmptyVal = MM_typeArray(2)
If (EmptyVal = "none" Then EmptyVal = ""
If (FormVal = "" Then
FormVal = EmptyVal
Else
If (AltVal <> "" Then
FormVal = AltVal
ElseIf (Delim = "'" Then ' escape quotes
FormVal = "'" & Replace(FormVal,"'","''" & "'"
Else
FormVal = Delim + FormVal + Delim
End If
End If
If (i <> LBound(MM_fields)) Then
MM_tableValues = MM_tableValues & ","
MM_dbValues = MM_dbValues & ","
End if
MM_tableValues = MM_tableValues & MM_columns(i)
MM_dbValues = MM_dbValues & FormVal
Next
MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & " values (" & MM_dbValues & ""
If (Not MM_abortEdit) Then
' execute the insert
Set MM_editCmd = Server.CreateObject("ADODB.Command"
MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = MM_editQuery
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close
If (MM_editRedirectUrl <> "" Then
Response.Redirect(MM_editRedirectUrl)
End If
End If
End If
%>
</font id=code></pre id=code>
Bsically put your CDONTS stuff after:
If (CStr(Request("MM_insert") <> "" Then
Regards
Owen.
Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo