How to send emails to customers in a tabular format in Dynamics NAV?

Introduction:

Hello Buddies! I’m back with a blog on sending emails to customers/users in a tabular format  using html tags in NAV.

Email have become an integral part of our daily lives. Its amazing that we can do so much with email in Microsoft Dynamics NAV using SMTP mail.

In this scenario, any error while importing files is stored in a table Import EDI Error Logs. As soon as the table populates consisting of Today’s date (current date) an email will be notified to the current user importing  the documents. This makes it easier to troubleshoot the error and rectify them.

Pre-requisites:

Microsoft Dynamics NAV

Steps:

• Firstly make sure you have setup the SMTP mail setup with userid and password

Explanation of the below code:

• Here I first filter on my table Import Error Logs for today’s date and time.

ImportEDIErrorLog.RESET;
ImportEDIErrorLog.SETRANGE("Responsibility Center",RespCen);
ImportEDIErrorLog.SETRANGE("ImportedOn",CREATEDATETIME(TODAY,000000T),CRETEDATETIME(TODAY,235900T));
IF ImportEDIErrorLog.FINDSET THEN BEGIN

• Filter on User setup , here I have enabled a boolean  field Import EDI Error Log on the user setup , an email will be sent to all users with the field marked as true.

UserSetup.RESET;
UserSetup.SETRANGE("Import EDI Error Mail",TRUE);
UserSetup.SETFILTER("E-Mail",'<>%1','');
UserSetup.SETRANGE("Sales Resp. Ctr. Filter",RespCen);

• I’ve created a Function called CreateMailBody with return type as Text for arranging the mail in a tabular format using basic html tags.

ImportEDIErrorLog.RESET;
ImportEDIErrorLog.SETRANGE("Responsibility Center",RespCen);
ImportEDIErrorLog.SETRANGE("Imported On",CREATEDATETIME(TODAY,000000T),CREATEDATETIME(TODAY,235900T));
IF ImportEDIErrorLog.FINDSET THEN BEGIN
SMTPMailSetup.GET;
UserSetup.RESET;
UserSetup.SETRANGE("Import EDI Error Mail",TRUE);
UserSetup.SETFILTER("E-Mail",'<>%1','');
UserSetup.SETRANGE("Sales Resp. Ctr. Filter",RespCen);
RespCen1:=RespCen;
IF UserSetup.FINDSET THEN
REPEAT
SMTPMail.CreateMessage('Liberty Aluminium Technologies Ltd',SMTPMailSetup."User ID",UserSetup."E-Mail",'EDI Import Error Log','',TRUE);
SMTPMail.AppendBody('Import EDI Error log for today '+FORMAT(TODAY));
SMTPMail.AppendBody('<br><br>');
SMTPMail.AppendBody(CreateMailBody);
SMTPMail.AppendBody('<br><br>');
SMTPMail.AppendBody('<HR>');
SMTPMail.AppendBody('This is a system generated email. Please do not reply to this mail');
SMTPMail.Send;
UNTIL UserSetup.NEXT=0;
END

• Create a local text variable EmailText with datatype as Text

Code  for the function is as below:

• Using basic html tags for table border, tr for table row, th for table header, td for table data.
• Here all the data present in the Import EDI Error logs for today’s date will be appended  to EmailText in a tabular format and then notified to the user

LOCAL CreateMailBody() : Text
EmailText+='<table border ="1">';
EmailText+='<tr>';
EmailText+='<th> Entry No. </th>';
EmailText+='<th> File Name </th>';
EmailText+='<th> Eror Message </th>';
EmailText+='<th> Imported On </th>';
EmailText+='<th> Imported By </th>';
EmailText+='</tr>';
ImportEDIErrorLog.RESET;
ImportEDIErrorLog.SETRANGE("Responsibility Center",RespCen1);
ImportEDIErrorLog.SETRANGE("Imported On",CREATEDATETIME(TODAY,000000T),CREATEDATETIME(TODAY,235900T));
IF ImportEDIErrorLog.FINDSET THEN
REPEAT

EmailText+='<tr>';
EmailText+='<td>' + FORMAT(ImportEDIErrorLog."Entry No.") + '</td>';
EmailText+='<td>' + FORMAT(ImportEDIErrorLog."File Name") + '</td>';
EmailText+='<td>' + FORMAT(ImportEDIErrorLog."Error Message") + '</td>';
EmailText+='<td>' + FORMAT(ImportEDIErrorLog."Imported On") + '</td>';
EmailText+='<td>' + FORMAT(ImportEDIErrorLog."Imported By") + '</td>';
EmailText+='</tr>';


UNTIL ImportEDIErrorLog.NEXT=0;
EmailText+='</table>';
EXIT(EmailText);

Output:

Happy Weekend!