How do I write a procedure which sends E- mail and rest of the details

[shivalisv]

Hi

I have a SQl Query which returns rows with E-Mail address, username,some of other details. How do I write a procedure which sends E- mail to all the people returned by query and their rest of the details should be included in message part which should display in HTML format.

Below is the procedure which send E-mail to all the people returned by query (Query returns only E-mail address)

ALTER PROC uspSendThemMail
AS BEGIN
SET NOCOUNT ON

DECLARE @em nvarchar(100), @rc int

DECLARE test CURSOR
FOR select EmailAddress from temp where EmailAddress is not NUll
OPEN test
FETCH NEXT FROM test INTO @em

WHILE @@Fetch_Status = 0 BEGIN

EXEC @rc = master.dbo.xp_smtp_sendmail
@FROM = N'MyDomain@aa.com',
@TO = @em,
@priority = N'HIGH',
@subject = N'gdfgdfg',
@message = N'Goodbye MAPI, goodbye Outlook',
@type = N'text/plain',
@server = N'xxx'

SELECT Email = @em, RC = @rc

FETCH NEXT FROM test INTO @em
END

CLOSE test
DEALLOCATE test
END

Thanks
Shivali.

 

[mrdenny]

That should work fine for you. Are you getting any specific error when running this proc?

However Outlook has a hard time sending HTML email that looks correct via the xp_sendmail esp. Look for xp_smtp_sendmail on google. It's a dll that you can plug into sql as an esp. It will properly send html email, and it sends faster than xp_sendmail does.

Denny

--Anything is possible. All it takes is a little research. (Me)