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