Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 321
  • Last Modified:

Sending emails using spPG_send_cdosysmail Stored Procedure - email Body.

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:
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/cdosys/html/_cdosys_messaging.asp

***********************************************************************/
   AS
   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', '138.83.66.45'

-- 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
   








0
chinnaji
Asked:
chinnaji
  • 3
  • 2
1 Solution
 
joeposter649Commented:
How are you calling the sp?  Sound like the message is getting truncated before it gets to the sp.  Perhaps in a url?
0
 
chinnajiAuthor Commented:
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
}
</script>
<%
'========================= 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,"~",",")
      err.Clear
      
      '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
      'Else            
      '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
      Else            
      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
      
      err.clear
      'strBody="Testing email SS"
      'strBodyText="Test BODY"
      
      'Comment the following line for stored procedure      
      'objCDO.Send
      
      '=======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
    'Response.End
   
    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"
            Response.End
      end if
      '---------------------------------
      SendMail = True
      Set objCDO = Nothing
      
End Function      

'=====================================================================================

%>
0
 
joeposter649Commented:
Try using Server.URLEncode when you pass strBody to SendMail().
0
 
chinnajiAuthor Commented:
Where exactly should I insert the server.URLEncode ??
0
 
joeposter649Commented:
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)


0
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.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now