Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics NAV (Archived) / While sending mail tha...
Microsoft Dynamics NAV (Archived)

While sending mail that I was selected data that showing in mail table Format but I want to insert data into Excel sheet and send particular vendor.

(0) ShareShare
ReportReport
Posted on by NeoMatrix 240

tableData.PNG

That is data it shown in mail but  I want import data into excel sheet while sending mail and send particulat vendor.

OnRun()
SMTPSetup.GET;
PurchRcptLine.RESET;
PurchRcptLine.SETRANGE("Qty. Rcd. Not Invoiced",PurchRcptLine."Qty. Rcd. Not Invoiced");
PurchRcptLine.SETFILTER(PurchRcptLine."Qty. Rcd. Not Invoiced",'>0');
VendNo:='10000';
Vendor.GET(VendNo);
Vendor.TESTFIELD("E-Mail");
SMTPMail.CreateMessage(SMTPSetup."Email Sender Name",SMTPSetup."Email Sender Email",Vendor."E-Mail",'Test Pending Receipt','',TRUE);
SMTPMail.AppendBody('<table border="1">' );
SMTPMail.AppendBody('<tr><th>Vendor No</th><th>Document No</th><th>No</th><th>Qty</th></tr>' );
IF PurchRcptLine.FINDFIRST THEN BEGIN
  REPEAT
SMTPMail.AppendBody('<tr><td>'+ PurchRcptLine."Buy-from Vendor No." 
  +'</td><td>'+ PurchRcptLine."Document No." 
  + '</td><td>'+ PurchRcptLine."No." 
  +'</td><td> '+PurchRcptLine."No."
  +' </td> </tr>' );
UNTIL PurchRcptLine.NEXT=0;

SMTPMail.Send;
MESSAGE('Mail Send');
END


*This post is locked for comments

  • Verified answer
    ManishS Profile Picture
    ManishS 6,578 on at
    RE: While sending mail that I was selected data that showing in mail table Format but I want to insert data into Excel sheet and send particular vendor.

    SMTPmail.addattachment();

  • NeoMatrix Profile Picture
    NeoMatrix 240 on at
    RE: While sending mail that I was selected data that showing in mail table Format but I want to insert data into Excel sheet and send particular vendor.

    Excel sheet Create but I want to send attachement  to vendor. so what is code for attachement

  • NeoMatrix Profile Picture
    NeoMatrix 240 on at
    RE: While sending mail that I was selected data that showing in mail table Format but I want to insert data into Excel sheet and send particular vendor.

    I write the following code to save the report data into excel 

    Documentation()

OnInitReport()
ExcelBuffer.DELETEALL;

OnPreReport()

OnPostReport()
//ExcelBuffer.CreateBookAndSaveExcel('','Sheet 1','Report',COMPANYNAME,USERID);
ExcelBuffer.CreateBookAndOpenExcel('','Sheet1','REport',COMPANYNAME,USERID);
Purch. Rcpt. Line - OnPreDataItem()
ExcelBuffer.NewRow;
ExcelBuffer.AddColumn('Vendor No',FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuffer."Cell Type"::Text);
ExcelBuffer.AddColumn('Document No',FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuffer."Cell Type"::Text);
ExcelBuffer.AddColumn('Item',FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuffer."Cell Type"::Text);
ExcelBuffer.AddColumn('Location Code',FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuffer."Cell Type"::Text);
ExcelBuffer.AddColumn('GRN Qty',FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuffer."Cell Type"::Number);

Purch. Rcpt. Line - OnAfterGetRecord()
ExcelBuffer.NewRow;
ExcelBuffer.AddColumn(PurchRcptLine."Buy-from Vendor No.",FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuffer."Cell Type"::Text);
ExcelBuffer.AddColumn(PurchRcptLine."Document No.",FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuffer."Cell Type"::Text);
ExcelBuffer.AddColumn(PurchRcptLine."No.",FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuffer."Cell Type"::Text);
ExcelBuffer.AddColumn(PurchRcptLine."Location Code",FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuffer."Cell Type"::Text);
ExcelBuffer.AddColumn(PurchRcptLine."Qty. Rcd. Not Invoiced",FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuffer."Cell Type"::Number);

Purch. Rcpt. Line - OnPostDataItem()

    but it gives the error like
    4338.Capture1.PNG


  • Verified answer
    ManishS Profile Picture
    ManishS 6,578 on at
    RE: While sending mail that I was selected data that showing in mail table Format but I want to insert data into Excel sheet and send particular vendor.

    Design a report in layout mode and use the standard code of report.saveasexcel in you code to execute the report.

    PurchRcptLine.RESET;

    PurchRcptLine.SETRANGE("Qty. Rcd. Not Invoiced",PurchRcptLine."Qty. Rcd. Not Invoiced");

    PurchRcptLine.SETFILTER(PurchRcptLine."Qty. Rcd. Not Invoiced",'>0');

    PurchRcpt.SETTABLEVIEW(PurchRcptLine);

    FileName := 'D:\ReportFile\'+ '.XLS';

    PurchRcpt.SAVEASEXCEL(FileName);

    PurchRcpt is a report global variable.

  • NeoMatrix Profile Picture
    NeoMatrix 240 on at
    RE: While sending mail that I was selected data that showing in mail table Format but I want to insert data into Excel sheet and send particular vendor.
     I bulid Report. It open in Excel sheet when it click on button, but I dont know where write code to save the excel sheet

    OnInitReport()
PrintToExcel:=TRUE

OnPreReport()
ExcelBuffer.DELETEALL;

OnPostReport()
IF PrintToExcel THEN CreateExcelBook;

Purch. Rcpt. Line - OnPreDataItem()
MakeExcelDataHeader;

Purch. Rcpt. Line - OnAfterGetRecord()
 IF PrintToExcel THEN MakeExcelDataBody;

Purch. Rcpt. Line - OnPostDataItem()

LOCAL MakeExcelDataHeader()
ExcelBuffer.AddColumn("Purch. Rcpt. Line".FIELDCAPTION("Buy-from Vendor No."),FALSE,'',TRUE,FALSE,TRUE,'',ExcelBuffer."Cell Type"::Text);
ExcelBuffer.AddColumn("Purch. Rcpt. Line".FIELDCAPTION("Document No."),FALSE,'',TRUE,FALSE,TRUE,'',ExcelBuffer."Cell Type"::Text);
ExcelBuffer.AddColumn("Purch. Rcpt. Line".FIELDCAPTION("No."),FALSE,'',TRUE,FALSE,TRUE,'',ExcelBuffer."Cell Type"::Text);
ExcelBuffer.AddColumn("Purch. Rcpt. Line".FIELDCAPTION("Location Code"),FALSE,'',TRUE,FALSE,TRUE,'',ExcelBuffer."Cell Type"::Text);
ExcelBuffer.AddColumn("Purch. Rcpt. Line".FIELDCAPTION("Qty. Rcd. Not Invoiced"),FALSE,'',TRUE,FALSE,TRUE,'',ExcelBuffer."Cell Type"::Number);

LOCAL MakeExcelDataBody()
ExcelBuffer.NewRow;
ExcelBuffer.AddColumn("Purch. Rcpt. Line"."Buy-from Vendor No.",FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuffer."Cell Type"::Text);
ExcelBuffer.AddColumn("Purch. Rcpt. Line"."Document No.",FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuffer."Cell Type"::Text);
ExcelBuffer.AddColumn("Purch. Rcpt. Line"."No.",FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuffer."Cell Type"::Text);
ExcelBuffer.AddColumn("Purch. Rcpt. Line"."Location Code",FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuffer."Cell Type"::Text);
ExcelBuffer.AddColumn("Purch. Rcpt. Line"."Qty. Rcd. Not Invoiced",FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuffer."Cell Type"::Number);

LOCAL CreateExcelBook()
ExcelBuffer.CreateBookAndOpenExcel('',Text007,'','','');
ERROR('');



  • ManishS Profile Picture
    ManishS 6,578 on at
    RE: While sending mail that I was selected data that showing in mail table Format but I want to insert data into Excel sheet and send particular vendor.

    You need to develop a report first, then execute the report and save it to excel on a location. Then attached it in the email via C/AL code and send it.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans