T-SQL - Send mail driven by email ids from table

It is certainly possible to send emails using T-SQL by using sp_send_dbmail stored procedure which is present in msdb database. The earlier way of sending mail was by using SQL Mail. This is now being replaced with Database Mail in SQL Server 2005.

Step 1: Configure your smtp server. For this locate the Database Mail by connecting to the management studio Database engine.

Step 2: There will be a wizard opened for configuring the Database mail. Click appropriate radio button and click next. Here, we are creating a new profile so select first option.






Step 3: Configure your smtp server by entering the credentials and other required information. I have tested this piece using Gmail smtp configuration. Remember the account name. It will be used as the Profile name when using a TSQL query.

Step 4: Call the inbuild msdb.dbo.sp_send_dbmail stored procedure to send emails in our query. Assuming that the email ids are present in a table called ‘email_user’, the query sends mail to all the ids present in this table. There can be multiple conditions or joins applied to restrict the users. The resultset of a table is exported to a comma separated file with names based on current date and time.

A detailed help on sp_send_dbmail is available at

http://msdn.microsoft.com/en-us/library/ms190307.aspx

====================================================

Query:

declare @filename varchar(max)

declare @filepath varchar(max)

declare @emailids varchar(max)

declare @currDate datetime

set @emailids=''

set @currDate=getdate()

set @filepath='C:\\'

set @filename=@filepath+convert(varchar,Year(@currDate))+

convert(varchar,month(@currDate))+

convert(varchar,day(@currDate))+

convert(varchar,datepart(hh,@currDate))+

convert(varchar,datepart(mi,@currDate))+

convert(varchar,datepart(ss,@currDate))+'.txt'

select @emailids=@emailids+';'+email_address from test.dbo.email_user

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Your Profile',

@recipients = @emailids,

@subject = 'Sending Mail using Database Mail' ,

@body = 'Just testing the mail',

@query='SELECT convert(varchar(max),[EmployeeID])+'',''+

convert(varchar(max),[NationalIDNumber])+'',''+

convert(varchar(max),[ContactID])+'',''+

convert(varchar(max),[LoginID])+'',''+

convert(varchar(max),[ManagerID])+'',''+

convert(varchar(max),[Title])+'',''+

convert(varchar(max),[BirthDate])+'',''+

convert(varchar(max),[MaritalStatus])+'',''+

convert(varchar(max),[Gender])+'',''+

convert(varchar(max),[HireDate])+'',''+

convert(varchar(max),[SalariedFlag])+'',''+

convert(varchar(max),[VacationHours])+'',''+

convert(varchar(max),[SickLeaveHours])+'',''+

convert(varchar(max),[CurrentFlag])+'',''+

convert(varchar(max),[rowguid])+'',''+

convert(varchar(max),[ModifiedDate])

FROM [AdventureWorks].[HumanResources].[Employee]',

@attach_query_result_as_file = 1,

@query_attachment_filename= @filename,

@query_no_truncate=1