Send Email

Question

Send Email

Hari Takella-174750

SSC Enthusiast

Points: 191
More actions
October 5, 2004 at 8:45 am

#60320

Hi,
I have an issue here. I need to send an email attachign a file using a DTS package which runs weekly. Also the To, From and Subject and body need to be extracted from the database and passed across to the Send Email Task in SQL DTS.
Can somebody please tell me the sequence of steps of point to me any code which handles this.
Thank you for your help,
Hari

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Brian Cidern SSC Veteran Points: 263 More actions · Answer 1

I don't have SQL Mail installed, nor will my IT admins allow me to install and configure it. Here is my work around in an ActiveX Script Task. You may need to may need to create an ADODB or OLDEB connection in your script to grab the data from the database, or you might be able to pass it into the Script Task via global variables.

' VBScript source code
Sub sendCDOMail(sz_to, sz_from, sz_subject, sz_body)
    Dim iMsg, iConf, Flds

    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")

    Set Flds = iConf.Fields

    ' Set the CDOSYS configuration fields to use port 25 on the SMTP server.
    With Flds
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "YOUR.DOMAIN.COM"
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 10
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 26
        ' Our internal port uses 26.  Yours will most likely be different.
        .Update
    End With

    ' Apply the settings to the message.
    With iMsg
        Set .Configuration = iConf
        .To = sz_to
        .From = sz_from
        .Subject = sz_subject
        .HTMLBody = sz_body        'For HTML formatted email.
        ' or .TextBody = sz_body   'for plain text.
        .Send
    End With

    ' Clean up variables.
    Set iMsg = Nothing
    Set iConf = Nothing
    Set Flds = Nothing
End Sub

Metra Christofferson Ten Centuries Points: 1153 More actions · Answer 2

For a purely DTS solution, you can also use the stored procedure 'sp_send_cdosysmail_attachment' which can be found on the microsoft knowldegebase, and you use it in an execute sql task in your dts package as follows:

........begin body of execute sql task.........

declare @Body varchar(4000)

select @Body = 'Update Succeeded -- See Attached for Details'

--the rest of this is one one line

exec sp_send_cdosysmail_attachment '<sendername>','<comma delimited resolved email list>', '<email subject>', @Body, '<path to attachment>'

........end body of execute sql task.........

I usually use the server name for the sender's name, and you need to be sure that the path to the attachment is one that can be reached both by you and by the server when you aren't logged in. I usually use the UNC path instead of drive letters just to be safe.

Metra

Hari Takella-174750 SSC Enthusiast Points: 191 More actions · Answer 3

Metra,

thank you for your advice on my issue , but is there a way that we can speicfy the FROM using the stored procedure

and if yes then does it look like it is comming from that FROM address.

Are there any restrictions if we use this sp_sendmail ..stored procecure.

Thanks for your help

HAri

Hari Takella-174750 SSC Enthusiast Points: 191 More actions · Answer 4

Metra,

I did not find any information abt the stored procedure that you have referenced in Microsoft KB.

Please send me the link if you have!!

Hari

Metra Christofferson Ten Centuries Points: 1153 More actions · Answer 5

I posted a long message on this yesterday, but it isn't showing up.

Here's the sp itself, you have to dig through Microsoft to find sp_send_cdosysmail and then add the attachment yourself looking at other implementations of cdo to figure it out...:

CREATE PROCEDURE [dbo].[sp_send_cdosysmail_attachment]

@From varchar(100) ,

@To varchar(100) ,

@Subject varchar(100)=" ",

@Body varchar(4000) =" ",

@Attach varchar(300)= null

/*********************************************************************

This stored procedure takes the parameters and sends an e-mail.

All the mail configurations are hard-coded in the stored procedure.

Comments are added to the stored procedure where necessary.

References 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("<A href="http://schemas.microsoft.com/cdo/configuration/sendusing&quot.Value','2'">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("<A href="http://schemas.microsoft.com/cdo/configuration/smtpserver&quot.Value'">http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', '<servername>

-- 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_OAMethod @iMsg, 'AddAttachment', @attach --,' ', ' '

EXEC @hr = sp_OAMethod @iMsg, 'Attach_Results', True

EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body

EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- Sample error handling.

IF @hr <>0

select @hr

BEGIN

EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT

IF @hr = 0

BEGIN

SELECT @output = ' Source: ' + @source

PRINT @output

SELECT @output = ' Description: ' + @description

PRINT @output

END

ELSE

BEGIN

PRINT ' sp_OAGetErrorInfo failed.'

RETURN @output

END

-- Do some error handling after each step if you have to.

-- Clean up the objects created.

EXEC @hr = sp_OADestroy @iMsg

GO

I hope this is useful

Metra

andyobl Right there with Babe Points: 767 More actions · Answer 6

Hi Metra,

Thanks for the attachment bit. I have been pulling my hair out on how to do the attachment.

However, there is a mistake in line

>> EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment', @attach --,' ', ' '

It should be

EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment', NULL, @attach --,' ', ' '

Otherwise attachment won't be sent. I have tested this many times over and found that to be case. Can you confirm?

Regards,

Andy