Sending emails with Dynamic text & charts through Excel

T

Hi,

Suppose I’m in an organisation where there is a hierarchical structure where there are Regions in metro cities under which Operating Units (OU) operate in smaller cities.

Suppose I publish daily a different report for each OU. Write now I only send dynamic content which only contains text with some formatting for which I got a VBA code but the module is password protected so I don’t know what’s written & also I’m an intermediate excel user but a newbie to VBA programming. I want to extend this functionality to include dynamic charts & graphs for each OU. Something like a dynamic dashboard. And I can email to all the OUs using outlook.

How can I do this? I need a solution which can work on excel 2013 onwards. I’ve seen many videos regarding this but none cater to my needs.

Do you by any means need to send excels as attachment?

Or extract charts/tables from excel and send those via mail?

T

rhitwick said:
Do you by any means need to send excels as attachment?

Or extract charts/tables from excel and send those via mail?

Extracting from excel and sending in the body of the email like an HTML is preferred. In this way one can quickly go through the data without having to wait for the operating system to load excel from attachment.

You can right click on the chart and copy. In gmail or outlook, you can paste as image.

We can display attachment as inline images by using img tag with src="cid:UNIQUE_ID"

*stackoverflow.com/questions/6706891/embedding-image-in-html-email

*stackoverflow.com/questions/34161736/sending-a-chart-in-mail-body

T

khalil1210 said:
You can right click on the chart and copy. In gmail or outlook, you can paste as image.

We can display attachment as inline images by using img tag with src="cid:UNIQUE_ID"

*stackoverflow.com/questions/6706891/embedding-image-in-html-email

*stackoverflow.com/questions/34161736/sending-a-chart-in-mail-body

That is not a practical solution since there are more than 50 operating units. So copying the chart for each and every unit and emailing them manually will require immense amount of time and energy. I think you didn’t get my question.

theterminator said:
That is not a practical solution since there are more than 50 operating units. So copying the chart for each and every unit and emailing them manually will require immense amount of time and energy. I think you didn’t get my question.

Yep.

Correct me if I m wrong, you have reports (.xls or .xlsx files) generated in one folder. You want to read file using a program and get the chart from this file and send html mail with chart content

I think you might want to extract the relevant portion of the sheet and export to something like XML and then use another script to transform it into html table format. You can then embedd this html table in your emails. How you exactly implement this will depends on the organization of your data and what sort of scripting you want to use.

While I won’t be of much of help here, just one suggestion. Since you already have a working VBA module which is password protected, however surprisingly breaking VBA password is an open secret. There are plenty of guides online that explain how to do it without need for any time-consuming brute-force attacks or even any third-party tools! And for you it’s sort of legit since your organization owns the Excel workbook in question. Even VBA password in Excel 2019 can be broken in just a few minutes. All you need is 7zip (free) and any free Hex editor app! Just a guess that may be it's a backdoor left as is and never fixed?

So, I suggest that you study the existing VBA code and see if that can be enhanced or customized to your needs. At least for you & those who want to help you or even if you decide to hire a professional might have some existing reference to start from in case you wish to stick to a VBA solution.

Hi,

Can you check if this can help you?

*www.extendoffice.com/documents/excel/5027-excel-vba-send-email-with-chart.html

T

Thanks for all the replies .. I was dealing with a family emergency hence could not post here … I will try to crack the vba password as the user patkim above pointed out & will check out the link share by rhitwick

Did you try software's like WinAutomation to automate the task or macro to record first time then automate?