how to send result set of a stored procedure as an automatic email using sql server 2005

You can configure Database Mail feature of Sql server and use it for sending the mails directly from stored procedures. The feature is just a replacement of SqlMail feature in previous versions.
For using the feature you need to :

1. Create a profile and account using the Configure Database Mail Wizard which can be accessed from the Configure Database Mail context menu of the Database Mail node in Management Node.

2. After the Account and the Profile are created successfully, we need to configure the Database Mail. To configure it, we need to enable the Database Mail XPs parameter through the sp_configure stored procedure, as shown here:

sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO

3. We  need to execute a stored procedure sp_send_dbmail and provide the required parameters as shown below:

USE msdb
GO
EXEC sp_send_dbmail @profile_name='YourProfile (as created in step 1)',
@recipients='[email protected]',
@subject='Test message',
@body='<>'

and you are done.