Solved

Sending emails using spPG_send_cdosysmail Stored Procedure - email Body.

Posted on 2004-09-16
8
306 Views
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:
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
Comment
Question by:chinnaji
[X]
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
8 Comments
 
LVL 15

Expert Comment

by:joeposter649
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?
0
 

Author Comment

by:chinnaji
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
}
</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
 
LVL 15

Expert Comment

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

Author Comment

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

Accepted Solution

by:
joeposter649 earned 100 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)


0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

688 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