I'm trying to get this query to send an email to a defined group either A,B,or C if the result set is greater then 1.
I continue to have issues with the recipient and with the URL I try to define. Any help would be appreciated.
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'administrator',
@recipients = 'user@domain.com',
@query = 'Select
Owner = a1.User_Display
,Recipient = Case When Left(ReqOwner_TicketNum,2) = 'MN' Then 'GroupA@domain.com' When Left(ReqOwner_TicketNum,4) = 'ITON' Then 'GroupB@domain.com' Else 'GroupC@domain.com' End
,Creator = a2.User_Display
,Status = Status_Name
,Ticket = ReqOwner_TicketNum
,Subject = ReqOwner_Subject
,URL = 'Http://Sitename/Support.aspx?id=' + Convert(varchar(10),ReqOwner_PK)
from ReqOwner
Inner Join AppUser a1 on ReqOwner_UserFK = a1.User_PK
Inner Join AppUser a2 on ReqOwner_AssignBy= a2.User_PK
Inner Join Status on Status_PK = ReqOwner_StatusFK
Where ReqOwner_Priority = 3 and ReqOwner_IsComplete=0 and
ReqOwner_Interested=0 and dateDiff(hour,ReqOwner_AssignDate, GetDate() - 2) > 1'
@subject = 'Need To RESOLVE Issue',
@attach_query_result_as_file = 1 ;