Sending automatic email with an attachment/link to a file in OneLake

[zenisekd]

Hi, 

I am relatively new to pipelines and using them. I have constructed a pipeline, that screenshots a table from power bi report (via notebook using semantic link) and saves it in Onelake table and Onelake file (in .xlsx format). So far so good. 

Now I was hoping that I could create a pipeline, which would take the lastest file (or a link to the file, which would allow to download it) and email it to the responsible person. 
The use case is that I snapshot calculated commissions, save it, and send it to HR.

However, I have learned, that a) I cannot attach a file and b) I cannot simply download a file from Onelake (unless using onelake explorer). Now I am not sure what to do. As I want to make it as simple as it can be for the person to get the file or a link to download it. 

Any advice? I was also looking into an option of getting the file from Onelake to OneDrive but that also seems not very straightforward. Most options require Power Automate, which I am not interested in using. 

[AndreyBear]

MSFT Graph API has fine-grain actions over emails, including attachment, CID specs, etc.
Run it through the Web Activity. 
message resource type - Microsoft Graph v1.0 | Microsoft Learn

[jwinchell40]

@zenisekd - Is there a reason why you are unable to use the Dynamic Report Subscriptions in this case?  I'm assuming it is because the recipient doesn't have access to the environment?

Onelake supports the same API's as ADLS Gen2 so you can use the API's to get the content of a Blob (File) and then turn around and attach that to an email.  I did this with Logic Apps and here is an article pointing the general steps in Logic Apps that you can basically code in Pipelines/Python to follow the steps. (https://medium.com/@jakubrzepka/azure-logic-app-send-files-from-blob-storage-using-email-edff69e7c1b...)

I'm not sure if you can use the Outlook Activity in the Pipeline to do the attaching or if you have to call the Outlook API's directly.

[FabianSchut]

Hi @zenisekd,

Microsoft announced the public preview of the OneLake shared access signatures (SAS) for Fabric items: https://support.fabric.microsoft.com/nl-be/blog/onelake-shared-access-signatures-sas-now-available-i....
Would it be possible for you to create this url and send it in the mail? I did not test this myself, but it may be a solution for you.

An alternative is to write a custom python script in a notebook that uploads your file to Sharepoint/OneDrive. It is possible to achieve that without Power Automate. Here is such an example: https://stackoverflow.com/questions/23696705/how-to-upload-a-file-to-sharepoint-site-using-python-sc...

[zenisekd]

Hello,  thank you for your suggestions.

The first suggestion is not suitable, as it share the OneLake, which doesn't support simple download of your files, which is what I seek.

The second suggestion is discussing local file (on a computer) uploading to sharepoint. I mean it could be modified, but I don't have the skill for it.

It will be less hassle to try to ask chatGPT to come up with a code for Notebook to send the file from Onelake to Onedrive and modify it... Watching the code suggestion, I dislike the fact, that I need to insert into the code the credentials. If I change them in time, it will fail. I was hoping that since it is a onedrive/sharepoint on the same account as the onelake, it will be smooth without adding the credentials.

[FabianSchut]

You could also use a service principal for that, so you don't need your credentials. An example of the authorization with service principal can be found here: https://stackoverflow.com/questions/75836585/how-do-i-authorize-a-python-script-to-upload-to-sharepo...