January 30, 2006 at 1:56 pm
send email using JMail component
--------------------------------
I am using JMAIL component to send out emails from SQL server with some data. I can send it out to the entire office network but can not do the same while sending it to hotmail or yahoo account.. What could be the reason?? Please help.. if you need more info please pm me on this site.
Thanks
Here is the code that i used, i got this from one of the article on this site
CREATE PROC envia_mail
/*=============================================================================================
Fecha...... 28/Nov/2003 Marco A. Serrano
Proposito.. Enviar desde SQL Server Correo electronico usando Jmail
Input... @Vchsender WHO SENDS THE MESSAGE, (JPALACIOS@HOTMAIL.COM)
@Vchsendername NAME OF THAT SENDS THE MESSAGE(OPTIONAL), (JOSE LUIS PALACIOS)
@Vchrecipient To WHO IS SENT The MESSAGE, (BVELASCO@YAHOO.COM;JUMENDEZ@HOTMAIL.COM)
@VchrecipientBCC To WHO IS SENT HIDDEN COPY To HIM Of the MESSAGE(OPTIONAL),
@VchrecipientCC To WHO IS SENT COPY To HIM Of the MESSAGE(OPTIONAL),
@Vchattachment ATTACHED FILE (OPTIONAL), (C:\MY DOCUMENTS\MY.DOC)
@Vchsubject SUBJECT OF THE MESSAGE, MAXIMUM 255 CHARACTERS,
@Vchmailbody BODY OF THE MESSAGE, MAXIMUM 255 CHARACTERS,
@VchBodyPart IN ORDER TO ADD TEXT TO THE BODY OF THE MESSAGE,
@VchContentType CONTAINED TYPE(HTML ó TEXTO) OPTIONAL, IF IT IS NOT INCLUDED BY DEFAULT IT IS TEXT
output.... Mail
NOTE: For greater information of this utileria can obtain it in http://www.dimac.net/
==============================================================================================
Date | modified | Intention
==============================================================================================*/
@Vchsender VARCHAR(100),
@Vchsendername VARCHAR(100)='',
@Vchrecipient VARCHAR(1000),
@VchrecipientBCC VARCHAR(1000)='',
@VchrecipientCC VARCHAR(1000)='',
@Vchattachment VARCHAR(100)='',
@Vchsubject VARCHAR(255),
@Vchmailbody VARCHAR(255),
@VchBodyPart VARCHAR(8000),
@VchContentType VARCHAR(50)=''
AS
--VARIABLES PARA DESGLOSAR LA LISTA DE DESTINATARIOS Y/O PARA DIVIDIR LA VARIABLE @VchBodyPart E INSERTAR TEXTO
--AL CUERPO DEL MENSAJE CUENDO ESTE SOBREPASE LOS 255 CARACTERES
DECLARE @Vchstring VARCHAR(8000), @Vchstring_length INT, @VchstrToEmail VARCHAR(255),
@Vchsubstring NVARCHAR(50), @Intstartpos INT, @Intendpos INT
--VARIABLES PARA EL OBJECTO JMAIL
DECLARE @Intobject INT, @hr INT, @Intrc INT, @Vchoutput VARCHAR(400),
@Vchdescription VARCHAR(400), @Vchsource VARCHAR(400),
@Vchdominio VARCHAR(100), --SERVIDOR DE DOMINIO (CCPUEBLA.COM.MX)
@Vchserveraddress VARCHAR(255) --DIRECCION O NOMBRE DEL SERVIDOR PROXI
SET NOCOUNT ON
BEGIN TRAN
--OBTENEMOS EL DOMINIO Y DIRECCION DEL SERVIDOR DE CORREO
--SELECT @Vchdominio = RTRIM(servidor_dominio), @Vchserveraddress = RTRIM(servidor_direccion)
--FROM configuracion_servidor_proxi
set @Vchdominio = 'domainname.com'
--CREA UNA INSTANCIA DEL OBJETO "jmail.smtpmail" EN UNA INSTANCIA DE MICROSOFT SQL SERVER
--Y ESTABLECE LOS VALORES DE LAS PROPIEDADES DEL OBJETO "jmail.smtpmail"
EXEC @hr = SP_OACreate 'jmail.smtpmail', @Intobject OUT --Crea el objeto
EXEC @hr = sp_OASetProperty @Intobject, 'ISOEncodeHeaders', 'false' --Decodifica caracteres segun el standard iso-8859-1
EXEC @hr = sp_OASetProperty @Intobject, 'charset', 'iso-8859-1' --Setea a caracteres iso-8859-1
EXEC @hr = sp_OASetProperty @Intobject, 'Maildomain', @Vchdominio
EXEC @hr = sp_OASetProperty @Intobject, 'Logging', true
EXEC @hr = SP_OASetProperty @Intobject, 'Sender', @Vchsender
--EXEC @hr = SP_OASetProperty @Intobject, 'ServerAddress', @Vchserveraddress
--SI SE REQUIERE FORMATO HTML
IF @VchContentType <> ''
EXEC @hr = SP_OASetProperty @Intobject, 'ContentType', @VchContentType --Para convertir el cuerpo del mensaje a formato HTML
-- ADICIONA LA LISTA DE CORREO ELECTRONICO AL METODO "ADDRECIPIENT" ESTE WHILE ES PARA PREVENIR
-- ENVIAR A VARIOS DESTINATARIOS
SELECT @Vchsubstring = ''
SELECT @Vchstring = @Vchrecipient
SELECT @Vchstring_length = len(@Vchstring)
SELECT @Intstartpos = 1
SELECT @Intendpos = 1
WHILE @Intstartpos <= @Vchstring_length and @Intendpos <= @Vchstring_length + 1
BEGIN
IF SUBSTRING(@Vchstring,@Intendpos,1) = ';' or @Intendpos > @Vchstring_length
BEGIN
SELECT @VchstrToEmail = SUBSTRING(@Vchstring,@Intstartpos,@Intendpos-@Intstartpos)
EXEC @hr = SP_OAMethod @Intobject, 'AddRecipient', NULL , @VchstrToEmail
SELECT @Intstartpos = @Intendpos + 1
SELECT @Intendpos = @Intstartpos + 1
END
SELECT @Intendpos = @Intendpos + 1
END
--INSERTAMOS EL ASUNTO Y CUERPO DEL MENSAJE
EXEC @hr = SP_OASetProperty @Intobject, 'Subject', @Vchsubject
--INSERTAMOS EL CUERPO DEL MENSAJE
EXEC @hr = SP_OASetProperty @Intobject, 'Body', @Vchmailbody
--SI ES MAS GRANDE EL CUERPO DEL MENSAJE ADICIONAMOS EL TEXTO EXTRA
SELECT @Vchsubstring = ''
SELECT @Vchstring = @VchBodyPart
SELECT @Vchstring_length = 8000--len(@Vchstring)
SELECT @Intstartpos = 1
SELECT @Intendpos = 80
WHILE @Intendpos <= @Vchstring_length
BEGIN
SELECT @VchBodyPart = SUBSTRING(@Vchstring,@Intstartpos,80)
IF Len(@VchBodyPart) = 0
BEGIN
BREAK;
END
EXEC @hr = sp_OAMethod @Intobject, 'AppendText', null, @VchBodyPart
SELECT @Intstartpos = @Intendpos + 1
SELECT @Intendpos = @Intendpos + 80
END
--SI EL MENSAJE VA EN FORMATO HTML CIERRA EL CUERPO DEL MENSAJE
IF @VchContentType = 'text/html'
BEGIN
SELECT @VchBodyPart = '</FONT></BODY></HTML>'
EXEC @hr = sp_OAMethod @Intobject, 'AppendText', null, @VchBodyPart
END
--SI SE NECESITARA ENVIAR UN ARCHIVO ADJUNTO
IF NOT(@Vchattachment='')
EXEC @hr = sp_OAMethod @Intobject, 'Addattachment', NULL , @Vchattachment
--SI SE REQUIERE ENVIAR UNA COPIA DEL MENSAJE OCULTA
IF NOT(@VchrecipientBCC='')
EXEC @hr = sp_OAMethod @Intobject, 'AddRecipientBCC', NULL , @VchrecipientBCC
--SI SE REQUIERE ENVIAR ADEMAS COPIAS DEL MENSAJE
IF NOT(@VchrecipientCC='')
EXEC @hr = sp_OAMethod @Intobject, 'AddRecipientCC', NULL , @VchrecipientCC
--PARA ENVIAR EL NOMBRE DE LA PERSONA QUE ENVIA EL MENSAJE
IF NOT(@Vchsendername='')
EXEC @hr = sp_OASetProperty @Intobject, 'SenderName', @Vchsendername
--EJECUTAMOS EL OBJETO CREADO PARA ENVIAR EL CORREO
EXEC @hr = sp_OAMethod @Intobject, 'execute', NULL
--CACHAMOS POSIBLES ERRORES
EXEC @hr = sp_OAGetErrorInfo @Intobject, @Vchsource OUT, @Vchdescription OUT
IF @hr <> 0
BEGIN
IF @@TRANCOUNT > 0 ROLLBACK TRAN
EXEC @hr = SP_OADestroy @Intobject
RAISERROR (@Vchdescription , 16, 1)
RETURN
END
--DESTRUIMOS EL OBJETO CREADO
EXEC @hr = SP_OADestroy @Intobject
COMMIT TRAN
GO
February 2, 2006 at 8:00 am
This was removed by the editor as SPAM
July 5, 2006 at 10:56 am
Yahoo and Hotmail are probably doing a reverse DNS look-up, which might fail depending on a few different things.
July 6, 2006 at 7:00 am
thanks.
November 14, 2006 at 11:08 am
This is really a good script. But for those of us who are Spanish impared, there is an English version on Dimac's web site. Here is the URL.
http://www.dimac.net/Products/w3JMail/Version37/Examples/sql.htm
The example is for JMail 3.7 but it wouldn't take much effort to adapt it for 4.1 or 4.5.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply