Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Sending emails using spPG_send_cdosysmail Stored Procedure - email Body.

Posted on 2004-09-16
Medium Priority
Last Modified: 2012-06-27
Hi Experts,

I have designed a web application where a user enters a request and submits to a Manager group. When clicked on Submit button the ASP page runs the below stored procedure and sends a email to the originator and the manager group.

Everything works fine except that it does not allow me to add a descriptive message in the email body. When the manager group receives the email it only displays partial tracking number which is a hyper link to that document in the database

for example :

Original Message: Request for Tracking # ABCD-091404-0001 ha sbeen submitted to manager group.

Message displayed in email: Request for Tracking # ABCD-

How can I add more lines to the email body so that users can read the email and get to know the status of the entry.

Please help .... Thanks

CREATE PROCEDURE [dbo].[spPG_send_cdosysmail]
   @From varchar(500) ,
   @To varchar(500) ,
   @Subject varchar(200),
   @Body varchar(4000)

This stored procedure takes the above parameters and sends
an e-mail.
All of the mail configurations are hard-coded in the
stored procedure.
Comments are added to the stored procedure where necessary.
Reference to the CDOSYS objects are at the following MSDN
Web site:

   Declare @iMsg int
   Declare @hr int
   Declare @source varchar(255)
   Declare @description varchar(500)
   Declare @output varchar(1000)

--************* Create the CDO.Message Object
   EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

--***************Configuring the Message Object
-- This is to configure a remote SMTP server.
-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
   EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
   EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', ''

-- Save the configurations to the message object.
   EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
   EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
   EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
   EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
   EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body
   EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
-- Do some error handling after each step if you need to.
-- Clean up the objects created.
   EXEC @hr = sp_OADestroy @iMsg -- commented by madhu

Question by:chinnaji
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 15

Expert Comment

ID: 12077823
How are you calling the sp?  Sound like the message is getting truncated before it gets to the sp.  Perhaps in a url?

Author Comment

ID: 12077927
The SP is called using an asp page called Sendmail.asp.

Here is the code for sendmail.asp
<script language="javascript">
var mainid = setTimeout('showTime()',1000);
function showTime(){
      var now1 = new Date()
      document.getElementById("clock").innerHTML = (now1.getMonth()+1) + "/" + now1.getDate() + "/" + now1.getFullYear() + " " + getTime();
      mainid = setTimeout('showTime()',1000);
function getTime() {
// initialize time-related variables with current time settings
var now = new Date()
var hour = now.getHours()
var minute = now.getMinutes()
var secs = now.getSeconds()
now = null
var ampm = "" 

// validate hour values and set value of ampm
if (hour >= 12) {
hour -= 12
ampm = "PM"
} else
ampm = "AM"
hour = (hour == 0) ? 12 : hour

// add zero digit to a one digit minute
if (minute < 10)
minute = "0" + minute // do not parse this number!

if (secs < 10)
      secs = "0" + secs;

// return time string
return hour + ":" + minute + ":" + secs + " " + ampm
'========================= E-Mail Functionality ======================================
Function SendMail(strFullAddr_From,strFullAddr_To, strBody, userLevel)
      'this function will generate the e-mail

      Dim objCDO
      Dim rs
      Dim ary
      Dim strBodyText
      'Dim strFullAddr_From
      'Dim strFullAddr_To
    Dim strFullAddr_cc
      Dim strPath, intSlashPos
    If userLevel = "1" Then
        strNextLvlName = "Account Transition Team."
      ElseIf userLevel = "2" Then
        strNextLvlName = "Collaborating Sales Team."
      ElseIf userLevel = "3" Then
        strNextLvlName = "Agent for NRC/MRC."
      ElseIf userLevel = "4" Then
        strNextLvlName = "Data Elements Verification Team."
      ElseIf userLevel = "5" Then
        strNextLvlName = "Compensation Payout Team."
      ElseIf userLevel = "6" Then
        strNextLvlName = "Compensation Admin Team."
      ElseIf userLevel = "101" Then
        strNextLvlName = "Avaya Account Transition Team."
      ElseIf userLevel = "102" Then
        strNextLvlName = "Avaya Collaborating Sales Team"
    End If


    on error resume next
      'Set objCDO = CreateObject("CDONTS.NewMail")
      Set objCDO = CreateObject("CDO.Message")
      strFullAddr_To = replace(strFullAddr_To,"~",",")
      'Response.Write Request.Form("hdnMgrName") & "<BR>"
      'Response.Write "<BR>To Adrress(s) : " & strFullAddr_To & "<BR>"
      intSlashPos = instrRev(Request.ServerVariables("SCRIPT_NAME"), "/")
      strPath = left(Request.ServerVariables("SCRIPT_NAME"), intSlashPos)
      'Comment from here when you start using stored procedure
      'objCDO.From =strFullAddr_From
      'objCDO.To = strFullAddr_To
      'objCDO.Subject = "Tracking #" & strBody      
      'Comment for CDO ends here
      'If session("AppType") = "AVAYA" Then 'For Avaya
      'strBodyText = "Request for Tracking #: <font color=""blue""><u><a target=""_blank""  href=""http://" & Request.ServerVariables("SERVER_NAME") & strPath & "Avaya_Track_View.asp?" & _
      '                    "track_num=" &  strBody & "&mgrName=" & Request.Form("hdnMgrName") & _
      '                    "&navFrom=mail&mgrPhone=" & Request.Form("hdnPhone") & _
      '                    "&mgrEmail="& Request.Form("hdnEmail") & _
      '                    """>" & strBody & "</a></u></font> has been sent to " & strNextLvlName & vbCrlf
      'strBodyText = "Request for Tracking #: <font color=""blue""><u><a target=""_blank""  href=""http://" & Request.ServerVariables("SERVER_NAME") & strPath & "Track_View.asp?" & _
      '                    "track_num=" &  strBody & "&mgrName=" & Request.Form("hdnMgrName") & _
      '                    "&navFrom=mail&mgrPhone=" & Request.Form("hdnPhone") & _
      '                    "&mgrEmail="& Request.Form("hdnEmail") & _
      '                    """>" & strBody & "</a></u></font> has been sent to " & strNextLvlName & vbCrlf
      'End If
      If session("AppType") = "AVAYA" Then 'For Avaya
      strBodyText = "TRK#:<font color=""blue""><u><a target=""_blank""  href=""http://" & Request.ServerVariables("SERVER_NAME") & strPath & "Avaya_Track_View.asp?" & _
                          "track_num=" &  strBody & "&mgrName=" & Request.Form("hdnMgrName") & _
                          "&navFrom=mail&mgrPhone=" & Request.Form("hdnPhone") & _
                          "&mgrEmail="& Request.Form("hdnEmail") & _
                          """>" & strBody & "</a></u></font> has been submitted" & vbCrlf
      strBodyText = "TRK#:<font color=""blue""><u><a target=""_blank""  href=""http://" & Request.ServerVariables("SERVER_NAME") & strPath & "Track_View.asp?" & _
                          "track_num=" &  strBody & "&mgrName=" & Request.Form("hdnMgrName") & _
                          "&navFrom=mail&mgrPhone=" & Request.Form("hdnPhone") & _
                          "&mgrEmail="& Request.Form("hdnEmail") & _
                          """>" & strBody & "</a></u></font> has been submitted" & vbCrlf
      End If
      'Comment the following line for stored procedure
      'objCDO.HTMLBody = strBodyText
      'strBody="Testing email SS"
      'strBodyText="Test BODY"
      'Comment the following line for stored procedure      
      '=======Remove the comments here to use the Stored procedure
      const adOpenDynamic = 3
      const adLockReadOnly  = 1
      Set objConn1   = Server.CreateObject("ADODB.Connection")
      Set objRS1     = Server.CreateObject("ADODB.Recordset")
    objConn1.Open  Application("ConnString")
    strSQL = "exec spPG_send_cdosysmail '" & strFullAddr_From & "','" & strFullAddr_To & "','" & "Tracking #" & strBody & "','" & strBodyText & "'"
    'strSQL = "exec spPG_send_cdosysmail 'test@test.com, abcd@abcd.com','Tracking #AVAYA-091504-0053','Request for Tracking #: AVAYA-091504-0053 has been sent to Collaboration team'"
    'strSQL = "select * from agents"
    'Response.Write strSQL
    objRS1.Open strSQL, objConn1, adOpenDynamic,adLockReadOnly
      '=======Remove the comments here to use the Stored procedure ends here

      if err.number <> 0 then
          Response.Write err.Description
          Response.Write "<br>"         
          Response.Write err.number
          Response.Write "Error occured while sending Email Notification"
      end if
      SendMail = True
      Set objCDO = Nothing
End Function      


LVL 15

Expert Comment

ID: 12078098
Try using Server.URLEncode when you pass strBody to SendMail().

Author Comment

ID: 12078813
Where exactly should I insert the server.URLEncode ??
LVL 15

Accepted Solution

joeposter649 earned 400 total points
ID: 12079085
You should probably be doing it with all the fields you're putting in an href.
Using it when you're calling SendMail() should take care of your tracking# problem...
SendMail(strFullAddr_From,strFullAddr_To, Server.URLEncode(strBody), userLevel)


Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

636 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