Macro - Auto-email for monthly KPIs updates across all departments

[whiteswan]

Dear,

I hope your day went well!

I need help in writing the one VBA to create an automatic email for monthly KPIs updating across all departments with the live Excel file (link file), I want to send a separate e-mail to each proponent with its live link so they can update their inputs, I also want the auto email sent every 1st of the month, with a reminder email after 6 working days, please note the the working days in my country from Sunday to Thursday.

such as business development has its own excel file to update the KPIs, same for all department.

It would be great if you could help me!

 

[jerry12302]

Does each department have its own unique link to an Excel file?

Is the Excel file for each department saved on a local network, or on a website?

Would each email address correspond to the same department every month?

If so, I would start by creating an Excel file that one person would be responsible for maintaining, that contains a table of email addresses and their associated departments, and another table listing the departments and their links. That file would also contain VBA code to send the emails, and the code would know which link to include in the body of each email based on the tables.

That person would setup their local Windows OS to open that Excel file on the 1st of every month (and reminders 6 days later). The Excel file should have the VBA run automatically upon opening. The emails would be sent from that file.

 

[cwstudio2020]

I think what you need is a complete program to handle this process. The workflow should be roughly as follows:

1. On the 1st of each month, the system retrieves KPIs for each department from the KPI database (which could also be an Excel sheet) and sends them individually to each department head.
2. The department heads fill out the KPI data and return the completed forms to you. You then store this KPI data in your KPI database.
3. If a department head does not reply within 6 working days, send a reminder email to that person.

If my understanding is correct, this task would require a comprehensive program to support it. VBA can handle part of it, such as sending emails. However, receiving emails and importing the KPI data from department heads cannot be fully managed with VBA alone. For a complete solution, you might want to consider other tools.