Send Email using sp_send_cdosysmail through a ColdFusion page

I'm trying to send email using the stored procedure called sp_send_cdosysmail through a coldfusion page.  The stored procedure can be found here;en-us;312839&sd=tech under the section "Create a Stored Procedure to Send CDOSYS Mail".  I'm passing in the required variables From, To, Subject, and Body.  If I run the query thorugh query analyzer the mail gets sent.  If I call the stored procedure through the webpage it is not sent.  Here's my code for calling the procedure.  The stored procedure is in the Master database.

<cfstoredproc procedure="sp_send_cdosysmailHTML" datasource="#datasource#">
      <cfprocparam cfsqltype="cf_sql_varchar" type="in" value="" dbvarname="@To">
      <cfprocparam cfsqltype="cf_sql_varchar" type="in" value="" dbvarname="@From">
      <cfprocparam cfsqltype="cf_sql_varchar" type="in" value="Order Confirmation" dbvarname="@Subject">
      <cfprocparam cfsqltype="cf_sql_longvarchar" type="in" value="This is the body of the email" dbvarname="@Body">      

When I call the procedure from the website I don't get an error message.  I've used an output variable to make sure the values where being passed in and the result were the values that were passed in.  

Can anyone shed some light on this situation?  

Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

LunchyConnect With a Mentor Commented:
Closed, 50 points refunded.
Friendly Neighbourhood Community Support Admin
You dont get an error, so I am assuming it just doesn't work?

Can you run it from Query Analyzer?

Does the following work if you run it? (I know it works on my machine)

ALTER Procedure sp_SMTPMail    
@SenderName varchar(100),      
@SenderAddress varchar(100),    
@RecipientName varchar(100),    
@RecipientAddress varchar(100),
@Subject varchar(200),  
@Body varchar(8000)AS          

SET nocount on          
declare @oMail int --Object reference  
declare @resultcode int        
EXEC @resultcode = sp_OACreate 'CDONTS.NewMail', @oMail OUT            
if @resultcode = 0      
EXEC @resultcode = sp_OASetProperty @oMail, 'From', @SenderAddress              
EXEC @resultcode = sp_OASetProperty @oMail, 'To', @RecipientAddress            
EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject        
EXEC @resultcode = sp_OASetProperty @oMail, 'Body', @Body                      
EXEC @resultcode = sp_OAMethod @oMail, 'Send', NULL                    
EXEC sp_OADestroy @oMail        
SET nocount off


Does the user on the ODBC connection have permissions to execute the sp_OASetProperty  etc...?
INFINIEDGEAuthor Commented:
I tried your procedure and I still didn't get any results or an error.  I can run both of the stored procedures through query analyzer with no problem.  In query analyzer the email is sent out and I recieve the email.

I checked the permissions for sp_OASetProperty, sp_OAMethod, etc and my user is granted permission to execute those procedures.
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

1) The user with which ColdFusion is running needs to have the appropriate permissions.
2) Why are you not using <cfmail> instead - that would be much easier. What is the requirement that forces you to use SQL Server to send emails?
INFINIEDGEAuthor Commented:
I solved the problem.  Apparently the <cfprocparam> tag's dbvariable doesn't work as I had hoped.  I assigned the variables in a different order than the stored proc was looking for them.  I thought by setting the dbvariable to the stored procedure's variable name the procedure wouldn't care the order of the parameters, this wasn't the case.

Thanks to all who responded.
Fine with me. I just would like to know why InfiniEdge is not using <cfmail>.
INFINIEDGEAuthor Commented:
Hi sigmacon,

I am currently in the process of changing the site from ColdFusion to  For a smooth transition I wanted to send my emails through a stored procedure that is already sending out emails for my company.

I am not re-writing the current page in, it will be done in the near future.  I was hoping that by using a stored procedure instead of the <cfmail> tag I would be able to minimize my work when I do write the page in
All Courses

From novice to tech pro — start learning today.