XPSMTP (cross) -- sql server programming

Prasad Koukuntla
3/10/2004 4:59:40 PM

Sokrates,
Use the following as an example. This stored procedure uses the email
addresses from my database and sends email using xp_smtp_sendmail .
HTH
Prasad Koukuntla.

CREATE PROC SP_XXXXXXX
AS
SET NOCOUNT ON

/**Get the all the new contacts that qualify**/
DECLARE @LastName varchar(100), @FirstName varchar(100), @Company
varchar(150), @NEXT_KEY int,
@SalesRep varchar(100), @EmailAddress varchar(150), @Listing_ID int,
@msg nvarchar(4000),
@From varchar(400), @FromName varchar(400), @ReplyTo varchar(300),
@BCC varchar(300),
@RecepientList varchar(400), @Subject varchar(300), @RC int,
@Domain_name varchar(200), @body varchar(3000),
@title varchar(80), @header varchar(10), @signature varchar(10)

SET @BCC = 'mailers@xxxxxx.com'
SET @Subject = 'Thank you for your interest in XXX'
SET @Domain_name = '@xxxxx.com'

SET @body = N'Thank you for your interest in XXXX. '
SET @body = @body + N'Please feel free to use <a href="http://www.xxxxx.com"
[quoted text, click to view]
SET @body = @body + N'as a resource for promotion ideas. '

SET @body = @body + N'<a href="http://www.xxxxxxx.com/subscribe">'
SET @body = @body + N'Click here</a> to subscribe to our quarterly
newsletter '
SET @body = @body + N'and to receive great promotion ideas targeted to your
industry via '
SET @body = @body + N'mail or e-mail.'
SET @body = @body + N'If there is anything else '
SET @body = @body + N'I can do to assist you, please feel free to contact me
anytime. '
SET @body = @body + N'I look forward to working with you in the
future.Best regards,'

DECLARE SEND_EMAIL CURSOR
FOR
SELECT
LAST_NM LastName,
FIRST_NM FirstName,
COMPANY_NM Company,
e.lst_custom_enum_nm SalesRep,
iae.ELEC_ADDR EMailAddress,
i.Listing_ID
FROM INT_AUX_LISTING I LEFT JOIN INT_AUX_LST_CUSTOM lc
LEFT JOIN
INT_LST_CUSTOM_DEF c ON
(c.lst_custom_def_id = lc.lst_custom_def_id)
LEFT OUTER JOIN
INT_LST_CUSTOM_ENUM e ON
(c.lst_custom_def_id = e.lst_custom_def_id and

e.lst_custom_enum_id=lc.lst_custom_enum_id)
ON (lc.listing_id = i.listing_id and c.label
= '1. Sales Rep 1')
LEFT OUTER JOIN (INT_AUX_LST_EADDR ea
inner join INT_AUX_ELEC_ADDR iae on
(iae.ELEC_ADDR_ID = ea.ELEC_ADDR_ID and ELEC_ADDR_TYP_ID = 1))
ON i.listing_id = ea.listing_id and
ea.rltn_typ_id = 1
LEFT JOIN INT_AUX_LST_CUSTOM lc2 left JOIN
INT_LST_CUSTOM_DEF c2
ON
(c2.lst_custom_def_id = lc2.lst_custom_def_id)
left JOIN
INT_LST_CUSTOM_ENUM e2
ON
(c2.lst_custom_def_id = e2.lst_custom_def_id and

e2.lst_custom_enum_id=lc2.lst_custom_enum_id and e2.LST_CUSTOM_ENUM_NM NOT
IN ('Delete Account & All Contacts', 'Delete Contact', 'Inactive',
'Archive'))
ON (lc2.listing_id = i.listing_id and
c2.label = '7. status')

WHERE I.LISTING_TYP_IND = 0 --Person contacts only
AND CONVERT(VARCHAR(12), I.CREATE_DT, 101) = CONVERT(VARCHAR(12),
GETDATE(), 101)
AND i.COMPANY_NM NOT LIKE 'XXX%'
AND iae.ELEC_ADDR IS NOT NULL
AND (e.lst_custom_enum_nm NOT IN ('Non-Sales', 'Unassigned' ) --Exclude
these SalesReps
OR e.lst_custom_enum_nm IS NULL) AND NOT EXISTS (SELECT * FROM
INT_AUX_DIR_LIST e
WHERE CONTAIN_DIR_ID = 137 --Auto Email (Do Not Send)
and e.listing_id = I.listing_id)
AND not exists (select * from INT_AUX_LST_CUSTOM l
WHERE l.LISTING_ID = i.LISTING_ID
AND LST_CUSTOM_DEF_ID = 12 AND LST_CUSTOM_ENUM_ID =
237) --Request to be removed from Mailings-E-Mail
AND not exists (select * from INT_AUX_LST_CUSTOM l
WHERE l.LISTING_ID = i.LISTING_ID
AND LST_CUSTOM_DEF_ID = 12 AND LST_CUSTOM_ENUM_ID =
341) --Request to be removed from Mailings-ALL
and exists (select *
from INT_AUX_DIR_LIST e1
where OWN_DIR_ID = -2 --search only in people public folder
and e1.listing_id = I.listing_id)
and iae.ELEC_ADDR like '%_@%'

OPEN SEND_EMAIL

FETCH NEXT FROM SEND_EMAIL INTO @LastName, @FirstName, @Company, @SalesRep,
@EmailAddress, @Listing_ID

WHILE (@@FETCH_STATUS = 0)
BEGIN

IF @SalesRep IS NOT NULL
BEGIN
SET @FromName = @SalesRep
SET @From = REPLACE(@SalesRep, ' ' , '.') + @Domain_name

SELECT @header = HeaderFile,
@signature = SignatureFile
FROM JpgFileNames
WHERE SalesrepName = @SalesRep

set @msg = N'<HTML><img
src="http://www.xxxxx.com/email/images/' + ISNULL(@header, '') + '">
'--LOWER(Left(@SalesRep, CHARINDEX(' ', @SalesRep) - 1)) + 'header.jpg"> '
set @msg = @msg + N'Dear ' + @FirstName + ', ' + ''
set @msg = @msg + @body
set @msg = @msg + N'<img
src="http://www.xxxxxx.com/email/images/sigs/' + ISNULL(@signature, '') +
'"> ' --LOWER(LEFT(@SalesRep, 1) + SUBSTRING(@SalesRep, CHARINDEX(' ',
@SalesRep) + 1, 1)) + '.jpg" >'
set @msg = @msg + N'' + @SalesRep + ''
set @msg = @msg + @title + N'</HTML>'

END
ELSE
BEGIN

SET @FromName = 'Your Company name here'
SET @From = 'mailers@xxxx.com'

set @msg = N'<HTML><img
src="http://www.xxxxxxxxx.com/email/images/xx.jpg"> '
set @msg = @msg + N'Dear ' +
@FirstName + ', ' + ''
set @msg = @msg + @body
set @msg = @msg + N'Your company
here.</HTML>'
END

IF (@EmailAddress LIKE '%.')
SET @EmailAddress = LEFT(@EmailAddress, LEN(@EmailAddress)-1)

SET @RecepientList = ISNULL(REPLACE(@FirstName, ',',' '), '') + ' ' +
ISNULL(REPLACE(@LastName, ',',' '), '') + ' <' + @EmailAddress + '>'
SET @ReplyTo = @From

/***Send the email to recepients**/
EXEC @rc = master.dbo.xp_smtp_sendmail
@FROM = @From,
@FROM_NAME = @FromName,
@TO = @RecepientList,
@BCC = @BCC,
@replyto = @ReplyTo,
@subject = @Subject,
@message = @msg,
@type = N'text/html',
@server = N'XXX.XXX.XXX.XXX'
FETCH NEXT FROM SEND_EMAIL INTO @LastName, @FirstName, @Company,
@SalesRep, @EmailAddress, @Listing_ID
END
CLOSE SEND_EMAIL
DEALLOCATE SEND_EMAIL
GO

[quoted text, click to view]

\
3/10/2004 11:32:31 PM

Hi,

I'm using the basic functions of XPSMTP http://sqldev.net/xp/xpsmtp.htm -
and it's a nice tool, however, I would like to send emails to people
(emailaddresses) from out database, but I can't see how to set it up.

The company I work for sells hardware over the Internet and every evening I
would like to send out track&trace-info on packages send the current day.

The thing I can't work out is how to "roll" though the email addresses in
our database and return them into the @TO variable.

Each (text) email has to hold info that I can make from a pretty simple
query - the following is a part of the info I can extract:

OrderNumber
InvoiceNumber
Date/TimeOfDelivery
Number of packages
TrackTraceNumber
etc.

But each mail has to hold some standard text ("Dear X, thank you bla bla
bla - yor track & trace number is X...." etc).

Can somebody help? Can I merge the XPSMTP into a SQL query...?

Thanx
Jakob

\
3/11/2004 12:59:04 PM

Wow - that is pretty advanced in my eyes...
Does it create a Stored Procedure? This has to run only once right?

Could you (or somebody else) show me how to do it from a table ORDERS
containing
ORDERNO, INVOICENO, TRACKNTRACE, EMAIL, COMPANYNAME

Just a simple email with one of the above cols in the email subject or
body...?

Pleeease ;)
Jakob

"Prasad Koukuntla" <prasad.koukuntla@scapromo.com> skrev i en meddelelse
news:%23SeCgNvBEHA.2380@TK2MSFTNGP10.phx.gbl...
[quoted text, click to view]

\
3/12/2004 12:07:33 AM

WOW,

This has really been a GREAT experience reading the code you send, thanx
alot! I went through it line after line a dusin of times, and now I get most
of it - it's kinda beautiful actually...

I finally get it - the store procedure thing, the cursor and so on - I
really like it, and I must say - this opens up thousands of possibilities!!!

The best thing is, that I learned a few things - but futher more, I created
a SP that saves us and our customers lots of lots of time and trouble!

Again - Thanx alot!

Best regards
Jakob

""Sokrates"" <somebody@somewhere.earth> skrev i en meddelelse
news:OIy0BB2BEHA.3804@TK2MSFTNGP09.phx.gbl...
[quoted text, click to view]

Prasad Koukuntla
3/12/2004 9:58:36 AM

Jakob,
I am glad that I could help.
Prasad.

[quoted text, click to view]

\
3/14/2004 2:13:48 AM

Perhaps you can help me a bit more...?

I'm doing something like the following, but I need to insert a few
functions.

1) I would like to be able to insert each orderline from our
INVOICELINEARCHIVE in the email text below the T&T number - like:
*Pseudo*
For Each Line in INVOICELINEARCHIVE, Where INVOICELINEARCHIVE.NUMBER =
@OrderNumber
Print OrderText, OrderAmount, OrderPrice
Next
- or this might be done by a SELECT ? Or another Cursor somehow?
- I have used the SELECT with XPSMTP before, like:
SELECT @message = @message + '<TR><TD>' + ORDERHEADARCHIVE.NUMBER +
'</TD><TD>' + ORDERHEADARCHIVE.NAME + '</TD></TR>'
FROM ***
WHERE ***
(A) Tried something like this by comparing the variable @OrderNumber with my
ORDERHEADARCHIVE.ORDERNUMBER in the WHERE statement, but it wouldnt work...

2) I would further more like to be able to insert multipe T&T numbers into
the same email - this is when an order is split over more than one package.

This is what I have right now:
--
CREATE PROC SP_TRACKNTRACE
AS
SET NOCOUNT ON

DECLARE @CustomorName varchar(100), @OrderNumber varchar(10), @InvoiceNumber
varchar(20), @EmailAddress varchar(150), @TrackTraceNumber varchar(20),
@NEXT_KEY int,
@message varchar(4000), @msg nvarchar(4000), @From varchar(400), @FromName
varchar(400), @ReplyTo varchar(300), @BCC varchar(300), @RecepientList
varchar(400),
@Subject varchar(300), @RC int, @body varchar(3000), @title varchar(80),
@header varchar(10), @signature varchar(10)

SET @BCC = 'netsales@domain.com'
SET @Subject = 'Deliverystatus - Track & Trace'

DECLARE SEND_EMAIL CURSOR
FOR
SELECT INVOICEHEADARCHIVE.NAME,
INVOICEHEADARCHIVE.EMAIL,
IT_TRACKNTRACE.ORDERNUMBER,
IT_TRACKNTRACE.INVOICENO,
IT_TRACKNTRACE.TRACKNTRACE

FROM INVOICEHEADARCHIVE,IT_TRACKNTRACE

WHERE INVOICEHEADARCHIVE.EMAIL like '%_@_%.__%'
AND IT_TRACKNTRACE.TRACKNTRACE <> ''
AND ISNUMERIC(IT_TRACKNTRACE.INVOICENO) > 0
AND INVOICEHEADARCHIVE.INVOICENO = CONVERT(INT,IT_TRACKNTRACE.INVOICENO)
AND INVOICEHEADARCHIVE.INVOICEDATE > DATEADD(DAY,-1,GETDATE())

OPEN SEND_EMAIL

FETCH NEXT FROM SEND_EMAIL INTO @CustomorName, @EmailAddress, @OrderNumber,
@InvoiceNumber, @TrackTraceNumber

WHILE (@@FETCH_STATUS = 0)
BEGIN
BEGIN
SET @FromName = 'Company Name'
SET @From = 'netsales@domain.com'
SET @msg = N'<HTML>Dear ' +
@CustomorName + ','
SET @msg = @msg + N' '
SET @msg = @msg + N'Today we send you ordernumber ' + @OrderNumber + ' ,
invoicenumber ' + @InvoiceNumber + '.'
SET @msg = @msg + N' '
SET @msg = @msg + N'Your package has Track & Trace number: ' +
@TrackTraceNumber + '.'
--** This is where I wuld like to add all T&T numbers concerning the order

SET @msg = @msg + N' '

--** This is where I would like some lines from a table by a SELECT query or
something like it (A)

SET @msg = @msg + N'Best Regards'
SET @msg = @msg + N' '
SET @msg = @msg + N'My Company Name'
SET @msg = @msg + N' '
SET @msg = @msg + N'<a href="http://www.domain.com">www.domain.com</a>'
SET @msg = @msg + N'</HTML>'
END

SET @ReplyTo = @From

EXEC @rc = master.dbo.xp_smtp_sendmail
@FROM = @From,
@FROM_NAME = @FromName,
@TO = @EmailAddress,
@BCC = @BCC,
@replyto = @ReplyTo,
@subject = @Subject,
@message = @msg,
@type = N'text/html',
@server = N'192.168.0.1'

FETCH NEXT FROM SEND_EMAIL INTO @CustomerName, @EmailAddress,
@OrderNumber, @InvoiceNumber, @TrackTraceNumber
END
CLOSE SEND_EMAIL
DEALLOCATE SEND_EMAIL
---

Thanx
Jakob

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : XPSMTP (cross)