Sales details through email in Dynamics NAV

17-02-2015
Reporting of sales through email is a handy way to provide information to the management. Realtime information flow is the beauty of ERP. The below example provides sales data in tabular format for the current month through email to the specified user. You may schedule it through Job Queue to run daily, hourly or whatever frequency you decide.

NameDataTypeSubtypeLength
SMTPMailSetupRecordSMTP Mail Setup
MailAutomation'Microsoft Navision Mail'.SmtpMessage
BodyText1024
InvoiceTotalDecimal
SILRecordSales Invoice Line
ResultText1024

NameConstValue
Text001The SMTP mail system returned the following error: %1

InvoiceTotal := 0;
IF ISCLEAR(Mail) THEN
  CREATE(Mail);
Mail.FromName := ''; // Enter your name
Mail.FromAddress := ''; // Enter your email address
Mail."To" := ''; // Enter email address
Mail.Subject := 'Sales Data';
Mail.HtmlFormatted := TRUE;
Body := '';
Body := Body + STRSUBSTNO('<font face=''verdana'' size=''4'' color=''navy''><p>Sales as on %1</P></font>',CURRENTDATETIME);
Body := Body+STRSUBSTNO('<table align=left width=95% border=0 cellspacing=0 cellpadding=5 style=''border: 1px #6699CC solid''>');
Body := Body + '<tr style=''background-color: #AADBDB;''>';
Body := Body + STRSUBSTNO('<td width=10%><font face="verdana" size="2"><b>Posting Date</b></Font></td>');
Body := Body + STRSUBSTNO('<td width=10%><font face="verdana" size="2"><b>Invoice No.</b></Font></td>');
Body := Body + STRSUBSTNO('<td width=20%><font face="verdana" size="2"><b>Customer</b></Font></td>');
Body := Body + STRSUBSTNO('<td width=10%><font face="verdana" size="2"><b>Item No.</b></Font></td>');
Body := Body + STRSUBSTNO('<td width=16 %><font face="verdana" size="2"><b>Description</b></Font></td>');
Body := Body + STRSUBSTNO('<td width=6% align=right><font face="verdana" size="2"><b>Quantity</b></Font></td>');
Body := Body + STRSUBSTNO('<td width=7% align=right><font face="verdana" size="2"><b>Price</b></Font></td>');
Body := Body + STRSUBSTNO('<td width=7% align=right><font face="verdana" size="2"><b>Amount</b></Font></td>');
Mail.Body := Body;

SIL.RESET;
SIL.SETRANGE("Posting Date",CALCDATE('-CM',WORKDATE),WORKDATE);
SIL.SETFILTER(Quantity,'<>%1',0);
IF SIL.FINDSET THEN
  REPEAT
    IF Cust.GET(SIL."Sell-to Customer No.") THEN BEGIN
      Body := '';
      Body := Body + '<tr><td style=''border-top: 1px solid #6699CC''>';
      Body := Body + '<font face=''verdana'' size=''2''>' + FORMAT(SIL."Posting Date") + '</Font></td>';
      Body := Body + '<td style=''border-top: 1px solid #6699CC''>';
      Body := Body + '<font face=''verdana'' size=''2''>' + FORMAT(SIL."Document No.") + '</Font></td>';
      Body := Body + '<td style=''border-top: 1px solid #6699CC''>';
      Body := Body + '<font face=''verdana'' size=''2''>' + Cust.Name + '</Font></td>';
      Body := Body + '<td style=''border-top: 1px solid #6699CC''>';
      Body := Body + '<font face=''verdana'' size=''2''>' + SIL."No." + '</Font></td>';
      Body := Body + '<td style=''border-top: 1px solid #6699CC''>';
      Body := Body + '<font face=''verdana'' size=''2''>' + SIL.Description + '</Font></td>';
      Body := Body + '<td align=''right'' style=''border-top: 1px solid #6699CC''>';
      Body := Body + '<font face=''verdana'' size=''2''>' + FORMAT(SIL.Quantity) + '</Font></td>';
      Body := Body + '<td align=''right'' style=''border-top: 1px solid #6699CC''>';
      Body := Body + '<font face=''verdana'' size=''2''>' + FORMAT(SIL."Unit Price") + '</Font></td>';
      Body := Body + '<td align=''right'' style=''border-top: 1px solid #6699CC''>';
      Body := Body + '<font face=''verdana'' size=''2''>' + FORMAT(SIL."Line Amount") + '</Font></td>';
      Body := Body + '<td align=''right'' style=''border-top: 1px solid #6699CC''>';
      Mail.AppendBody := Body;
      InvoiceTotal += SIL."Line Amount";
    END;
  UNTIL SIL.NEXT = 0;
Mail.AppendBody := '</tr><tr><td colspan=6 align=right style=''border-top: 1px solid #6699CC''>';
Mail.AppendBody := '<font face=''verdana'' size=''2''><b>Total : ' + FORMAT(InvoiceTotal);
Mail.AppendBody := '</b></font></td></tr></table><BR><BR>';
WITH SMTPMailSetup DO
  Result := Mail.Send("SMTP Server",Authentication <> SMTPMailSetup.Authentication::Anonymous,"User ID", Password);
CLEAR(Mail);
IF Result <> '' THEN
  ERROR(Text001,Result);

"SMTP Mail Setup" table must be configured in order to send mail.