February 28, 2014 at 11:54 am
Hi
What I would like to do is email users who end up on a report
for example if a user has not had "X" to 80 days (their email exists on a table)
I would like to email each line of output
for example
output looks like:
J Jones jjones@companya.com
A Allen aalen@companyb.com
etc..
etc..
thus sending on X number of emails depending on output
Would it be a better question in the SSRS section?
Thanks
Joe
February 28, 2014 at 12:40 pm
Ok so with a bit of searching I came up with this which sort of works
But I need to expand my select statement to find the records I need and not just one user
DECLARE @MailqueueID int;
DECLARE @FromName VARCHAR(max);
DECLARE @FromAddress VARCHAR(max);
DECLARE @ToName VARCHAR(max);
DECLARE @ToAddress VARCHAR(max);
DECLARE @varSubject VARCHAR(max);
DECLARE @varBody VARCHAR(max);
DECLARE @HasSent int;
DECLARE @DateStamp Datetime;
set @ToAddress = (SELECT username from table
WHERE username = 'mickeymouse')+'@companyname.org'
BEGIN
EXEC msdb.dbo.sp_send_dbmail @recipients=@ToAddress,
@from_address = @FromAddress,
@subject = 'test',
@body = 'testbody',
@body_format = 'HTML'
END
SET NOCOUNT OFF;
February 28, 2014 at 1:15 pm
SO I'm a bit further....
But it only select the last one..
Anyway to get it to send an email to each one it finds?
Thanks
DECLARE @ToAddress VARCHAR(max);
DECLARE @varSubject VARCHAR(max);
DECLARE @varBody VARCHAR(max);
DECLARE @HasSent int;
DECLARE @DateStamp Datetime;
BEGIN
select @ToAddress = username
+ '@company.org'
from Table
where UserName = 'name1' or UserName = 'name2'
EXEC msdb.dbo.sp_send_dbmail @recipients=@ToAddress,
@from_address = @FromAddress,
@subject = 'test',
@body = 'testbody',
@body_format = 'HTML'
END
SET NOCOUNT OFF;
February 28, 2014 at 3:27 pm
Throw the results into a parameter table, then loop through the table and send the email to each recipient - this is a very rough example but should get you going in the right direction (It will send an email to every person in the temp table): DECLARE @ToAddress VARCHAR(150), @FromAddress varchar(150),
@varSubject VARCHAR(max),
@varBody VARCHAR(max),
@HasSent int,
@DateStamp Datetime,
@Cnt INT = 1,
@Idx int
DECLARE @EmailIst TABLE (id idenitity(1,1), username VARCHAR(50))
BEGIN
SET @FromAddress = 'you@company.com'
INSERT INTO @EmailIst
SELECT username + '@company.org' FROM table
WHERE UserName = 'name1' or UserName = 'name2'
WHILE (SELECT COUNT(1) FROM @EmailIst) <> 0
BEGIN
SELECT TOP 1 @Idx = Id, @ToAddress = username FROM @EmailIst ORDER BY id
EXEC msdb.dbo.sp_send_dbmail @recipients=@ToAddress,
@from_address = @FromAddress,
@subject = 'test',
@body = 'testbody',
@body_format = 'HTML'
DELETE FROM @EmailIst WHERE id = @ID
END
END
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply