SQL SERVER 发邮箱

s01896

已于 2023-03-27 19:23:04 修改

阅读量419

点赞数

分类专栏： sqlerver 文章标签： sqlserver

于 2023-03-27 19:20:17 首次发布

本文链接：https://blog.csdn.net/s01896/article/details/129801774

版权

sqlerver 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

该文章涉及两个SQL存储过程，用于自动化发送邮件。第一个过程[Ex_sendmail13_BPM]用于发送物品放行还未回厂的明细表格，通过查询数据库信息并生成HTML表格作为邮件正文。第二个过程[Ex_sendmail4_BPM]是针对OA採購變更單，向供应商发送带有PDF附件的邮件。这两个过程都利用了msdb.dbo.sp_send_dbmail进行邮件发送。

摘要由CSDN通过智能技术生成

1.内容为表格模式

--物品放行单，还未回厂的单发送邮件

CREATE proc [dbo].[Ex_sendmail13_BPM] 
as 
begin
declare @subject nvarchar(4000)
declare @cbody nvarchar(4000)
declare @recipients nvarchar(4000)
declare @copy_recipients nvarchar(4000)
declare @SqlCommandText nvarchar(4000)
declare @file_attachments nvarchar(4000)
declare @sqlcompare nvarchar(4000)
declare @body nvarchar(max)
DECLARE @email NVARCHAR(2000)
DECLARE @email2 NVARCHAR(4000)
DECLARE @taskid INT

	--等待5秒后執行,目的是生成PDF檔時需要時間
	--WAITFOR DELAY'00:00:05'
	
	
	SET @copy_recipients=''
	SET @file_attachments=''
	SET @subject='物品放行还未回厂明细'
	SET @cbody=''
	
    
	DECLARE curimadmwp2 CURSOR for  SELECT DISTINCT UV_ADMWP.TASKID FROM NewCloudDBHL.dbo.UV_ADMWP LEFT JOIN NewCloudDBHL.dbo.SYSBPMITasks ON UV_ADMWP.TASKID = SYSBPMITasks.TaskId 
WHERE SYSBPMITasks.Status =0  
AND WP013<=GETDATE()
AND  EXISTS
(SELECT * FROM NewCloudDBHL.dbo.SYSBPMISteps WHERE ProcessGuid='05320653-d69e-4bf4-b4d7-fd203e292573'AND NodeId IN(8,44) AND FinishOn IS NOT NULL
AND UV_ADMWP.TASKID=SYSBPMISteps.TaskId)
	open curimadmwp2 
	fetch next from curimadmwp2 into @taskid
	while @@FETCH_STATUS =0
	begin 
		SET @email2=''
		SET @recipients=''
	    declare curimadmwp3 cursor for SELECT Email FROM NewCloudDBHL.dbo.SYSUser WHERE Account IN(
												SELECT  OwnerAccount FROM NewCloudDBHL.dbo.SYSBPMISteps WHERE TaskId=@taskid AND ActionLinkName IN('提交','同意')
												) 
												AND NewCloudDBHL.dbo.SYSUser.Active=1
		  open curimadmwp3 
			 fetch next from curimadmwp3 into @email2
			while @@FETCH_STATUS =0
			begin 	
				SET @recipients+=@email2+';'
				
			fetch next from curimadmwp3 into @email2
			end
			close curimadmwp3
			deallocate curimadmwp3    
		
		set @cbody= 
       N'<H1>您們好</H1><br>此邮件为系统自动产生<br></H1><br>附件為携出物品未回厂明細！请勿回复!<br><table border="1">' +  
           N'<tr><th>任務號</th><th>流水號</th><th>申請人</th><th>姓名</th><th>部門</th><th>廠商</th><th>回廠否</th><th>携出日期</th><th>物品名稱</th><th>數量</th><th>携出用途</th><th>預計回廠日期</th><th>實際回廠日期</th><th>回廠消單人</th><th>消單人姓名</th></tr>' +  
																																										
    CAST ( ( select td=A,'',td=B,'',td=C,'',td=D,'',td=E, '',td=F, '',td=G, '',td=H, '',td=I, '',td=J, '',td=K, '',td=L, '',td=M, '',td=N, '',td=O From 
    (
SELECT TASKID A,RECORD_ID B,WP001 C,WP002 D,WP004 E,WP007 F,WP008 G,WP009 H,WP010 I,WP011 J,WP012 K,WP013 L,WP014 M,WP015 N,WP016 O FROM  NewCloudDBHL.dbo.UV_ADMWP WHERE taskid=@taskid)a
              FOR XML PATH('tr'), ELEMENTS 
    ) AS NVARCHAR(MAX) ) +
    N'</table>'	
	
	
		EXEC msdb.dbo.sp_send_dbmail @profile_name ='OAMAIL',
		@recipients=@recipients,
		@copy_recipients=@copy_recipients,
		@blind_copy_recipients='llf@163.com.cn',
		@subject = @subject,
		@body =@cbody,  
		@file_attachments =@file_attachments,
		@body_format = 'HTML' ;
		
fetch next from curimadmwp2 into @taskid
	end
	close curimadmwp2 
	deallocate curimadmwp2

end

2.插入附件模式

--OA採購變更單發郵件給供應商

CREATE proc [dbo].[Ex_sendmail4_BPM] @TASKID INT  
as 
begin
declare @subject nvarchar(4000)
declare @cbody nvarchar(4000)
declare @recipients nvarchar(4000)
declare @copy_recipients nvarchar(4000)
declare @SqlCommandText nvarchar(4000)
declare @file_attachments nvarchar(4000)
declare @sqlcompare nvarchar(4000)
declare @body nvarchar(max)
DECLARE @PNA01 NVARCHAR(50)
DECLARE @PNA02 NVARCHAR(50)
DECLARE @PMC03 NVARCHAR(50)
DECLARE @PMM09 NVARCHAR(50)
DECLARE @PNAUSER NVARCHAR(50)
DECLARE @AZF03 NVARCHAR(100)
DECLARE @email NVARCHAR(2000)

	--等待5秒后執行,目的是生成PDF檔時需要時間
	--WAITFOR DELAY'00:00:05'
	
	SET @recipients=''
    SELECT @PNA01=PNA01,@PNA02=PNA02,@PMC03=PMC03,@PMM09=PMM09,@PNAUSER=PNAUSER,@AZF03=azf03 FROM NewCloudDBM.dbo.UT_ERPTF WHERE TASKID=@TASKID
    
    SELECT @copy_recipients = Email from NewCloudDBM.dbo.SYSUser
         WHERE  Account=@PNAUSER
     
    --SET @sqlcompare ='select pmc12 from pmc_file where pmc01='''+@TM002+'''' 
    --SET @sqlcompare = REPLACE(@sqlcompare,'''','''''')
    --SET @SqlCommandText= N'SELECT @recipients=isnull(pmc12,'''') from openquery(tip,'''+@sqlcompare+''')'     
    --exec sp_executesql @SqlCommandText,N'@recipients nvarchar(4000) out',@recipients output
    
    declare curimerptm cursor for SELECT EMAIL FROM NewCloudDBM.dbo.UT_PURMAIL WHERE LOWER(PUR_NO) = LOWER(@PMM09)
      open curimerptm 
		 fetch next from curimerptm into @email
		while @@FETCH_STATUS =0
		begin 
			SET @recipients+=@email+';'
			
		fetch next from curimerptm into @email
		end
		close curimerptm 
		deallocate curimerptm
		
	PRINT @recipients
	
	SET @file_attachments='C:\採購變更單-'+@PMC03+'.PDF'
	--SET @recipients='fhq@melton.com.cn;litao@melton.com.cn;llf@melton.com.cn;scraper@melton.com.cn'

	--PRINT @SqlCommandText 
	--PRINT @recipients
	--PRINT @file_attachments
	SET @subject='XX(東莞)有限公司(採購變更單 - '+@PMC03+')--變更原因：'+@AZF03
	SET @cbody='您好~  此邮件为系统自动产生，附件為採購變更單！请勿直接回复。  採購單號：'+@PNA01 +'  變更項次：'+@PNA02
	
	
	--IF @recipients <>'' AND (CHARINDEX('com',@recipients)>0 OR CHARINDEX('cn',@recipients)>0)
	--BEGIN 
		EXEC msdb.dbo.sp_send_dbmail @profile_name ='OAMAIL',
		@recipients=@recipients,
		@copy_recipients=@copy_recipients,
		@blind_copy_recipients='llf@melton.com.cn',
		@subject = @subject,
		@body =@cbody,  
		@file_attachments =@file_attachments,
		@body_format = 'HTML' ;
	--END
	
    EXEC('master..xp_cmdshell ''del '+@file_attachments+''',no_output')
    
end