The Analytics Edge Core Add-in allows you to fully automate the refresh and delivery of report by email on a schedule of your choosing. It involves several steps:
- setup your email server options
- create the report you want to send
- add a macro with a Save/Email PDF or Workbook command
- schedule the report to run
Setup your email server options
From the Analytics Edge ribbon, click the Options button and navigate to the Email tab. Enter the settings for your email server settings. See: Options
Create the report you want to send
Using Analytics Edge connector and a combination of Quick Queries and/or Macros, get the data you want into your workbook. For examples of reports you can produce, see: Reports
Add a macro with a Save/Email PDF or Workbook command
If you have existing macros in your workbook, add a new macro to the end of your sequence (macros run in alphabetical order). If you do not have any macros, create one.
Safety belt function: I recommend you add a Filter function, testing a location in your report where the should be data if everything worked properly. Create a filter that tests to see if that data is within an expected range, and check the option to Stop macro if empty. If the data does not pass the filter, the macro will not proceed to the next step, and you won’t send a bad report by email to your important clients. See: Filter
To send a PDF file version of your report, add a Save/Email PDF function. To send the workbook itself, add a Save/Email Workbook function. If you want to hide some of the workbook contents, I suggest you put that content on hidden worksheets and use the PDF format — hidden worksheets are not included in the PDF output, but they can be easily unhidden in the Excel workbook format. See: Save/Email PDF or Save/Email Workbook
Schedule the report to run
Save your workbook, then select the Schedule button from the Analytics Edge ribbon. Set the options to refresh the workbook daily, weekly or monthly as desired. See: Schedule Refresh
If you need finer control over the schedule, open the Windows Schedule Manager and locate the scheduled task — it will be named AnalyticsEdge## and the workbook file will be visible in the description. You can change the Trigger, Conditions and Settings to get the refresh schedule you want, but you should not change the General or Actions tab information.