Question

How to send an excel sheet as an attachment to mail genetated through MSOGenerateExcelFile

Hi,

I exported a Report definition in excel file through MSOGenerateExcelFile.I want to send the same file as an email attachment through activity.Tried SendEmailWithAttachments.Want to know how to get stream data and .pyData property

***Edited by Moderator: Lochan to update platform capability tags***

Comments

Keep up to date on this post and subscribe to comments

Pega
November 21, 2019 - 12:40am

Hi, 

If you are generating the excel file from MSOGenerateExcelFile activity, it will just generate the file and can download the file to the local system. It will not save the file to any specific location or space.

The activity does not return any content that can be attached to the attachment page. 

The pyData is the path from where the data can be accessed. 

The excel generated from the MSOGenerateExcelFile activity will be stored in the ServiceExport directory. From the directory, the file can be added to the desired location.

Please have a look at these links :

https://community1.pega.com/community/pega-support/question/email-generated-excel-msogenerateexcel-file

https://community1.pega.com/community/pega-support/question/we-are-generating-excel-file-using-activity-msogenerateexcelfile-we

https://community1.pega.com/community/pega-support/question/how-get-excel-file-generated-msogenerateexcelfile-activity-out

https://community1.pega.com/community/pega-product-support/question/saving-excel-file-generated-using-msogenerateexcelfile

November 21, 2019 - 1:18am
Response to mutha1

Hi,

Thanks for your quick response.

I want to pass this file as an attachment to mail.I already refered all links which you provided.But no luck.Can you provide the complete steps

If I pass some static content like .pyAttachments(1).pyData="Test" then the excel is getting generated.But I need the data generated in excel file through MSOGenerateEcxelFile.

My FSFileName is "Deatiledlist.Xlsx" and TemplateRFB is "excel!Resultlist!xlxs" so what will be my pyData in this case

 

 

Pega
November 21, 2019 - 1:44am
Response to Asmas876

Hi, 

With respect to MSOGenerateFile activity, it is not feasible to achieve the above functionality.

Alternately, we can try the scenario by adding the excel file to the attachment page and pass the name as pyData in the activity when we are trying to call SendEmailWithAllAttachments.

1. The MSOGenerateExcelFile activity just generates and saves the file in Db. It does not return any content to the attachment page.

2. We created a sample attachment page and has set the excel properties

The pyData field is hardcoded with the encoded excel data. We uploaded a excel file in the pulse of a work object.

Whenever, we attach any file, the file will be present in Data-WorkAttach-File class. Open the excel file that has been attached.

The file open as XML code. Copy the pyAttachStream value. It is an encoded value. This value has been passed in the pyData parameter.

3. Set the param.AttachmentPage to the page that we have created.

4. Call the SendEmailWithAllAttachments activity and pass the current page as parameter.

Also, all the other activities that are used to generate the excel file without Wo does not return any content to be attached to the attachment page.

Please try this approach.

Thanks,

November 21, 2019 - 2:01am
Response to mutha1

Hi,

We are not attaching the file to work object.We need this without work object attachment.We are doing this Agent.

An agent should retrieve the data from report definition in excel sheet and that excel should be send as an email attachment

Can you help us is there is any other way to achieve this

November 21, 2019 - 2:24am

Hello Asma, By taking reference from "Scheduled Report" functionality, I have developed a working solution which I have kept at below google drive location. You can check and customize as per your requirement.

https://tiny.cc/061lgz

November 21, 2019 - 3:42am
Response to Abhinav_Chaudhary

Hi Abhinav,

Thanks for the code.By using above code I am able to get excel generated as an attachment in mail with content.

Also I have other Issue.Can you please help on this

After exporting report to excel Datetime filed is getting changed from EST to GMT.I want the same EST timings in excel as there in report definition

 

 

November 21, 2019 - 4:20am
Response to Asmas876

Hi Asma, As Pega always maintains DateTime properties in GMT timezone internally, to convert to any specific TimeZone, please use function FormatDateTime in below format - 

@(Pega-RULES:DateTime).FormatDateTime(@DateTime.CurrentDateTime(),"yyyy-MM-dd'T'hh:MM:ss", "EST", "en_US")

Replace @DateTime.CurrentDateTime() with your report specific DateTime property in the above function. Check if this resolves the problem.

November 21, 2019 - 5:40am
Response to Abhinav_Chaudhary

Hi,

When I run report correct time stamp is displayed.So why to use again FormatDatetime function.When I export report time is getting changed from EST to GMT

November 21, 2019 - 5:54am
Response to Asmas876

November 25, 2019 - 3:55am
Response to Abhinav_Chaudhary

Hi Abhinav,

Thanks for all valid information.Able to format date as required.

Along with sending mail,we need to store excel file in some other server location.Can you please suggest how can we do this

 

 

November 25, 2019 - 12:55pm
Response to Asmas876

If the server location is a shared location with write access, you can use Connect File. Otherwise, you can host an FTP server and use Connect FTP to transfer the file to the remote location.