E-mail Vendor Remittance from Payment Journal

Aravindh_Navision · 2020-07-28

Dear Friends,

Need you help in sorting out my issue that I am facing.
I have few payment entries in Payment Journal for few Vendors (Account Type is Vendor in PJ).
Say,
VEND0001
VEND0002
VEND0003
VEND0004
VEND0005

Requirement: I need to send email to Vendors and Vendor Remittance report as attachment in it. I have placed a new option Email Remittance button in action tab of Payment Journal. On a single click email has to trigger for each Vendors with their corresponding Remittance.

Issue: I could able to send mail to Vendors as mentioned in the requirement. But the email attachment for all Vendors contains the Remittance of all the Vendors in new new page instead of attaching the corresponding Remittance to them.

Report Dataitem: Gen. Journal Line

DataItemTableView Property: WHERE(Journal Batch Name=FILTER(AP-CA-PAYM),Journal Template Name=FILTER(PAYMENTS),Account Type=FILTER(Vendor))

Global Variables:

Function

LOCAL SendMailToVendWithAttachment(VendNo : Code[20])

locVendor.RESET;
locVendor.SETFILTER(locVendor."E-Mail", '%1', '*@*');
locVendor.SETRANGE(locVendor."No.", "Gen. Journal Line"."Account No.");
IF locVendor.FINDFIRST THEN BEGIN
    REPEAT
    
        CLEAR(SMTPMail);
        CLEARLASTERROR;
        
        SMTPSetup.GET;
        PdfDocPath := 'Remittance for ' + locVendor.Name + '.pdf';
        Path := 'C:\Aarvi\PDFPath\Remittance' + PdfDocPath;
        //Path := FilePath + PdfDocPath;
        
        CLEAR(VendRemittance);
        VendRemittance.USEREQUESTPAGE(FALSE);
        VendRemittance.SETTABLEVIEW("Gen. Journal Line");
        VendRemittance.SAVEASPDF(Path);
        
        SMTPMail.CreateMessage('Company Name',SMTPSetup."User ID",
        locVendor."E-Mail",'Remittance for ' + locVendor.Name ,'',TRUE);
        
        SMTPMail.AppendBody('Dear Sir/Madam,');
        SMTPMail.AppendBody('<br><br>');
        SMTPMail.AppendBody('Please find the attachment of remittance herewith.');
        SMTPMail.AppendBody('<br><Br>');
        SMTPMail.AppendBody('Regards,');
        SMTPMail.AppendBody('<br>');
        SMTPMail.AppendBody('Company Name');
        SMTPMail.AppendBody('<br><br>');
        SMTPMail.AppendBody('<HR>');
        SMTPMail.AppendBody('This is a system generated mail. Please do not reply to this email ID.');
        
        SMTPMail.AddAttachment(Path,PdfDocPath); 
        SMTPMail.Send;
        
        SLEEP(1000);
        
        ERASE(Path);
    
    UNTIL locVendor.NEXT = 0;
END;

Calling the Function

Gen. Journal Line - OnAfterGetRecord()

SendMailToVendWithAttachment("Gen. Journal Line"."Account No.");

Can anyone tell me what am I missing here in the code?

Thanks in advance, Aarvi.

Xwioch · 2020-07-30

Well, this is up to your Vendor Remittance report.
If you build a well done dataitem the only thing you have to do is pass with the SETTABLEVIEW a filtered Journal Line for what you want to print.
And in this case you don't have to loop anymore on GenJnlLine.

So, I don't remember if SETTABLEVIEW works with temporary records. If it does your code should be like this:

    TmpGenJnlLine.RESET;
    TmpGenJnlLine.SETRANGE("Account No.", TmpVendor."No.");

    PdfDocPath := 'Remittance for ' + TmpVendor.Name + '.pdf';
    Path := FileMgt.ServerTempFileName('.pdf');

    CLEAR(VendRemittance);
    VendRemittance.USEREQUESTPAGE(FALSE);
    // We just ask the report to print all the journal filtered for the current vendor
    // We don't need to loop anymore on TmpGenJnlLine
    VendRemittance.SETTABLEVIEW(TmpGenJnlLine); // <<<<<<
    VendRemittance.SAVEASPDF(Path);

    SMTPMail.AddAttachment(Path, PdfDocPath);

    TmpFileList.INIT;
    TmpFileList.ID := 1; // Just put one, is always a single record now
    TmpFileList.Name := Path;
    TmpFileList.INSERT;

    SMTPMail.AddAttachment(Path, PdfDocPath);

    SMTPMail.Send;

    IF TmpFileList.FINDSET THEN
      REPEAT
        FileMgt.DeleteServerFile(TmpFileList.Name);
      UNTIL TmpFileList.NEXT = 0;

If it doesn't work because the record is temporary we need to use marks for filter.
We have to loop, mark the records on a real variable and then print.
Example:

    TmpGenJnlLine.RESET;
    TmpGenJnlLine.SETRANGE("Account No.", TmpVendor."No.");

    // Reset marks on every vendor loop
    GenJnlLine.RESET;

    IF TmpGenJnlLine.FINDSET THEN
      REPEAT
        // Marks the records we want to print
        GenJnlLine.GET(TmpGenJnlLine."Journal Template Name", TmpGenJnlLine."Journal Batch Name", TmpGenJnlLine."Line No.");
        GenJnlLine.MARK(TRUE);
      UNTIL TmpGenJnlLine.NEXT = 0;

    PdfDocPath := 'Remittance for ' + TmpVendor.Name + '.pdf';
    Path := FileMgt.ServerTempFileName('.pdf');

    // Select all the marked records
    GenJnlLine.MARKEDONLY(TRUE);

    CLEAR(VendRemittance);
    VendRemittance.USEREQUESTPAGE(FALSE);
    // Print marked records
    VendRemittance.SETTABLEVIEW(GenJnlLine);
    VendRemittance.SAVEASPDF(Path);

    SMTPMail.AddAttachment(Path, PdfDocPath);

    TmpFileList.INIT;
    TmpFileList.ID := 1;
    TmpFileList.Name := Path;
    TmpFileList.INSERT;

    SMTPMail.Send;

    IF TmpFileList.FINDSET THEN
      REPEAT
        FileMgt.DeleteServerFile(TmpFileList.Name);
      UNTIL TmpFileList.NEXT = 0;

Xwioch · 2020-07-28

Hi Aarvi.
Your code seems a bit weird to me.
So you need to send an email for each Gen. Journal Line to his vendor with in attachement the Remittance right?
I would say you should repeat over the general journal line first, then get the vendor and at the end use VendRemittance.SETTABLEVIEW passing a variable filtered for the current gen journal line.

I probably will do something like this:

GenJnlLine.COPY("Gen. Journal Line");

IF GenJnlLine.FINDSET THEN	
	REPEAT
		IF locVendor.GET(GenJnlLine."Account No.") AND (locVendor."E-Mail" <> '') THEN BEGIN
			GenJnlLine2.GET(GenJnlLine.PRIMARY KEY... ecc...);
			GenJnlLine2.SETRECFILTER; // Use this one for the settableview
				
			CLEAR(VendRemittance);
        		VendRemittance.USEREQUESTPAGE(FALSE);
        		VendRemittance.SETTABLEVIEW(GenJnlLine2); // variable filtered
        		VendRemittance.SAVEASPDF(Path);
				
			// And then everything you did in your code...
		END;
	UNTIL GenJnlLine.NEXT = 0;

This will send an e-mail for each general journal line in your batch.

I don't really understand where this "Gen. Journal Line" come from.
Anyway I usually run filters and reports over new variable for don't touch the original one and risk to do mistakes with filters.
And why are calling it from the onaftergetrecord? You said you did an action, are you using it or not?
You are calling the function SendMailToVendWithAttachment with the VendNo parameter but you are not using it anywhere.

Moreover I would raccomand you to not use FINDFIRST on loops and what is the purpose of the SLEEP(1000) ?

Aravindh_Navision · 2020-07-28

Hi Xwioch,

Thank you very much for your reply. Answer for your queries and doubts.

1. Yes, I need to send email with remittance advice attachment into it.
2. Gen. Journal Line is a report dataitem.
3. I did it in action tab of Payment Journal. Before posting the payment journal, they will just click a button, which does sending mail to all vendors in that batch.
4. I missed to mention VendNo parameter in the coding part. Actually I gave it as shown below.

CLEAR(SMTPMail);
    CLEARLASTERROR;

    SMTPSetup.GET;
    locVendor.GET(VendNo);

    PdfDocPath := 'Remittance for ' + locVendor.Name + '.pdf';
    Path := 'C:\Aarvi\PDFPath\Remittance' + PdfDocPath;

5. SLEEP(1000) is not required. Missed to remove.

May I know where to write the code that you have given in your reply? In the function that I created?

Thanks again, Aarvi.

Xwioch · 2020-07-28

Ok that's more clear.
I got it know.
So users click your action that run your report and loop over all your general journal line and for each one send the e-mail.

Just do this:
in the SendMailToVendWithAttachment declare a new local variable called GenJnlLine type record "Gen. Journal Line" and then that's your code:

GenJnlLine.GET(GenJnlLine.PRIMARY KEY... ecc...);
GenJnlLine.SETRECFILTER; // Use this one for the settableview
			
CLEAR(VendRemittance);
VendRemittance.USEREQUESTPAGE(FALSE);
VendRemittance.SETTABLEVIEW(GenJnlLine); // variable filtered
VendRemittance.SAVEASPDF(Path);

Just put it in your SendMailToVendWithAttachment and it should works.
The secret is that your must set filter on records before run the report VendRemittance.

Aravindh_Navision · 2020-07-28

I am getting below error.

My final code looks like this..

Function

LOCAL SendMailToVendWithAttachment(VendNo : Code[20])

locVendor.RESET;
locVendor.SETFILTER(locVendor."E-Mail", '%1', '*@*');
locVendor.SETRANGE(locVendor."No.", "Gen. Journal Line"."Account No.");
IF locVendor.FINDFIRST THEN BEGIN
  REPEAT

    CLEAR(SMTPMail);
    CLEARLASTERROR;

    SMTPSetup.GET;
    locVendor.GET(VendNo);
 
    PdfDocPath := 'Remittance for ' + locVendor.Name + '.pdf';
    Path := 'C:\Aarvi\PDFPath\Remittance\' + PdfDocPath;

    GenJnlLine.GET(GenJnlLine."Journal Template Name",GenJnlLine."Journal Batch Name",GenJnlLine."Line No.");
    GenJnlLine.SETRECFILTER; // Use this one for the settableview

    CLEAR(VendRemittance);
    VendRemittance.USEREQUESTPAGE(FALSE);
    VendRemittance.SETTABLEVIEW(GenJnlLine);
    VendRemittance.SAVEASPDF(Path);

    SMTPMail.CreateMessage('Company name',SMTPSetup."User ID",
                            locVendor."E-Mail",'Remittance for ' + locVendor.Name ,'',TRUE);

    SMTPMail.AppendBody('Dear Sir/Madam,');
    SMTPMail.AppendBody('<br><br>');
    SMTPMail.AppendBody('Please find the attachment of remittance herewith.');
    SMTPMail.AppendBody('<br><Br>');
    SMTPMail.AppendBody('Regards,');
    SMTPMail.AppendBody('<br>');
    SMTPMail.AppendBody('Company Name');
    SMTPMail.AppendBody('<br><br>');
    SMTPMail.AppendBody('<HR>');
    SMTPMail.AppendBody('This is a system generated mail. Please do not reply to this email ID.');

    SMTPMail.AddAttachment(Path,PdfDocPath);      
    SMTPMail.Send;

    ERASE(Path);

  UNTIL locVendor.NEXT = 0;
END;

Xwioch · 2020-07-28

This line must be like this

GenJnlLine.GET("Gen. Journal Line"."Journal Template Name","Gen. Journal Line"."Journal Batch Name","Gen. Journal Line"."Line No.");

Use the dataitem variable for get the variable.
I wrote wrong in the previous sample.

Aravindh_Navision · 2020-07-29

Hi Xwioch,

Thanks for your clarification. Now I am getting this error.

Xwioch · 2020-07-29

SMTPMail.AddAttachment function use a .net dll that is not set with property runonclient.
This is the function that really add the file to your mail (just look the code inside SMTPMail.AddAttachment), and Mail is a .net.

Result := Mail.AddAttachmentWithName(Attachment,FileName);

So it will search the file in the server directories and you are saving your file in your client I guess.

That was the explanation for your problem I think.
To avoid this you should save your .pdf in a temporary path in your server.
File Management codeunit does it for you.
Your code should be like this:

Path := FileMgt.ServerTempFileName('.pdf');

It works fine even without the dot. The function will add it.
It will create a file in the server temporary directory.

At the end replace ERASE function with:

FileMgt.DeleteServerFile(Path);

Aravindh_Navision · 2020-07-29

Hi Xwioch,

Yeah, it worked and you are awesome. Thank you very much for your assistance.

Just couple of things.

1. Among the entries in Payment Journal, there are multiple lines related to one single vendor. How do I group those Vendors PJ entries in single PDF. Grouping based on Vendorwise and send in one single mail per Vendor.
2. If there are many mails triggered from this Payment Journal, is there any possibility of network traffic issue. Because the user send 200-300 mails in one shot.

Thanks once again for all your wonderful help.

Xwioch · 2020-07-29

Well if they will send so many mails you're right... maybe they will have some traffic issue or worse, finish in a spam list with their domain.

Usually there are two ways for grouping (at least as I know): temporary record or marks.
I prefer create a temporary variable, fill it with my records and then loop through it for do what I want.
In my example I will fill a temporary record for vendor and then for every vendor I will send an email with in attachment a file for every payment entry.

So we need this two global variables (THEY MUST BE TEMPORARY)

Name	DataType	Subtype	Length
TmpVendor	Record	Vendor	
TmpGenJnlLine	Record	Gen. Journal Line

and then this is the code for your report

Gen. Journal Line - OnPreDataItem()
IF TmpVendor.ISTEMPORARY THEN
  TmpVendor.DELETEALL;

IF TmpGenJnlLine.ISTEMPORARY THEN
  TmpGenJnlLine.DELETEALL;

Gen. Journal Line - OnAfterGetRecord()
IF NOT TmpVendor.GET("Gen. Journal Line"."Account No.") THEN BEGIN
  Vendor.GET("Gen. Journal Line"."Account No.");
  TmpVendor := Vendor;
  TmpVendor.INIT;
END;

IF NOT TmpGenJnlLine.GET("Gen. Journal Line"."Journal Template Name", "Gen. Journal Line"."Journal Batch Name", "Gen. Journal Line"."Line No.") THEN BEGIN
  TmpGenJnlLine := "Gen. Journal Line";
  TmpGenJnlLine.INSERT;
END;

Gen. Journal Line - OnPostDataItem()
TmpVendor.RESET;

IF TmpVendor.FINDSET THEN
  REPEAT
    CLEAR(SMTPMail);

    SMTPSetup.GET;

    SMTPMail.CreateMessage('Company name',SMTPSetup."User ID",
                            TmpVendor."E-Mail",'Remittance for ' + TmpVendor.Name ,'',TRUE);

    SMTPMail.AppendBody('Dear Sir/Madam,');
    SMTPMail.AppendBody('<br><br>');
    SMTPMail.AppendBody('Please find the attachment of remittance herewith.');
    SMTPMail.AppendBody('<br><Br>');
    SMTPMail.AppendBody('Regards,');
    SMTPMail.AppendBody('<br>');
    SMTPMail.AppendBody('Company Name');
    SMTPMail.AppendBody('<br><br>');
    SMTPMail.AppendBody('<HR>');
    SMTPMail.AppendBody('This is a system generated mail. Please do not reply to this email ID.');

    TmpGenJnlLine.RESET;
    TmpGenJnlLine.SETRANGE("Account No.", TmpVendor."No.");

    i := 1;
  
    IF TmpGenJnlLine.FINDSET THEN
      REPEAT
        PdfDocPath := 'Remittance ' + FORMAT(i) + ' for ' + TmpVendor.Name + '.pdf';
        Path := FileMgt.ServerTempFileName('.pdf');

        GenJnlLine.GET(TmpGenJnlLine."Journal Template Name", TmpGenJnlLine."Journal Batch Name", TmpGenJnlLine."Line No.");
        GenJnlLine.SETRECFILTER;

        CLEAR(VendRemittance);
        VendRemittance.USEREQUESTPAGE(FALSE);
        VendRemittance.SETTABLEVIEW(GenJnlLine);
        VendRemittance.SAVEASPDF(Path);

        SMTPMail.AddAttachment(Path, PdfDocPath); 
        
        FileMgt.DeleteServerFile(Path);

        i += 1;
      UNTIL TmpGenJnlLine.NEXT = 0;    
        
    SMTPMail.Send;
  UNTIL TmpVendor.NEXT = 0;

I didn't try it but, I hope it works.
In the onaftergetrecord I'm just filling my variables with the data I need, in particolar vendors.
Then, in onpostdataitem I will loop on vendors, create an e-mail for each one and inside this loop I will repeat over TmpGenJnlLine per create a file for every record and then send.

This is the full txt object I did for sample

OBJECT Report 50150 Test
{
OBJECT-PROPERTIES
{
Date=29/07/20;
Time=20:11:02;
Modified=Yes;
Version List=;
}
PROPERTIES
{
}
DATASET
{
{ 50000; ;DataItem; ;
DataItemTable=Table81;
OnPreDataItem=BEGIN
IF TmpVendor.ISTEMPORARY THEN
TmpVendor.DELETEALL;

IF TmpGenJnlLine.ISTEMPORARY THEN
TmpGenJnlLine.DELETEALL;
END;

OnAfterGetRecord=VAR
Vendor@50000 : Record 23;
BEGIN
IF NOT TmpVendor.GET("Gen. Journal Line"."Account No.") THEN BEGIN
Vendor.GET("Gen. Journal Line"."Account No.");
TmpVendor := Vendor;
TmpVendor.INIT;
END;

IF NOT TmpGenJnlLine.GET("Gen. Journal Line"."Journal Template Name", "Gen. Journal Line"."Journal Batch Name", "Gen. Journal Line"."Line No.") THEN BEGIN
TmpGenJnlLine := "Gen. Journal Line";
TmpGenJnlLine.INSERT;
END;
END;

OnPostDataItem=VAR
Path@50000 : Text;
VendRemittance@50001 : Report 1;
SMTPSetup@50002 : Record 409;
GenJnlLine@50003 : Record 81;
i@50004 : Integer;
PdfDocPath@50005 : Text;
BEGIN
TmpVendor.RESET;

IF TmpVendor.FINDSET THEN
REPEAT
CLEAR(SMTPMail);

SMTPSetup.GET;

SMTPMail.CreateMessage('Company name',SMTPSetup."User ID",
TmpVendor."E-Mail",'Remittance for ' + TmpVendor.Name ,'',TRUE);

SMTPMail.AppendBody('Dear Sir/Madam,');
SMTPMail.AppendBody('<br><br>');
SMTPMail.AppendBody('Please find the attachment of remittance herewith.');
SMTPMail.AppendBody('<br><Br>');
SMTPMail.AppendBody('Regards,');
SMTPMail.AppendBody('<br>');
SMTPMail.AppendBody('Company Name');
SMTPMail.AppendBody('<br><br>');
SMTPMail.AppendBody('<HR>');
SMTPMail.AppendBody('This is a system generated mail. Please do not reply to this email ID.');

TmpGenJnlLine.RESET;
TmpGenJnlLine.SETRANGE("Account No.", TmpVendor."No.");

i := 1;

IF TmpGenJnlLine.FINDSET THEN
REPEAT
PdfDocPath := 'Remittance ' + FORMAT(i) + ' for ' + TmpVendor.Name + '.pdf';
Path := FileMgt.ServerTempFileName('.pdf');

GenJnlLine.GET(TmpGenJnlLine."Journal Template Name", TmpGenJnlLine."Journal Batch Name", TmpGenJnlLine."Line No.");
GenJnlLine.SETRECFILTER;

CLEAR(VendRemittance);
VendRemittance.USEREQUESTPAGE(FALSE);
VendRemittance.SETTABLEVIEW(GenJnlLine);
VendRemittance.SAVEASPDF(Path);

SMTPMail.AddAttachment(Path, PdfDocPath);

FileMgt.DeleteServerFile(Path);

i += 1;
UNTIL TmpGenJnlLine.NEXT = 0;

SMTPMail.Send;
UNTIL TmpVendor.NEXT = 0;
END;
}

}
REQUESTPAGE
{
PROPERTIES
{
}
CONTROLS
{
}
}
LABELS
{
}
CODE
{
VAR
TmpVendor@50000 : TEMPORARY Record 23;
TmpGenJnlLine@50001 : TEMPORARY Record 81;
SMTPMail@50002 : Codeunit 400;
FileMgt@50003 : Codeunit 419;

BEGIN
END.
}
RDLDATA
{
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<AutoRefresh>0</AutoRefresh>
<DataSources>
<DataSource Name="DataSource">
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString />
</ConnectionProperties>
<rd:SecurityType>None</rd:SecurityType>
</DataSource>
</DataSources>
<ReportSections>
<ReportSection>
<Body>
<Height>2in</Height>
<Style />
</Body>
<Width>6.5in</Width>
<Page>
<Style />
</Page>
</ReportSection>
</ReportSections>
<Code>Public Function BlankZero(ByVal Value As Decimal)
if Value = 0 then
Return ""
end if
Return Value
End Function

Public Function BlankPos(ByVal Value As Decimal)
if Value > 0 then
Return ""
end if
Return Value
End Function

Public Function BlankZeroAndPos(ByVal Value As Decimal)
if Value >= 0 then
Return ""
end if
Return Value
End Function

Public Function BlankNeg(ByVal Value As Decimal)
if Value < 0 then
Return ""
end if
Return Value
End Function

Public Function BlankNegAndZero(ByVal Value As Decimal)
if Value <= 0 then
Return ""
end if
Return Value
End Function
</Code>
<Language>=User!Language</Language>
<ConsumeContainerWhitespace>true</ConsumeContainerWhitespace>
<rd:ReportUnitType>Inch</rd:ReportUnitType>
<rd:ReportID>0eeb6585-38ae-40f1-885b-8d50088d51b4</rd:ReportID>
</Report>
END_OF_RDLDATA
}
}

Aravindh_Navision · 2020-07-29

Wow great..!! Thank a lot Xwioch. Yup you got the point. It may result in traffic issues and can get into spam folder.

With respect to your previous solution... I tried with this code, but it is not triggering any mails.

Xwioch · 2020-07-29

Ops, I guess I saw the problem, first part of Gen. Journal Line - OnAfterGetRecord()

IF NOT TmpVendor.GET("Gen. Journal Line"."Account No.") THEN BEGIN
  Vendor.GET("Gen. Journal Line"."Account No.");
  TmpVendor := Vendor;
  TmpVendor.INSERT; // <<<<<<
END;

I did a huge mistake

It wasn't working because the TmpVendor record was empty, this should fix the problem.
I can't see others errors.

For be sure to not get errors consider to write this instead of the previous:

IF NOT TmpVendor.GET("Gen. Journal Line"."Account No.") THEN
  IF Vendor.GET("Gen. Journal Line"."Account No.") THEN BEGIN
    TmpVendor := Vendor;
    TmpVendor.INSERT;
  END;

Aravindh_Navision · 2020-07-29

Though its a mistake, it is a learning stuff for me. You are amazing..!!

What is the above error is about? Wile running the report, am getting this error. I have closed all the previous PDFs opened and closed the session and opened and tried again. Still same error persists.

Am doing this task for the first time, that is why many questions from me. Am sorry for that.

Xwioch · 2020-07-29

Don't worry, is always a bit annoying works with files and mails.
So, the error is probably caused from the mail, because it keeps busy the file (I was hoping not).
I think we must send the e-mail before erase the pdf.

For that purpose I used another temporary record (yes I like them).

Name	DataType	Subtype	Length
TmpFileList	Record	Name/Value Buffer

I use the record Name/Value Buffer (table 823) because has fields with length 250 so we can store the full path length for delete files after sending the e-mail.
THIS MUST BE TEMPORARY TOO.

And then your code:

TmpVendor.RESET;

IF TmpVendor.FINDSET THEN
  REPEAT
    CLEAR(SMTPMail);

    // Just keep this list clean on every vendor loop
    TmpFileList.DELETEALL;

    SMTPSetup.GET;

    SMTPMail.CreateMessage('Company name',SMTPSetup."User ID",
                            TmpVendor."E-Mail",'Remittance for ' + TmpVendor.Name ,'',TRUE);

    SMTPMail.AppendBody('Dear Sir/Madam,');
    SMTPMail.AppendBody('<br><br>');
    SMTPMail.AppendBody('Please find the attachment of remittance herewith.');
    SMTPMail.AppendBody('<br><Br>');
    SMTPMail.AppendBody('Regards,');
    SMTPMail.AppendBody('<br>');
    SMTPMail.AppendBody('Company Name');
    SMTPMail.AppendBody('<br><br>');
    SMTPMail.AppendBody('<HR>');
    SMTPMail.AppendBody('This is a system generated mail. Please do not reply to this email ID.');

    TmpGenJnlLine.RESET;
    TmpGenJnlLine.SETRANGE("Account No.", TmpVendor."No.");

    i := 1;

    IF TmpGenJnlLine.FINDSET THEN
      REPEAT
        PdfDocPath := 'Remittance ' + FORMAT(i) + ' for ' + TmpVendor.Name + '.pdf';
        Path := FileMgt.ServerTempFileName('.pdf');

        GenJnlLine.GET(TmpGenJnlLine."Journal Template Name", TmpGenJnlLine."Journal Batch Name", TmpGenJnlLine."Line No.");
        GenJnlLine.SETRECFILTER;

        CLEAR(VendRemittance);
        VendRemittance.USEREQUESTPAGE(FALSE);
        VendRemittance.SETTABLEVIEW(GenJnlLine);
        VendRemittance.SAVEASPDF(Path);

        SMTPMail.AddAttachment(Path, PdfDocPath);

        // Save che path on the temporary list, I used the i variable as ID
        TmpFileList.INIT;
        TmpFileList.ID := i;
        TmpFileList.Name := Path;
        TmpFileList.INSERT;

        i += 1;
      UNTIL TmpGenJnlLine.NEXT = 0;

    SMTPMail.Send;

    // And then, after send the e-mail delete all the attachments
    IF TmpFileList.FINDSET THEN
      REPEAT
        FileMgt.DeleteServerFile(TmpFileList.Name);
      UNTIL TmpFileList.NEXT = 0;
  UNTIL TmpVendor.NEXT = 0;

Just look on comments, is where I wrote new code.
I can't try it because I don't have an environment available for tests.

Xwioch · 2020-07-29

Don't worry, is always a bit annoying works with files and mails.
So, the error is probably caused from the mail, because it keeps busy the file (I was hoping not).
I think we must send the e-mail before erase the pdf.

So, for this purpose I created a new temporary variable (yes I like them)

Name	DataType	Subtype	Length
TmpFileList	Record	Name/Value Buffer

I'm using the record Name/Value Buffer (table 823) because has fields with length 250 so we can store the full file path for delete it after send the e-mail.
THIS MUST BE TEMPORARY TOO.

And then this is your code:

TmpVendor.RESET;

IF TmpVendor.FINDSET THEN
  REPEAT
    CLEAR(SMTPMail);

    // Keep the list clean on every vendor loop
    TmpFileList.DELETEALL;

    SMTPSetup.GET;

    SMTPMail.CreateMessage('Company name',SMTPSetup."User ID",
                            TmpVendor."E-Mail",'Remittance for ' + TmpVendor.Name ,'',TRUE);

    SMTPMail.AppendBody('Dear Sir/Madam,');
    SMTPMail.AppendBody('<br><br>');
    SMTPMail.AppendBody('Please find the attachment of remittance herewith.');
    SMTPMail.AppendBody('<br><Br>');
    SMTPMail.AppendBody('Regards,');
    SMTPMail.AppendBody('<br>');
    SMTPMail.AppendBody('Company Name');
    SMTPMail.AppendBody('<br><br>');
    SMTPMail.AppendBody('<HR>');
    SMTPMail.AppendBody('This is a system generated mail. Please do not reply to this email ID.');

    TmpGenJnlLine.RESET;
    TmpGenJnlLine.SETRANGE("Account No.", TmpVendor."No.");

    i := 1;

    IF TmpGenJnlLine.FINDSET THEN
      REPEAT
        PdfDocPath := 'Remittance ' + FORMAT(i) + ' for ' + TmpVendor.Name + '.pdf';
        Path := FileMgt.ServerTempFileName('.pdf');

        GenJnlLine.GET(TmpGenJnlLine."Journal Template Name", TmpGenJnlLine."Journal Batch Name", TmpGenJnlLine."Line No.");
        GenJnlLine.SETRECFILTER;

        CLEAR(VendRemittance);
        VendRemittance.USEREQUESTPAGE(FALSE);
        VendRemittance.SETTABLEVIEW(GenJnlLine);
        VendRemittance.SAVEASPDF(Path);

        SMTPMail.AddAttachment(Path, PdfDocPath);

        // Save the file path in our list, I used variable i as ID
        TmpFileList.INIT;
        TmpFileList.ID := i;
        TmpFileList.Name := Path;
        TmpFileList.INSERT;

        i += 1;
      UNTIL TmpGenJnlLine.NEXT = 0;

    SMTPMail.Send;

    // And then after send the e-mail we can delete all files
    IF TmpFileList.FINDSET THEN
      REPEAT
        FileMgt.DeleteServerFile(TmpFileList.Name);
      UNTIL TmpFileList.NEXT = 0;
  UNTIL TmpVendor.NEXT = 0;

I can't try it because I don't have an environment available for tests, but it should works.

Aravindh_Navision · 2020-07-29

Fantastic Xwioch !! Now mail is getting triggered. Interesting and very very useful.

One more thing, in the mail, I am getting multiple PDF attachments instead it should be in one single pdf file. One Remittance page with multiple lines (lines from Gen. Journal Line). How to make the combined pdf attachment for multiple advices?

Xwioch · 2020-07-30

Well, this is up to your Vendor Remittance report.
If you build a well done dataitem the only thing you have to do is pass with the SETTABLEVIEW a filtered Journal Line for what you want to print.
And in this case you don't have to loop anymore on GenJnlLine.

So, I don't remember if SETTABLEVIEW works with temporary records. If it does your code should be like this:

    TmpGenJnlLine.RESET;
    TmpGenJnlLine.SETRANGE("Account No.", TmpVendor."No.");

    PdfDocPath := 'Remittance for ' + TmpVendor.Name + '.pdf';
    Path := FileMgt.ServerTempFileName('.pdf');

    CLEAR(VendRemittance);
    VendRemittance.USEREQUESTPAGE(FALSE);
    // We just ask the report to print all the journal filtered for the current vendor
    // We don't need to loop anymore on TmpGenJnlLine
    VendRemittance.SETTABLEVIEW(TmpGenJnlLine); // <<<<<<
    VendRemittance.SAVEASPDF(Path);

    SMTPMail.AddAttachment(Path, PdfDocPath);

    TmpFileList.INIT;
    TmpFileList.ID := 1; // Just put one, is always a single record now
    TmpFileList.Name := Path;
    TmpFileList.INSERT;

    SMTPMail.AddAttachment(Path, PdfDocPath);

    SMTPMail.Send;

    IF TmpFileList.FINDSET THEN
      REPEAT
        FileMgt.DeleteServerFile(TmpFileList.Name);
      UNTIL TmpFileList.NEXT = 0;

If it doesn't work because the record is temporary we need to use marks for filter.
We have to loop, mark the records on a real variable and then print.
Example:

    TmpGenJnlLine.RESET;
    TmpGenJnlLine.SETRANGE("Account No.", TmpVendor."No.");

    // Reset marks on every vendor loop
    GenJnlLine.RESET;

    IF TmpGenJnlLine.FINDSET THEN
      REPEAT
        // Marks the records we want to print
        GenJnlLine.GET(TmpGenJnlLine."Journal Template Name", TmpGenJnlLine."Journal Batch Name", TmpGenJnlLine."Line No.");
        GenJnlLine.MARK(TRUE);
      UNTIL TmpGenJnlLine.NEXT = 0;

    PdfDocPath := 'Remittance for ' + TmpVendor.Name + '.pdf';
    Path := FileMgt.ServerTempFileName('.pdf');

    // Select all the marked records
    GenJnlLine.MARKEDONLY(TRUE);

    CLEAR(VendRemittance);
    VendRemittance.USEREQUESTPAGE(FALSE);
    // Print marked records
    VendRemittance.SETTABLEVIEW(GenJnlLine);
    VendRemittance.SAVEASPDF(Path);

    SMTPMail.AddAttachment(Path, PdfDocPath);

    TmpFileList.INIT;
    TmpFileList.ID := 1;
    TmpFileList.Name := Path;
    TmpFileList.INSERT;

    SMTPMail.Send;

    IF TmpFileList.FINDSET THEN
      REPEAT
        FileMgt.DeleteServerFile(TmpFileList.Name);
      UNTIL TmpFileList.NEXT = 0;

robbonick · 2020-07-30

Hi,

I had this requirement for a customer running 2017, and just ended up "downgrading" the functionality from Business Central, which worked well. Might be worth taking a look.

https://github.com/microsoft/ALAppExtensions/tree/master/AddOns/UKSendRemittanceAdvice

Aravindh_Navision · 2020-07-30

Marvelous..!! It worked.

Thank you very much for your patience and guidance.

Both the options are working. In the first one, the mail is getting attached with same 2 PDF files, the second one with 1 PDF.

This post will definitely going to be a very useful post for those who check this kind of requirement. Am much happy that I learned a concept that am not used to.

Thank you once again Xwioch !!

Aravindh_Navision · 2020-07-30

Thank you robbonick for your reply and reference link. Sure, I will check on to that too.

Howdy, Stranger!

Quick Links

Categories

E-mail Vendor Remittance from Payment Journal

Best Answer

Answers