How to send E-Mail through Stored procedure for multiple people

[shivalisv]

I have a query which gives set of rows containing Email-Id with respective Names.

Below is stored procedure which sends mail to 1 person at a time.
declare @rc int
exec @rc = master.dbo.xp_smtp_sendmail
@FROM = N'MyUsername@xxx.com',
@TO = N'FriendsUsername@yyy.com',
@priority = N'HIGH',
@subject = N'Hello SQL Server SMTP Mail',
@message = N'Goodbye MAPI, goodbye Outlook',
@type = N'text/plain',
@server = N'aaaaa'
select RC = @rc


My question is how do I write a stored procedure which sends mail to all the people who are in there result set of query with one person at a time using the above stored procedure

Thanks
Shivali

 

[JohnDTampaBay]

You will need to write a procedure that creates a cursor from the query. Loop through the cursor, fetching each person and executing xp_sendmail. Something like this:

CREATE PROC uspSendThemMail
AS BEGIN
  SET NOCOUNT ON

  DECLARE @em nvarchar(100), @rc int

  DECLARE YourCursor CURSOR
  FOR SELECT EmailAddress FROM ...

  OPEN YourCursor
  FETCH NEXT FROM YourCursor INTO @em

  WHILE @@Fetch_Status = 0 BEGIN

    EXEC @rc = master.dbo.xp_smtp_sendmail
      @FROM       = N'MyUsername@xxx.com',
[COLOR=blue]      @TO         = @em[/color],
      @priority   = N'HIGH',
      @subject    = N'Hello SQL Server SMTP Mail',
      @message    = N'Goodbye MAPI, goodbye Outlook',
      @type       = N'text/plain',
      @server     = N'aaaaa'

    SELECT Email = @em, RC = @rc

    FETCH NEXT FROM YourCursor INTO @em

  END

  CLOSE YourCursor
  DEALLOCATE YourCursur
END

Good luck!

--Angel
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[shivalisv]

HI
Thanks, But the code gets exec only for 1 person, it selects 1 person's email randomly out of the list of rows
Could you just tell me why its happening ?

Thanks
Shivali.

 

[JohnDTampaBay]

I would check the SELECT statement that defines the cursor. If there is only one address SELECTed, only one address will be emailed. Also, ensure that the second FETCH statement appears within the WHILE block. If the second FETCH doesn't exist, only the first addresses SELECTed will be emailed.

--Angel
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)