12 Replies Latest reply on Jan 19, 2012 2:03 AM by mhalhead

    Scheduled email of a datalink file


      I’ve got a simple problem I’m sure vcampus member have already solved many times before, hence I’d like to borrow off experience to find the cleanest and most efficient solution.


      I would like to wrtite a ACE routine to send a daily email to user group containing a datalink file with refreshed values for that day. What are some of the best practices to be followed eg: message handler(CDO or Mapi?), how to refresh the spreadsheet etc. All advice would be greatly be greatly appreciated as I rather borrow off existing infrastructure than write everything from scratch.

        • Re: Scheduled email of a datalink file

          I would say use PI Notifications, to deliver email messages with PI values to a group of users. Even with a piece of code from somebody else on the community, using PI ACE to do this would pretty much be writing everything from scratch as opposed to leveraging existing infrastructure...

            • Re: Scheduled email of a datalink file

              point noted on ACE, we are already using Notifications to send small batches of values. We even have a sharepoint, however we have small groups of process engineers sitting off site and not on the same network that need to constantly monitor and analyse a section of the plant. Of course its for commissioning and this is not a long term solution, but their requirement for now has been put very clear to me, a daily spreadsheet in their ibox J . So bearing that in mind I’m trying to be as true to that goal as possible.

            • Re: Scheduled email of a datalink file

              Jason I'm not sure that ACE is really the best tool for this. In order to update the values you will need to open the excel file, call an update on all the data, save the workbook and then send it. ACE can do this but I'm not sure this the approach I would follow.


              What I've done in the past is to create an excel workbook and use a Macro to handle the updating of the data and sending the email. One thing I normally do is copy and paste the values to the worksheets that will be sent; I do this to prevent volitale function from causing problems with users that don't have datalink or who don't have access to our PI servers (like external vendors). Within Excel there are a couple of email methods that can be used; CDO (i.e. direct SMTP) and via Outlook. I've found the CDO method to be easier; the via outlook method gives problems with the outlook security.


              For the schedule side I simply use the Windows scheduler. I admit that this is a bitty solution so I'm interested in hearing more about Steve's option of using PINS to send a structured workbook.


              Steve: Where we use this is for external vendors that provide maintenance on certain equipment. They need the data for monitoring the operation of the equipment (not in real time) from a maintenance perspective. Because they outside of our networks they don't have access to our PI Servers and even if they did I would be sceptical about giving them access as their is data they don't need which is confidential.


              Below is a VBA function that I have which will send the emails with attachements. You will notice that I use an excel sheet to maintain a log of all the mails sent; this is quite useful.

              Sub CDO_Mail()

                  On Error GoTo EmailFailed

                  Dim iMsg As Object
                  Dim iConf As Object
                  Dim Flds As Variant
                  Dim fromAddress As String
                  Dim smtpServer As String
                  Dim smtpPort As Integer
                  Dim addressFormat As String
                  Dim subjectText As String
                  Dim bodyText As String
                  Dim destinationAddresses As String
                  Dim ccAddresses As String
                  Dim bccAddresses As String

                  smtpServer = Settings.Range("E11").Text
                  smtpPort = Settings.Range("E12").Text
                  fromAddress = Settings.Range("E13").Text
                  addressFormat = Settings.Range("E14").Text

                  subjectText = Replace(Settings.Range("E22").Text, "[PlantName]", ARFData.Range("curPlantName").Text)
                  bodyText = Settings.Range("E24").Text
                  bodyText = Replace(bodyText, "[Name]", fromAddress)

                  destinationAddresses = Settings.Range("E17").Text
                  ccAddresses = Settings.Range("E18").Text
                  bccAddresses = Settings.Range("E19").Text

                  Set iMsg = CreateObject("CDO.Message")
                  Set iConf = CreateObject("CDO.Configuration")

                  iConf.Load -1    ' CDO Source Defaults
                  Set Flds = iConf.Fields
                  With Flds
                      .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
                      .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = smtpServer
                      .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = smtpPort
                      .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False
                      '.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
                      If Settings.Range("K16").Text = "yes" Then
                          .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = Settings.Range("E49").Text
                          .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = Settings.Range("E50").Text
                      End If
                  End With

                  Dim rowsCount As Integer
                  rowsCount = Worksheets("Logs").Cells(Rows.Count, "B").End(xlUp).Row

                  With iMsg
                      Set .Configuration = iConf
                      .To = destinationAddresses
                      .CC = ccAddresses
                      .BCC = bccAddresses
                      .from = fromAddress
                      .Subject = subjectText
                      .TextBody = bodyText
                      .AddAttachment Worksheets("Logs").Cells(rowsCount, 2).Text
                      If Worksheets("Logs").Cells(rowsCount, 6).Text <> "" Then
                          .AddAttachment Worksheets("Logs").Cells(rowsCount, 6).Text
                      End If
                  End With

                  Worksheets("Logs").Cells(rowsCount, 4).Value = "Yes"
                  Worksheets("Logs").Cells(rowsCount, 5).Value = Format(Date, "yyyy-MM-dd") & " " & Format(Time, "HH:mm:ss")
                  Worksheets("Dashboard").Range("K4").Value = "Yes"

                  Exit Sub

                  MsgBox "Email could not be sent. Please check that there is a valid Internet connection and that the email settings are correct"
              End Sub

                • Re: Scheduled email of a datalink file

                  Thanks Michael for the post, some good ideas above. I'll also intrested on hearing Steve's thoughts using PI infrastructure to autogen structured workbook.

                    • Re: Scheduled email of a datalink file

                      Michael Halhead

                      I admit that this is a bitty solution so I'm interested in hearing more about Steve's option of using PINS to send a structured workbook.
                      Jason Boddy

                      I'll also intrested on hearing Steve's thoughts using PI infrastructure to autogen structured workbook

                      @Michael and Jason: I'm afraid there is a bit of misunderstanding here...  I didn't say you could use PI Notifications to send an "autogen structured workbook" - I wrote you could use PI Notifications to "deliver email messages with PI values to a group of users". More specifically, I meant you could use PI Notifications to send emails to a group of user, which emails would contain PI values (whatever you under the "Content" tab...). Not use PI Notifications to send a DataLink-enabled Excel spreadsheet.


                      If the requirement is to deliver an actual spreadsheet, then maybe your solution (Excel + VBA + CDO) is the right one. Assuming that the core requirement was to deliver data by email to a group of people, I thought PI Notifications might have helped here. It really depends on how much data you need to send, and what type of data - whether it's current values or calculations using historical data.

                        • Re: Scheduled email of a datalink file

                          Dear vCampus and/or Jason Boddy:


                          Did you ever write the ACE routine?  I want to write an ACE routine to send a monthly report from MatLab.  I think any information you came up with using ACE to schedule the trigger could help me.  Thanks !

                            • Re: Scheduled email of a datalink file
                              Ahmad Fattahi

                              In general scheduling a calculation in PI ACE is either based on a PI tag or on regular time intervals. In the former, whenever a new event happens to the tag the calculation executes while in the latter case, the calculation repeats on specific time intervals (say every 1 minute).


                              There is also another option inside MATLAB which is the Timer object. It is an object within MATLAB that you can leverage to schedule your MATLAB calculations. I would suggest consulting with MATLAB documentation on that.

                                • Re: Scheduled email of a datalink file



                                  There is a nice example in the ACE user guide on how to call Matlab from ACE; I think that it is example 2.


                                  As an alternative you could use the Windows scheduler for scheduling the reports; simply pass a Matlab command and startup file. To get the PI data we are using the PI OLE DB provider. This generally works well. There are however a few caveats:

                                  1. You need Matlab installed on the server
                                  2. There is a limit to the number of Matlabs that can run concurrently; I believe that it is 4 on the standard license.

                                  As a side note point 1 can be remedied by compiling the Matlab. Unfortunately not all Matlab code can be compiled; but it is improving.