How to send an Excel report as an attachment using Qlik Application Automation

[AfeefaTk]

Environment

• Qlik Sense Enterprise SaaS
• Qlik Application Automation

This article describes how an Excel file can be sent as an attachment by using the Send Mail block in Qlik Application Automation.

Automation Structure

The creation of an Excel report from a Qlik Sense straight table is explained in the first part of this article.

Let's walk through the blocks used in the automation workflow to send the Excel file as an attachment using the "Send Mail" block.

1. Open the Excel file saved in Microsoft SharePoint using the "Open File on Microsoft SharePoint" block.
   
   
2. Use the "Send Mail" block from Mail Connector to send the Excel file as an attachment via email.
   
   
   

A JSON file containing the above automation is attached to this article.

Please follow the steps provided in the How to import automation from a JSON file article to import the automation.

Related Content 

• Generate Excel Report 
• Copy data from a Qlik Sense straight table to an Excel sheet 

[TTI-Richard]

Hi,
I'm attaching a file which is under 3 sub folders and I noticed that the filename on the Email attachment includes the folder paths. Is there a way to remove folder paths from the Attachment File?

Thanks!

[PetyaAtanasova]

I'm also looking into the same thing. My file is also located under a couple of folders and the attached file name consists of the full path. If someone has an update, please let us know.

If I try to use the Drive ID leading to the child folder and only put the file name in the Path section, the path section still is looking into finding full path and trowing an error. 

Thank you in advance.

[Sonja_Bauernfeind]

Hello @PetyaAtanasova and @TTI-Richard Let me look into this for you; though it may be necessary to post about your requirements in our Qlik Application Automation forum. Will update you!

All the best,
Sonja 

[poojask123]

Hi @TTI-Richard @PetyaAtanasova 

Were you able to solve the issue on getting the folder and subfolders in the mail attachment ? If so, please guide me with the steps. I am stuck with the same issue now. 

Thanks!

[PetyaAtanasova]

Hi @poojask123, I found a workaround. What I did is I removed the nested folders and started storing my generated reports in the first possible location on my SP Site. That way the file name appeared cleaner for the recipients. 

Later you can implement another automation that will move your files to appropriate location, somewhere in your folder structure.  

I hope this helps. Good Luck! 

[poojask123]

Thank you @PetyaAtanasova 
Tried what you suggested and I am now getting the desired output.

[rolmontero]

Hi there,

I am trying to replicate this solution with my own tweaks; however, I am getting the following error.

My Create Excel Table with Headers block looks like this:

I have created my own template (see image below), I have converted it to base64, I have replaced the values inside the variable, and I am almost sure that my format as table as table option is not working as desire. I do not know if it is done in this way; hard coding the four columns I would like to fill with data. 

I hope you can help me!

Thanks a lot!

[Sonja_Bauernfeind]

Hello @rolmontero 

I recommend posting your requirement in the Qlik Application Automation forum, where our active product experts and your knowledgeable Qlik peers are better able to assist you.

All the best, 
Sonja 

[PetyaAtanasova]

Hello @rolmontero , sorry for the late reply.

One important thing that you need to have on mind when writing tables in Excel is your connection. It should definately be an Admin connection and on the Excel Subscribtion, you need a professional license. It wasn't me who set it up for my Business accounts, but this is a high overview of the conneciton requirements. 

Rest of steps you took look good and you have a proper data output shown below the error. This is what leads me to believe that maybe your license/connection to Excel needs to be modified.   

Another thing that comes to mind, but you may have alrerady done it is checking the size of your base64 code. I usually use a variable to hold my base64 code but a variable holds up to 50 000 characters (unless they recently changed this). I usually get my base64 code, put it in Microsoft Word, break the code into parts < 50 000 characters and add as many variables as i need to ensure i won't face any errors by this. 

Keep me posted and if you found a solution. Good luck! 

[rolmontero]

Hi @PetyaAtanasova,

Thanks a lot for your answer! If I use the JSON file that was shared in this post, everything works fine and with the same Excel connection and Qlik app.

The main change I made was creating my own template and converted it to a base64 Excel file. I also checked, and I have 46.512 characters, whereas the original base64 file had 30.320. I'll try separating the characters with variables.

Thanks a lot!