Wrox Programmer Forums
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old February 21st, 2006, 05:38 PM
Registered User
Join Date: Feb 2006
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default sendEmail Function in DTS

In lieu of using MAPI mail to send a user confirmation email, I have opted to use the sendEmail function of sql mail subsystem. I can send a text email no problem, but I now want to send an HTML email but am having a bit of difficulty. The pickup directory path is at the bottom of the code below, and I have changed the type of email from text to html, but everytime I put an HTML doc in the pickup directory, it immediately sends doc somewhere, but I'm not sure where. Can someone advise me on the steps to send an HTML email using the sendEmail function, as shown below?? thanks in advance.

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()
    Main = DTSTaskExecResult_Success

set myConn = CreateObject("ADODB.Connection")
myConn.open "Provider=SQLOLEDB;Data Source=;Initial Catalog=;User ID=;password="

set myRS = myConn.execute("select advertiseremail, advertisername, accountnumber from table where Ademail is not null ")

if not myRS.eof then
    do while not myRS.eof
            call SendEmail("test@test.com",myRS("AdEmail"), "Listing.", "Dear " & myRS("advertisername") & vbcrlf & vbcrlf &"Thank you for placing a listing in the classifieds. As a part of your advertising package, your listing will be displayed on our condo internet marketing portal<http://development.test.com>, for XX days." & vbcrlf & vbcrlf & "As part of your advertising package, the price of your this listing, you are entitled to modify and enhance your listing with Customer Support Team" & vbcrlf &"test.test.com" & vbcrlf & "xxx.xxx.xxxx")
end if

set myRS = nothing

End Function

Function SendEmail(sender,recipient,subject,body)

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

    Set Flds = iConf.Fields
    With Flds
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 1
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverpickupdirectory")="c:\inetpub\mailroot\p ickup"
    End With

    With iMsg
        Set .Configuration = iConf
        .To = recipient

        .From = sender
        .Subject = subject
        .htmlBody = body
    End With

    set iMsg = nothing
    set iConf = nothing

end function

Similar Threads
Thread Thread Starter Forum Replies Last Post
send variable in function to another function schoolBoy Javascript How-To 6 March 3rd, 2007 09:16 AM
How to call javascript function from VB function vinod_yadav1919 VB How-To 0 February 13th, 2006 06:03 AM
dts calls a lot of dts steffi_ma SQL Server DTS 2 May 24th, 2004 07:18 PM
retreive function/Line from macro or function? MikoMax J2EE 0 April 1st, 2004 04:42 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.