Sending a mail

Add SMTP server details in app.config

add key="SMTPServer" value="123.123.12.123"/
    add key="ErrorRecipient" value="rkonduru@gmail.com" /
    add key="SystemEmailAddress" value="donotreply@sample.com" /
    add key="AdminEmail" value="donotreply@sample.com" /
    add key="DOMAINNAME"  value="URDomain"/>
    add key="AppEnvironment" value="Prod - ProjectName"/

   
   

   
Add a MailHelper Class:
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Net.Mail;
using System.Text;
using System.Threading.Tasks;

namespace MoveZipFilesToSpecificFolder
{
    class MailHelper
    {
        // Sends an email in a synchronous manner
        /*CC2-2D Internal*/
        public void SendEmail(string fromAddress, string toAddress, string subject, string message, bool isHtmlBody)
        // public void SendEmail(string fromAddress, string toAddress, string subject, string message, bool isBodyHtml = false)
        {
            try
            {
                var smtpClient = new SmtpClient(ConfigurationManager.AppSettings["SMTPServer"]);

                var toEmails = toAddress.Split(',').ToList().SelectMany(x => x.Split(';')).ToList();

                var mailMessage = new MailMessage(fromAddress, toEmails[0], subject, message) { IsBodyHtml = isHtmlBody };

                toEmails.RemoveAt(0);
                foreach (var to in toEmails)
                {
                    mailMessage.To.Add(new MailAddress(to));
                }

                smtpClient.Send(mailMessage);
            }
            catch (SmtpException ex)
            {
                throw new ApplicationException("Error sending email.", ex);
            }
        }

        public void SendEmail(string fromAddress, string toAddress, string ccAddress, string bccAddress, string subject, string message, bool isHtmlBody)
        {
            try
            {
                var smtpClient = new SmtpClient(ConfigurationManager.AppSettings["SMTPServer"]);
                var toEmails = toAddress.Split(',').ToList().SelectMany(x => x.Split(';')).ToList();

                var mailMessage = new MailMessage(fromAddress, toEmails[0], subject, message) { IsBodyHtml = isHtmlBody };

                toEmails.RemoveAt(0);
                foreach (var to in toEmails)
                {
                    mailMessage.To.Add(new MailAddress(to));
                }

                // Check CC mail address.
                if (!string.IsNullOrEmpty(ccAddress.Trim()))
                {
                    var ccEmails = ccAddress.Split(',').ToList().SelectMany(x => x.Split(';')).ToList();
                    foreach (var cc in ccEmails)
                    {
                        mailMessage.CC.Add(new MailAddress(cc));
                    }
                }

                // Check BCC mail address.
                if (!string.IsNullOrEmpty(bccAddress.Trim()))
                {
                    var bccEmails = bccAddress.Split(',').ToList().SelectMany(x => x.Split(';')).ToList();
                    foreach (var bcc in bccEmails)
                    {
                        mailMessage.Bcc.Add(new MailAddress(bcc));
                    }
                }
                smtpClient.Send(mailMessage);
            }
            catch (SmtpException ex)
            {
                throw new ApplicationException("Error sending email.", ex);
            }
        }

        public void SendEmail(string fromAddress, string toAddress, string subject, string message)
        {
            SendEmail(fromAddress, toAddress, subject, message, false);
        }
        /// Sends an email in a synchronous manner
        public void SendEmail(string toAddress, string subject, string message)
        {
            SendEmail(ConfigurationManager.AppSettings["AdminEmail"], toAddress, subject, message);
        }

        public void SendEmail(string toAddress, string subject, string message, bool isHtmlBody)
        {
            SendEmail(ConfigurationManager.AppSettings["AdminEmail"], toAddress, subject, message, isHtmlBody);
        }
    }
}

Send the Text to send in the body alone in below method

public static void SendEmail(string textLog)
        {
            var ticketEmail = ConfigurationManager.AppSettings["ErrorRecipient"];
            var doNotReplayEmail = ConfigurationManager.AppSettings["SystemEmailAddress"];
            var appEnv = ConfigurationManager.AppSettings["AppEnvironment"];

            try
            {
                string body = "Hello ,\n"+textLog;
                var emailService = new MailHelper();
                emailService.SendEmail(doNotReplayEmail, ticketEmail, "MRM Error - Moving Files from ORPHANS to INCOMING", body, true);
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }


Sending Mail Through SQL Server Agent
-------------------------------------------------------

Alter proc [dbo].[RUG_MRAttachedError]

As

Begin

set nocount on;

DECLARE @tableHTML  NVARCHAR(MAX)

Declare @count int,

@identity int



insert into Rug_MRAttached_Error_Master(Execution_date,Active)

select GETDATE(),1



create table #result(Name varchar(500),GroupingID bigint,ID int,claimnumber varchar(200),substatusid int)



insert into #result

select distinct s.Name,a.GroupingID,a.Id,cs.claimnumber,s.Id as substatusid  --into #result

from Prod_ExtMyClinet_Workflow..audit a  with(nolock)

inner join Prod_ExtMyClinet_Workflow..Substatus s with(nolock)

on a.Substatus_id=s.Id

inner join Prod_ExtMyClinet_AuditClaims..ClaimSummary cs with(nolock)

on cs.ClaimSummaryUID=a.ClaimSummaryUid

where a.GroupingID in (select groupingid from audit where Substatus_id=20301)

and a.Flow_id=1818

and s.Name in ('Selected','Technical Denial','LIMIT')

union all

select distinct s.Name,a.GroupingID,a.Id,cs.claimnumber,s.Id as substatusid  --into #result

from Prod_ExtMyClinet_Workflow..audit a  with(nolock)

inner join Prod_ExtMyClinet_Workflow..Substatus s with(nolock)

on a.Substatus_id=s.Id

inner join Prod_ExtMyClinet_AuditClaims..ClaimSummary cs with(nolock)

on cs.ClaimSummaryUID=a.ClaimSummaryUid

where a.GroupingID is null and a.Flow_id=1818

and s.Name in ('Ready For Review')



SELECT @identity=IDENT_CURRENT('Rug_MRAttached_Error_Master')



insert into Rug_MRAttached_Error_Audit_Detail

select b.*,@identity

from #result as a

inner join Prod_ExtMyClinet_Workflow..audit as b on a.GroupingID=b.GroupingID

select @count=@@ROWCOUNT



update b set Substatus_id=a.substatusid,

PreviousSubstatus_id=null

from #result as a

inner join Prod_ExtMyClinet_Workflow..audit as b on a.GroupingID=b.GroupingID

where a.GroupingID is not null

--Below listed group Id''s need to be attached as per RUG grouping logic.



update b set Substatus_id=b.PreviousSubstatus_id

from #result as a

inner join Prod_ExtMyClinet_Workflow..audit as b on a.GroupingID=b.GroupingID

where a.GroupingID is null



SET @tableHTML =

    N'Hi Team,
' +

    N'Below listed group Id''s are modified as per RUG grouping logic.' + 

    N'



' + 


    N'

Substatus Name

GroupingID

' + 
    N'

Audit_Id

ClaimNumber

' +           CAST ( ( SELECT td = Name,       '',

                    td = GroupingID, '',

                    td = Id, '',

                    td = claimnumber, ''

              FROM #result

              FOR XML PATH('tr'), TYPE 

    ) AS NVARCHAR(MAX) ) +

    N'

' ;       

if exists(select 1 from #result)

begin

EXEC msdb.dbo.sp_send_dbmail @recipients='RKonduru@Sciohealthanalytics.com;CBashaC@Sciohealthanalytics.com;Ashokkumar.P@Sciohealthanalytics.com',

@copy_recipients ='bjahangeer@sciohealthanalytics.com'  ,

    @subject = 'RUG group claims MR_Attached Error - Audits' ,

    @body = @tableHTML,

    @body_format = 'HTML' ;

    update Rug_MRAttached_Error_Master set rec_count=@count,status='Success with record modified',MailStatus='Sent' where id=@identity

End

else

Begin

 update Rug_MRAttached_Error_Master set rec_count=@count,status='Success with out record modified',MailStatus='Not Sent' where id=@identity

End

set nocount on;

End