Email Excel Template
The email Excel template option provides a means to use the VTScada email tools to email a report sent to an Excel template. This option is available only in the VTScada Oil & Gas Layer. Your application must use this layer for the email Excel template option to be available in your application.
The following explains how to use the built-in VTScada feature. Instructions for writing VBA code in Excel are beyond the scope of this document. See also: Use an Excel Macro Template.
Preparation:
- You will need to create an Excel macro-enabled template (*.xtlm) file to format data from the VTScada Historian into a suitable report.
- Any formatting that you wish to apply via your macro is up to you and will vary depending on how you would like to summarize and present your report.
- At a minimum, your macro must save the resulting file using the name that VTScada places in cell B1 of tab, "Report Info".
- The macro that you create must be named "Complete".
- Configure your system to enable sending reports by email.
Refer to Email Setup for Alarms and Reports - You will need to configure your Report tag or Reports page to send the report by email.
Refer to the example in the following image:
Report Creation Process
When the report runs, VTScada will create a new Excel file based on your macro-enabled template.
Within that file, VTScada will insert a tab named “Report Info” and populate cells A1 through A7 and B1 through B5 as shown in the following example:
Your macro will run to format the report data. It must save the file using the name in cell B1. It may use cell B6 to provide a more user-friendly name for the file attachment. If your macro will generate a PDF or other file, use cell B7 to specify the absolute path to that file (include the file name) .
| Cell | Title (Column A) | Description |
|---|---|---|
| B1 | Save File Name |
The file name, including the absolute file location. Even if a file is designated in B7, your Excel template must create this file. |
| B2 | Email Recipients | List of recipients that will be sent the report. Created by VTScada and should not be modified by your macro. |
| B3 | Email Subject | The subject that will be used when sending the email. Created by VTScada and should not be modified by your macro. |
| B4 | Email Content | The body of the email to be sent. Created by VTScada and should not be modified by your macro. |
| B5 | Report Type | The type of report that was generated. Created by VTScada and should not be modified by your macro. |
| B6 | Email Attachment File Name | Optional. A user-friendly file name to be given to the attachment when the email is sent. Your VB code is responsible for setting this (will default to "Attachment" if blank) |
| B7 | File to Attach |
Optional. If populated, this file will be sent as the email attachment instead of the file in B1. Your VB Code is responsible to put the absolute path to that file (including file name) in this field. This does not have to be an Excel file; it can be a PDF or other file type. |
Email Service
The VTScada Oil & Gas Layer email service will periodically scan the directory "\YourApp\Data\Email Outbox". The only files in this directory should be the Excel reports created by your Excel template.
Reports in this directory trigger the email service to open the report, process the contents on the tab "Report Info" (column B) and email an attachment, either the Excel report itself or an alternative file.
After a report is sent successfully, the Excel report will be moved to the directory "\YourApp\Data\Email Outbox\Sent".
If a report fails to send, the Excel report will be moved to the directory "\YourApp\Data\Email Outbox\Failed". A log file is maintained in the failed directory, containing a description of any failure.
Application Configuration Settings
| Setting Name | Default | Description |
|---|---|---|
| OGTimeBetweenEmail | 0 | Time to wait between sending email messages using this service |
