How can you send mail from a procedure with an attachment?

Mail can be sent via SQL server only if SMTP is set on the server. Post that you can send the mails as the normal mail also send the data from SQL server as and attached file via mail

create proc usp_send_user_mail_with_attachment

as

Begin

DECLARE @V_MESSAGE NVARCHAR(MAX), @V_subject NVARCHAR(MAX) ,@V_query VARCHAR(50) ;

set @V_mysubject =N' Your mail send from server: '+@@SERVERNAME+' in Database: '+DB_name()

SET @V_MESSAGE =N' Your e-mail body will be as you from a table/ customized’

set @V_query =N'sp_who'

EXEC msdb.dbo.sp_send_dbmail

@profile_name = '@profile_name of your server', --Mandatory

@recipients = ‘to email ids with semicolon separated', --Mandatory

@copy_recipients =‘CC email ids with semicolon separated', --only if any email exists

@blind_copy_recipients= 'BCC email ids with semicolon separated', --only if any email exists

@body = @ERROR_MESSAGE,--Mandatory

@subject = @V_subject , --Mandatory

@query = @V_query, --only if need to send a attachment

@attach_query_result_as_file = 1,--only if need to send a attachment

@body_format = 'HTML' ;--Mandatory

end