Solved

Send Email using sp_send_cdosysmail through a ColdFusion page

Posted on 2004-10-28
353 Views
Last Modified: 2013-12-20
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 http://support.microsoft.com/default.aspx?scid=kb;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="person@foo.com" dbvarname="@To">
      <cfprocparam cfsqltype="cf_sql_varchar" type="in" value="orders@foo.com" 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">      
</cfstoredproc>

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?  

Thanks
0
Question by:INFINIEDGE
    7 Comments
     
    LVL 17

    Expert Comment

    by:Tacobell777
    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      
    BEGIN          
    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        
    END            
    SET nocount off

    GO

    Does the user on the ODBC connection have permissions to execute the sp_OASetProperty  etc...?
    0
     

    Author Comment

    by:INFINIEDGE
    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.
    0
     
    LVL 8

    Expert Comment

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

    Author Comment

    by:INFINIEDGE
    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.
    0
     
    LVL 8

    Expert Comment

    by:sigmacon
    Fine with me. I just would like to know why InfiniEdge is not using <cfmail>.
    0
     

    Author Comment

    by:INFINIEDGE
    Hi sigmacon,

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

    I am not re-writing the current page in ASP.net, 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 ASP.net.
    0
     
    LVL 2

    Accepted Solution

    by:
    Closed, 50 points refunded.
    Lunchy
    Friendly Neighbourhood Community Support Admin
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    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!

    Now that Expression Web 4.0 (http://www.microsoft.com/expression/products/Upgrade.aspx) is free if you buy or have the full version of Expression Web 3.0, now is the best time to  migrate from FrontPage to Expression Web (http://www.frontpage-to-exp…
    Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
    The purpose of this video is to demonstrate how to automatically show related posts at the bottom of a blog post in WordPress. This will be demonstrated using a Windows 8 PC. Plugin “Yet Another Related Posts Plugin” will be used. Go to your…
    The purpose of this video is to demonstrate how to set up basic WordPress SEO. This will be demonstrated using a Windows 8 PC. The plugin used will be WordPress SEO by Yoast. Go to your WordPress login page. This will look like the following: myw…

    934 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

    12 Experts available now in Live!

    Get 1:1 Help Now