send mail upon record insert

Posted on 2006-05-10
Medium Priority
Last Modified: 2008-03-04
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?

Question by:headbump
  • 4
  • 3
LVL 54

Expert Comment

ID: 16653843

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.


Author Comment

ID: 16653878

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

LVL 54

Expert Comment

ID: 16653993

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.


Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 16654503

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..
LVL 54

Accepted Solution

b0lsc0tt earned 2000 total points
ID: 16661711
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

Author Comment

ID: 16662050

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,
LVL 54

Expert Comment

ID: 16662706
Thanks for letting me know and the grade/points.  Session variables are really nice to have.


Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question