Solved

Sending emails using spPG_send_cdosysmail Stored Procedure - email Body.

Posted on 2004-09-16
8
272 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
  • 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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 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…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
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…

746 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

16 Experts available now in Live!

Get 1:1 Help Now