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 
Creation date: 1/21/2020 7:52 AM (1032)      Updated: 1/21/2020 7:52 AM ()