I want to create a sql job that will run every night at 3 AM and send emails.
I am able to send emails from the stored procedure using "msdb.dbo.sp_send_dbmail".
SET @SQL = ' EXEC msdb.dbo.sp_send_dbmail
@profile_name = ''' + @Profile_Name + ''',
@recipients = ''' + @EmailRecipients + ''',
@body_format = ''html'',
@subject = ''' + @EmailSubjectToSend + ''',
@body = ''' + @EmailBodyToSend + ''';'
Now In many cases I want to attach some files (one or more than one) to the email. The attachments are stored in the database itself (in one table. The table structure is:
CREATE TABLE [dbo].[tblCourseAutoEmailAttachments](
[AttachmentID] [int] IDENTITY(1,1) NOT NULL,
[CourseAutomatedEmailID] [int] NULL,
[Attachment] [image] NULL,
[AttachmentName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AttachmentSize] [int] NULL)
Please let me know how can I send emails with attachments from the stored procedure.
Thanks in advance.