I have a trigger in place that sends an email on insert. I am wanting to create a second email when a date field equals today. From what I can tell the only way to accomplish this would be a sql job? Creating the job looks pretty easy through management studio, but I don't know T-SQL very well. Below is the exact code I am using for the trigger. I am also including a quick snippet of code for what needs to be searched for in the records to send emails on (one email per record found). If anyone can help with converting this code over to what needs to be pasted into the SQL Job step it would be greatly appreciated.
// Code to find records
Select * from dbo.Tasks where DueDate=GetDate() AND CompletedDate IS NULL
// Code from trigger that needs to be converted to loop through found records and send one email for each record found
/****** Object: Trigger [dbo].[t_NewTaskEmail] Script Date: 11/10/2015 5:28:47 PM ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
-- Create date:
ALTER TRIGGER [dbo].[t_NewTaskEmail]
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
@AssignedToWorkEmail = AssignedTo.WorkEmail,
@CreatedByAgent = i.Priority,
@EmailSubject =isnull(CreatedBy.Agent,'N/A')+' assigned you a new task!',
@EmailBody = '
<td>Assigned:</td><td>'+cast(i.AssignedDate as varchar(20))+'</td>
<td>Due:</td><td>'+isnull(cast(i.DueDate as varchar(20)),'N/A')+'</td>
DO NOT REPLY TO THIS EMAIL. THIS IS AN AUTOMATED MESSAGE FROM AN UNMONITORED MAILBOX.
From INSERTED i
left join dbo.Agents AssignedTo on i.AssignedTo =AssignedTo.AgentID
left join dbo.Agents CreatedBy on i.CreatedBy=CreatedBy.AgentID
@profile_name = 'ProMeLand System',
@recipients = @AssignedToWorkEmail,
@subject = @EmailSubject,
@body = @EmailBody,
@body_format = 'HTML'