locked
Email using sp_send_dbmail RRS feed

  • Question

  • User-797751191 posted

    Hi

      I have below code and i want to display data by Group on Location. Sr.No should start with each location. In Location Database i have emailid

    begin
    Set @MailHeader='<H4>Dear Sir,<br/><br/> Email <br/><br/></H4>';
    
    SET @tableHTML =@MailHeader+
        N'<table border="1" >' +
        N'<tr><th>SN.</th><th>Document No</th><th>Date</th>' +
        N'<th>Remarks</th><th>Url</th>' +
     
        CAST ( ( SELECT td = ROW_NUMBER() OVER(ORDER BY [Date]), '',
    					td = [Invoice No], '',
    					td = [Date], '', 
    					td = [Remarks], '', 
    					td = +'<a href="http://localhost:1501/Test.aspx?No='+CAST(@ENo AS varchar(10))+'">Click Me</a>'
                  FROM [Test]
    			  where [ENo] = @ENo
    			  order by [Date]
                  FOR XML PATH('tr'), TYPE 
        ) AS NVARCHAR(MAX) ) +
        N'</table><br/><br/>Thanks and Regards<br><br> ;
    
    	EXEC msdb.dbo.sp_send_dbmail @recipients=@emailid,
        @subject = @MailSubject,
        @body_format = 'HTML' ,
    	@body = @tableHTML
    end

    Thanks

    Thursday, July 4, 2019 4:43 AM

All replies

  • User-719153870 posted

    Hi jsshivalik,

    It's hard to understand your requirements with the code and description of the problem you're presently providing.

    If possible you can provide the structure of all the tables involved in your question and the relationship between them, as well as the declaration and assignment of the parameter @emailed please.

    The above information can help us solve your problem as soon as possible.

    Best Regard,

    Yang Shen

    Friday, July 5, 2019 5:10 AM
  • User-797751191 posted

    Hi Yang

      I have 2 tables . Location & Employee table . I want to send List of Employees Location Wise. Is it possible to do without Cursor . 

    Get Locations Data in some variable & then loop thru Employee table 

    Thanks

    Sunday, July 7, 2019 7:25 AM
  • User-719153870 posted

    Hi jsshivalik,

    Although I still don't understand your purpose, it's entirely feasible for you to use loops and variables to get the corresponding values in SQL.

    If you can provide the structure and data of Location and Employee tables, I might be able to build a demo to help you.

    Best Regard,

    Yang Shen

    Tuesday, July 9, 2019 6:45 AM