SQL Server Performance

Sending mail by T-SQL (without SQL Mail)

Discussion in 'Contribute Your SQL Server Scripts' started by johnny_bigu, May 17, 2004.

  1. johnny_bigu New Member

    [<img src='/community/emoticons/emotion-6.gif' alt=':(' />!] I was losting a lot of time trying to send mail by using SQL Mail, when I used my head! <br /><br />[<img src='/community/emoticons/emotion-2.gif' alt=':D' />] So, I forgot SQL Mail and discover an alternative method: using VBScrit/CDO/SMTP.<br /><br />A CDO is a interface programatically to manipulate collaboration objects. It´s easy to find ready to use in Windows 2000 computers. See this very simple example, by SMTP server (using default SMTP port = 25):<br />******************************************************************<br />1. Create a file named sendmail.vbs and save on c:winnt.<br />2. Put this code:<br />Set objEmail = CreateObject("CDO.Message")<br />objEmail.From = "yourmail@yourserver.com"<br /><br />objEmail.to = WScript.Arguments(1)<br />objEmail.Subject = WScript.Arguments(2)<br />objEmail.Textbody = WScript.Arguments(3)<br /><br />objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2<br />objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "your_SMTP_server_IP_or_name"<br />objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25<br /><br />objEmail.Configuration.Fields.Update<br />objEmail.Send<br /><br />set objEmail = Nothing<br /><br />3. And now, complete creating a stored procedure:<br />USE MASTER<br />GO<br />CREATE PROCEDURE sp_sendmail @mail varchar(150), @subject varchar(150), @textbody varchar(800) AS<br />DECLARE @MSG_SENT VARCHAR(1200)<br />SELECT @MSG_SENT = 'cscript c:winntsendmail.vbs /p "' + @mail + '" "' + @subject + '" "' + @textbody + '"'<br /><br />exec master.dbo.xp_cmdshell @MSG_SENT, NO_OUTPUT<br />SELECT 'Mail sent!'<br />GO<br />******************************************************************<br /><br />Ok, allright! <br /><br />Send a mail to test:<br /><br />EXEC master.dbo.sp_sendmail 'yourto@yourserver.com', 'subject', 'message'<br /><br />PS: Please, don´t put he xp_sendmail in trash (any day it´s may be util)
  2. kpyeung New Member

    [<img src='/community/emoticons/emotion-1.gif' alt=':)' />][<img src='/community/emoticons/emotion-1.gif' alt=':)' />]Is there any work around if the company blocked the SMTP port 25[xx(][xx(]<br /><br />I can successfully sent the mail but the receipant cannot receive any[V][V]<br /><br />
  3. johnny_bigu New Member

    Ok men,

    Do you know what machine is the SMTP server, that is connected to internet? Are you sure? Ask to your net. admin. Ok?

    Wait a minute: what you have between you and recepient? Check out the conections. Are the outlook working and sending mails (using SMTP)? So this code do the same.

    I sure.
  4. Fabianus New Member

    Hello,

    No mail is sent when using the SP in teh QA.

    So I have added SELECT @MSG_SENT at the end, and when I run this output in a command shell, mail is sent. So the vbs works.

    Can you help me on this?


  5. johnny_bigu New Member

    Hi there<br /><br />You just need verify your SMTP server. Look up at mail server at outlook and smtp port (the default is 25). Talk about with your network/mail administrator.<br />[<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />I good ideia is to run VB Script by using cscript (DOS Command).<br />I should - in a command prompt - type:<br />cscript c:sendmail.vbs <br /><br />You must use full path to .vbs file.<br /><br />More problems email -&gt; me.<br /><br />johnny_bigu@yahoo.com<br /><br /><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Fabianus</i><br /><br />Hello,<br /><br />No mail is sent when using the SP in teh QA.<br /><br />So I have added SELECT @MSG_SENT at the end, and when I run this output in a command shell, mail is sent. So the vbs works.<br /><br />Can you help me on this?<br /><br /><br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">
  6. Anjali_mp New Member

    Hi,
    I used your code to send email and it works. Thanks for posting.
    I have a question on the message part of email. How can i format the message like sending in html format.
    Please help. !!! Its urgent
    Thanks again
    Anjali
  7. Anjali_mp New Member

    Hi,
    I used your code to send email and it works. Thanks for posting.
    I have a question on the message part of email. How can i format the message like sending in html format.
    Please help. !!! Its urgent
    Thanks again
    Anjali
  8. Jack Vamvas Member

    Try adding something like :
    objMsg.HTMLBody = "your html"

Share This Page