send mail upon record insert

Hi all,

I would like to send mail after a record is inserted into an Access db...I tried using CDOSYS to grab variables passed from a previous page, but no go...I am using VB script and would like to use the value of some of the form fields being inserted in my email message. I want to use the email field in my insert form as the objMail.To = value.

Any suggestions?

Who is Participating?
b0lsc0ttConnect With a Mentor IT ManagerCommented:
You could insert this sample block of code right after you close the connection.

'Create the mail object
Set objMail = Server.CreateObject("CDO.Message")

'Set key properties
objMail.From = "me@123.123"
objMail.To = strCustomerEmail
objMail.Subject= "This is your subject
strEmailBody = "Stuff in your email body and your password is: " & strPassword
objMail.TextBody = strEmailBody

'Send the email

'Clean-up mail object
Set objMail = Nothing

' Rest of your code
b0lsc0ttIT ManagerCommented:

What was the problem?  Getting the mail object to work or getting the correct email address?  What you are trying to do is possible and I could give you a basic example but it may be easier to know what you have and what the problem is.

headbumpAuthor Commented:

Here is my insert code...basically I had tried to send mail on the redirect page after the insert and it didn't work (I realize because the cdosys can't grab the form fields)...I'd like to send email to the field in this form called strCustomerEmail and send the strPassword field in the body of that email as well:

' *** Edit Operations: declare variables

Dim MM_editAction
Dim MM_abortEdit
Dim MM_editQuery
Dim MM_editCmd

Dim MM_editConnection
Dim MM_editTable
Dim MM_editRedirectUrl
Dim MM_editColumn
Dim MM_recordId

Dim MM_fieldsStr
Dim MM_columnsStr
Dim MM_fields
Dim MM_columns
Dim MM_typeArray
Dim MM_formVal
Dim MM_delim
Dim MM_altVal
Dim MM_emptyVal
Dim MM_i

MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
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 = ""
' *** Insert Record: set variables

If (CStr(Request("MM_insert")) = "form1") Then

  MM_editConnection = MM_CopyKingPreview_STRING
  MM_editTable = "tblCustomer"
  MM_editRedirectUrl = "customer_added.asp"
  MM_fieldsStr  = "strCustomerName|value|strCustomerNumber|value|strCustomerContactFName|value|strCustomerContactLName|value|strCustomerPhone|value|strCustomerEmail|value|strCustomerFolder|value|strPassword|value|intStatus|value"
  MM_columnsStr = "strCustomerName|',none,''|strCustomerNumber|',none,''|strCustomerContactFName|',none,''|strCustomerContactLName|',none,''|strCustomerPhone|',none,''|strCustomerEmail|',none,''|strCustomerFolder|',none,''|strPassword|',none,''|bitStatus|none,Yes,No"

  ' create the MM_fields and MM_columns arrays
  MM_fields = Split(MM_fieldsStr, "|")
  MM_columns = Split(MM_columnsStr, "|")
  ' set the form values
  For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
    MM_fields(MM_i+1) = CStr(Request.Form(MM_fields(MM_i)))

  ' 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
      MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
    End If
  End If

End If
' *** Insert Record: construct a sql insert statement and execute it

Dim MM_tableValues
Dim MM_dbValues

If (CStr(Request("MM_insert")) <> "") Then

  ' create the sql insert statement
  MM_tableValues = ""
  MM_dbValues = ""
  For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
    MM_formVal = MM_fields(MM_i+1)
    MM_typeArray = Split(MM_columns(MM_i+1),",")
    MM_delim = MM_typeArray(0)
    If (MM_delim = "none") Then MM_delim = ""
    MM_altVal = MM_typeArray(1)
    If (MM_altVal = "none") Then MM_altVal = ""
    MM_emptyVal = MM_typeArray(2)
    If (MM_emptyVal = "none") Then MM_emptyVal = ""
    If (MM_formVal = "") Then
      MM_formVal = MM_emptyVal
      If (MM_altVal <> "") Then
        MM_formVal = MM_altVal
      ElseIf (MM_delim = "'") Then  ' escape quotes
        MM_formVal = "'" & Replace(MM_formVal,"'","''") & "'"
        MM_formVal = MM_delim + MM_formVal + MM_delim
      End If
    End If
    If (MM_i <> LBound(MM_fields)) Then
      MM_tableValues = MM_tableValues & ","
      MM_dbValues = MM_dbValues & ","
    End If
    MM_tableValues = MM_tableValues & MM_columns(MM_i)
    MM_dbValues = MM_dbValues & MM_formVal
  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

    If (MM_editRedirectUrl <> "") Then
    End If
  End If

End If

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

b0lsc0ttIT ManagerCommented:

I recommend that you send the mail before redirecting.  Do it on the same page and right after you insert.  You can even add a section so that it will only send the mail if there were no errors when inserting the record.

If you really want to send the message on a new page then use the URL to send variables (e.g. page2.asp?email=a%40a.123) or set up session variables (e.g. Session("email") = a@a.123).

Let me know which method you want to use if you want a more detailed example.  If you use the URL then you will need to URLEncode the variables, especially the email address.


headbumpAuthor Commented:

I'll take your recommendation and go with sending the mail on the same page. A more detailed example would be great and let me know if you need anything else from me..
headbumpAuthor Commented:

Actually I solved this another way but your answer helped steer me that way...I created session variables on the insert page and then supplied them as values on the next page where I send the mail.

Thanks again,
b0lsc0ttIT ManagerCommented:
Thanks for letting me know and the grade/points.  Session variables are really nice to have.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.