Question

my team is using the mentioned script to send the email but script is executing without any error and not sending emails so please guide me and tell me the way to rectify. even I have checked through command about the configure profile and server but still not able to find the issue. Below is the code not able to find the way to start the troubleshoot so guide me

ALTER PROCEDURE [dbo].[CBS_Invoice_Mail]       
AS       
BEGIN         
   
   
SELECT identity(int,1,1)'SN',
       A.DocNum,
       A.DocEntry,
       A.DocDate,
       A.CardCode,
       A.CardName,
       A.DocTotal,
       --'\192.168.5.229Attachement'
        '\sapappB1_SHRAttachment'
         +''+ t.FileName +'.'+ t.FileExt as Attachement  
          
INTO  #temp     
FROM  OINV A 
      left JOIN OCRD C ON A.CardCode = C.CardCode 
      left join ATC1 t on t.AbsEntry = A.AtcEntry
where A.DocDate = convert(nvarchar(100),GETDATE(),111)
and   C.U_Mailing = 'Yes'
and   isnull(A.U_Mail,'') <>'YES'

   

declare @min int, @max int
select  @min= min(sn) from #temp
select  @max= max(sn) from #temp

while (@min<=@max)
begin
-------------------------------------------------Mailing Start-------------------------------   
Declare @INDocNum                  Nvarchar(200)     
Declare @INDocDate                 Nvarchar(200)   
Declare @INCardName                Nvarchar(200)
Declare @INContactPerson           Nvarchar(200)  
Declare @INAmount                  Nvarchar(200)
Declare @INAmtWord                 Nvarchar(300)
Declare @INCustomerMailId          Nvarchar(200)
Declare @INAgentMailId             Nvarchar(200)
Declare @INattach                  Nvarchar(max)


select @INDocNum =         A.DocNum   from  #temp A where  sn = @min
select @INDocDate =        convert(nvarchar(100),A.DocDate,103) from  #temp A where  sn = @min
select @INCardName =       A.CardName from   #temp A where  sn = @min 
select @INAmount  =        convert(numeric(19,2),A.DocTotal)  from   #temp A where  sn = @min
select @INAmtWord  =       dbo.fnMoneyToEnglish(A.DocTotal)  from   #temp A where  sn = @min
select @INattach          = A.Attachement from #temp A where  sn = @min


Declare @IPsubject nvarchar(max)      
Set     @IPsubject = (select 'Dadri Invoice to'  + ' ' +@INCardName) 

Declare @Body  nvarchar(max)              
Set     @Body = '<body>  
 <h1 style="color:Blue;"><center>MEEN<font style="color:Red;">A</font>KSHI POLYMERS PVT. LTD.</center></h1>

 <h2> <center> INVOICE </center> </h2>
 <h5>  Dear Sir </h5> 
 
 <div>  Please Find Attachement of Invoice </div> 

<p>  </p>
<p>  </p> 
</br> 
     <div> <font style="color:Blue;"> MEENAKSHI POLYMERS(P)LTD. </font> </div>
     <div style ="margin:0px;">   25 Km Stone Vill-Kote GT ROAD Dadri-203207 (U.P) INDIA  </div>     
     <div>    Website: www.meenakshipolymers.com   </div>    
           
  <p> <font style="color:Blue;"> Note:- This is a system generated mail does not required signature </font> </p>  


</body> '  


EXECUTE Msdb..sp_send_dbmail
@profile_name = 'SAP Dadri',                           
@recipients = '[email protected]; [email protected]; ',
@copy_recipients = '[email protected];',
--@copy_recipients = @INAgentMailId ,
@BODY    = @Body       ,           
@SUBJECT = @IPsubject  ,
@BODY_FORMAT = 'HTML'  ,
@file_attachments=@INattach   


update o
set o.U_Mail='YES'
from oinv o,#temp t
where o.DocEntry =t.DocEntry 
and t.SN=@min

select @min=@min+1

end
end

Issue in Email Sending with attachment through SQL procedure

LEAVE A COMMENT Hủy