declare @planname varchar(100)
declare @dir varchar(200)
declare @operator varchar(50)
declare @cmd varchar (200)
declare @mailfilename varchar(200)
declare @filenamelen int

--Values set here can actually be provided as parameters to a stored procedure.
--If provided as parameters to a stored procedure, rem the following select statements.
--@plananme is the plan whose maintenance report is sent.
--@dir is the log directory for SQL Server. It is the directory to which the 
--maintenance report files are written. 
--@operator is the email address of the person to whom the report file should be mailed.

select @planname = 'Database Maintenance Plan 1'
select @dir ='c:\Program Files\Microsoft SQL Server\MSSQL$SQL2K1\LOG'
select @operator ='email@domain.com'
--You can automatically set the above by reading various values from SQL Server.

SET NOCOUNT ON
IF RIGHT (@dir, 1) <> '\' 
begin
select @dir =@dir +'\'
end
SELECT @dir = 'dir /s /b '+'"'+@dir + @planname+'*.txt'+'"' +' >c:\dir.txt'

create table #TMP_MAINT_FILENAMES (NAME1 varchar(8000))
exec xp_cmdshell @dir
BULK INSERT #TMP_MAINT_FILENAMES
   FROM 'c:\dir.txt'
   WITH 
      (
         ROWTERMINATOR = '\n'
      )

select @mailfilename=MAX(name1) from #TMP_MAINT_FILENAMES
print 'The following file is being sent as an atachement'
print @mailfilename

--Set the various parameters for xp_sendmail.
declare @tmpmessage varchar(300)
declare @tmpsubject varchar(300)
select @tmpmessage = 'This is the last maintenance report on the server for the maintenance plan '+@planname
select @tmpsubject = 'SQL Server Maintenance Report for '+@planname

--Send the last file for the maintenance plan.
exec master..xp_sendmail @recipients= @operator, @subject =@tmpsubject, @message=@tmpmessage, 
@attachments= @mailfilename

--Perform cleanup here.
drop table #TMP_MAINT_FILENAMES
exec master..xp_cmdshell "del c:\dir.txt"
SET NOCOUNT OFF