Combine SQL Select With SP

I am looking for the correct T-sql statement. I want to put parameters in a SP from a select statement. And make the SP exec for each records that the select statement returns. The following is the SP that I want to pass the parameters.

CREATE PROCEDURE sp_sendSMTPmail (@To varchar(8000),
@Subject varchar(255),
@Body text = null,
@Importance int = 1, -- 0=low, 1=normal, 2=high
@Cc varchar(8000) = null,
@Bcc varchar(8000) = null,
@Attachments varchar(8000) = null, -- delimeter is ;
@HTMLFormat int = 0,
@From varchar(255) = null)

/* Name: sp_sendSMTPmail

Purpose: Send an SMTP mail using CDONTS object.

Requirements: SMTP server (comes with IIS but doesn't require IIS) must be loaded.

Returns: 0 if successful, 1 if any errors

Sample Usage:
sp_sendSMTPmail 'vince.iacoboni@db.com', 'Testing', 'testing sp_sendSMTPmail, please reply if you receive this',
@cc='irmsqlmail@db.com',
@Importance=1,
@Attachments='c:oot.ini;c:autoexec.bat'

History:
02/07/2001 VRI Created.
*/

AS

SET NOCOUNT ON

DECLARE @object int,
@hr int,
@StrEnd int,
@Attachment varchar(255),
@return int,
@Msg varchar(255)

SELECT @From = isnull(@From, @@SERVERNAME)

-- Create the CDONTS NewMail object.
EXEC @hr = sp_OACreate 'CDONTS.NewMail', @object OUT
IF @hr <> 0 GOTO ObjectError

-- Add the optional properties if they are specified
IF @Body IS NOT NULL
BEGIN
EXEC @hr = sp_OASetProperty @object, 'Body', @Body
IF @hr <> 0 GOTO ObjectError
END

IF @Cc IS NOT NULL
BEGIN
EXEC @hr = sp_OASetProperty @object, 'Cc', @Cc
IF @hr <> 0 GOTO ObjectError
END

IF @Bcc IS NOT NULL
BEGIN
EXEC @hr = sp_OASetProperty @object, 'Bcc', @Bcc
IF @hr <> 0 GOTO ObjectError
END

IF @HTMLFormat <> 0
BEGIN
EXEC @hr = sp_OASetProperty @object, 'MailFormat', 0
IF @hr <> 0 GOTO ObjectError
END

-- Loop through the ; delimited files to attach
CREATE TABLE #FileExists (FileExists int, FileIsDir int, ParentDirExists int)

WHILE isnull(len(@Attachments),0) > 0
BEGIN
SELECT @StrEnd = CASE charindex(';', @Attachments)
WHEN 0 THEN len(@Attachments)
ELSE charindex(';', @Attachments) - 1
END
SELECT @Attachment = substring(@Attachments, 1, @StrEnd)
SELECT @Attachments = substring(@Attachments, @StrEnd+2, len(@Attachments))

-- Ensure we can find the file we want to send.
DELETE #FileExists
INSERT #FileExists
EXEC master..xp_fileexist @Attachment

IF NOT EXISTS (SELECT * FROM #FileExists WHERE FileExists = 1)
BEGIN
RAISERROR ('File %s does not exist. Message not sent.', 16, 1, @Attachment)
RETURN 1
END

EXEC @hr = sp_OAMethod @object, 'AttachFile', NULL, @Attachment
IF @hr <> 0 GOTO ObjectError

SELECT @Msg = 'File ' + @Attachment + ' attached.'
PRINT @Msg
END

-- Call the Send method with parms for standard properties
EXEC @hr = sp_OAMethod @object, 'Send', NULL, @From, @To, @Subject, @Importance=@Importance
IF @hr <> 0 GOTO ObjectError

-- Destroy the NewMail object.
EXEC @hr = sp_OADestroy @object
IF @hr <> 0 GOTO ObjectError

PRINT 'Message sent.'
RETURN 0

ObjectError:
BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN 1
END



GO