Create Multiple Sheet Excel Book - Also send on email in BC SaaS

Hello Friends,

Below sample code to save multiple sheet data in final merge sheet

    var     

        ExcelBuf: Record "Excel Buffer" temporary;

        ExcelBuf2: Record "Excel Buffer" temporary;

        ExcelBuf3: Record "Excel Buffer" temporary;

        MergeExcelBuf: Record "Excel Buffer" temporary;

procedure CreateExcelbook()

    begin

        if not SendEmil_gBln then begin

            ExcelBuf.Reset();

            IF ExcelBuf.FindSet() Then begin

                repeat

                    MergeExcelBuf.Init();

                    MergeExcelBuf := ExcelBuf;

                    MergeExcelBuf.Insert();

                until ExcelBuf.Next() = 0;

            end;

            CreateExcelSheet('Sales', TRUE);

            ExcelBuf2.Reset();

            IF ExcelBuf2.FindFirst() THen begin

                repeat

                    MergeExcelBuf.Init();

                    MergeExcelBuf := ExcelBuf2;

                    MergeExcelBuf.Insert();

                until ExcelBuf2.NExt = 0;

            end;

            CreateExcelSheet('Collection', false);

            ExcelBuf3.Reset();

            IF ExcelBuf3.FindSet() Then begin

                repeat

                    MergeExcelBuf.Init();

                    MergeExcelBuf := ExcelBuf3;

                    MergeExcelBuf.Insert();

                until ExcelBuf3.NExt = 0;

            end;

            CreateExcelSheet('Receivables', false);

            CreateExcelBook_Final;

        end else begin

            ExcelBuf.Reset();

            IF ExcelBuf.FindSet() Then begin

                repeat

                    MergeExcelBuf.Init();

                    MergeExcelBuf := ExcelBuf;

                    MergeExcelBuf.Insert();

                until ExcelBuf.Next() = 0;

            end;

            CreateExcelSheet('Sales', TRUE);

            ExcelBuf2.Reset();

            IF ExcelBuf2.FindFirst() THen begin

                repeat

                    MergeExcelBuf.Init();

                    MergeExcelBuf := ExcelBuf2;

                    MergeExcelBuf.Insert();

                until ExcelBuf2.NExt = 0;

            end;

            CreateExcelSheet('Collection', false);

            ExcelBuf3.Reset();

            IF ExcelBuf3.FindSet() Then begin

                repeat

                    MergeExcelBuf.Init();

                    MergeExcelBuf := ExcelBuf3;

                    MergeExcelBuf.Insert();

                until ExcelBuf3.NExt = 0;

            end;

            CreateExcelSheet('Receivables', false);

        end;

    end;

    local procedure CreateExcelSheet(SheetName: Text[250]; NewBook: Boolean)

    begin

        if NewBook then

            MergeExcelBuf.CreateNewBook(SheetName)

        else

            MergeExcelBuf.SelectOrAddSheet(SheetName);

        MergeExcelBuf.WriteSheet(SheetName, CompanyName, UserId);

        MergeExcelBuf.DeleteAll();

        MergeExcelBuf.ClearNewRow();

    end;

    local procedure CreateExcelBook_Final()

    var

        TempBlob_lCdu: Codeunit "Temp Blob";

    begin

        MergeExcelBuf.CloseBook();

        MergeExcelBuf.SetFriendlyFilename('Stock Report');

        MergeExcelBuf.OpenExcel();

    end;

    procedure GetExportExcelFileToBlob(var TempBlob: Codeunit "Temp Blob")

    var

        OutStr: OutStream;

    begin

        MergeExcelBuf.CloseBook();

        MergeExcelBuf.SetFriendlyFilename('Stock Report');

        TempBlob.CreateOutStream(OutStr);

        MergeExcelBuf.SaveToStream(OutStr, true);

    end;

=============

Now below code is use to send email for multiple sheet email

    Clear(FortnightlyReport_lRpt);

    FortnightlyReport_lRpt.SetMail_gFnc(CalcDate('<-CM>', Today), Today);  //T12358-N

    FortnightlyReport_lRpt.UseRequestPage(false);

    FortnightlyReport_lRpt.RunModal;

    FortnightlyReport_lRpt.GetExportExcelFileToBlob(TempBlob_lCdu);

  if not FortnightlyReport_lRpt.GetDataInserted_gBln then

      CurrReport.Break;

  TempBlob_lCdu.CREATEINSTREAM(Instr);

  EmailMessage.AddAttachment('Fortnightly Report.XLSX', 'xlsx', Instr);