Solved

Sending emails using spPG_send_cdosysmail Stored Procedure - email Body.

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

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…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

733 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