send mail upon record insert

Posted on 2006-05-10
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
    LVL 54

    Expert Comment


    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


    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


    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.



    Author Comment


    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

    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


    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

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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Suggested Solutions

    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 information …
    I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now