How to email monthly POS Reports via SQL

How to email monthly POS Reports

1. Save POS reports in \\901file\groups\mis\vendorPOSreports\Current directory

2. Connect to SQL via SSMS (Sql Server Management Studio)

3. Run query UPDATE [DAC].[dbo].[VendorPOS_EmailAutomation] Set emailSent = '0' to reset the status log

(0 = not sent 1 = sent)

4. Run below query to pick up the files and email them out

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

    -- Insert statements for procedure here

DECLARE

  @vendorNumber VARCHAR(max),

  @vendorContact VARCHAR(max),

  @emailSent VARCHAR(max),

  @posPath VARCHAR(max),

  @emailSubject varchar(max),

  @emailBody varchar(max),

  @path varchar(max)

  DECLARE c1 CURSOR FOR 

    SELECT vendorContact,vendorNumber, emailSent FROM VendorPOS_EmailAutomation Where emailSent = '0' AND VendorContact IS NOT NULL

OPEN c1

  FETCH NEXT FROM c1 INTO @vendorContact,@vendorNumber, @emailSent

  WHILE @@FETCH_STATUS <> -1

    BEGIN

    SELECT 

      @emailSubject = 'Vendor POS Report',

      @emailBody = 'Attached is your POS Vendor Report.',

  @path = '\\901file\groups\MIS\vendorPOSreports\Current\'+@vendorNumber+'.csv'

      EXEC msdb.dbo.sp_send_dbmail 

      @profile_Name ='ToddMills',

      @recipients= @vendorContact ,

      @subject = @emailSubject,

      @body = @emailBody,

  @body_format = 'HTML',

      @file_attachments = @path;

   

    

    FETCH NEXT FROM c1 INTO @vendorContact,@vendorNumber, @emailSent

    END

  CLOSE c1

  DEALLOCATE c1

  WAITFOR DELAY '00:00:59';

Update VendorPOS_EmailAutomation 

 SET emailSent = '1'

 FROM VendorPOS_EmailAutomation A

LEFT JOIN  msdb.dbo.sysmail_sentitems B ON A.vendorContact = b.recipients 

where b.subject = 'Vendor POS Report' AND sent_status = 'sent'

AND b.file_attachments = '\\901file\groups\MIS\vendorPOSreports\Current\'+A.vendorNumber+'.csv'

     AND CAST(sent_date AS DATE) = CONVERT (DATE, GETDATE())

SELECT * FROM VendorPOS_EmailAutomation

END