sending bulk emails with attachements¶
Example to send emails with attachements
DECLARE
-- @MFTableName NVARCHAR(100), -- this is the class table with the attachement
@subject NVARCHAR(128) = 'Test Attachment',
@body NVARCHAR(128) = 'Your Invoice',
@Process_ID INT = 1, -- filter the attachements to be sent by email
@ProcessBatch_ID INT = NULL,
@Debug SMALLINT = 1
DECLARE @filenames NVARCHAR(1000)
DECLARE @FileLocation NVARCHAR(100)
DECLARE @Recipients NVARCHAR(128)
DECLARE @ProfileName NVARCHAR(128)
DECLARE @ID int
SELECT @ProfileName = CAST(value AS NVARCHAR(100)) FROM mfsettings WHERE name = 'SupportEmailProfile'
-- -----------------------------------------------------------
-- Create attachments
-- -----------------------------------------------------------
UPDATE si
SET Process_ID = @Process_ID
FROM mfsalesinvoice si
WHERE FileCount > 0 AND Document_Date = '2007-03-01'
EXEC dbo.spMFExportFiles
@TableName = 'MFSalesInvoice',
@PathProperty_L1 = null,
@PathProperty_L2 = null,
@PathProperty_L3 = null,
@IncludeDocID = 0,
@Process_id = @Process_ID,
@ProcessBatch_ID = @ProcessBatch_ID,
@Debug = 0
-- -----------------------------------------------------------
-- Prepare emaillist
-- -----------------------------------------------------------
CREATE TABLE #TempEmailList
(id INT IDENTITY, Recipients NVARCHAR(250), FileLocation NVARCHAR(4000))
INSERT INTO #TempEmailList
(
Recipients,
FileLocation
)
SELECT
mcp.Email_Address ,
mefh.FileExportRoot + CASE
WHEN mefh.SubFolder_1 != ''
THEN mefh.SubFolder_1 + '\'
ELSE
''
END + CASE
WHEN mefh.SubFolder_2 != ''
THEN mefh.SubFolder_2 + '\'
ELSE
''
END + CASE
WHEN mefh.SubFolder_3 != ''
THEN mefh.SubFolder_3 + '\'
ELSE
''
END + mefh.FileName
-- , mefh.*
FROM
dbo.MFContactPerson AS mcp
INNER JOIN dbo.MFCustomer AS mc
ON mcp.Owner_Customer_ID = mc.objid
INNER JOIN dbo.MFSalesInvoice AS msi
ON msi.Customer_ID = mc.objid
inner JOIN
dbo.MFExportFileHistory AS mefh
ON msi.ObjID = mefh.ObjID
AND mefh.ClassID = msi.Class_ID
WHERE mcp.Email_Address IS NOT NULL
IF @Debug > 0
Begin
UPDATE #TempEmailList
SET Recipients = 'support@lamininsolutions.com'
WHERE Recipients <> 'Support@lamininsolutions.com'
end
-- -----------------------------------------------------------
-- Send emails
-- -----------------------------------------------------------
WHILE EXISTS(SELECT id FROM #TempEmailList AS tel)
BEGIN
SELECT @ID = MIN(ID) FROM #TempEmailList AS tel
SELECT @Recipients = recipients, @FileLocation = tel.FileLocation FROM #TempEmailList AS tel
WHERE id = @ID
EXEC msdb..sp_send_dbmail
@profile_name = @ProfileName,
@recipients = @Recipients,
@subject = @subject,
@body = @body,
@file_attachments = @FileLocation;
DELETE FROM #TempEmailList WHERE id = @ID
END
DROP table #TempEmailList