Example Macros
Function to return a string:
Public Function GetCustomSubject(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)
GetCustomSubject = "Your subject goes here."
End Function
Function to return the contents of a text file:
Public Function GetCustomMsgBody( oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory )
Dim fsObj
Dim txtStreamObj
Dim emailbody
Dim strFileName
strFileName = "C:\Messages\msg.txt"
Set fsObj = CreateObject(
"Scripting.FileSystemObject")
Set txtStreamObj = fsObj.OpenTextFile(
strFileName, 1, False, False)
emailbody = txtStreamObj.ReadAll
txtStreamObj.Close
GetCustomMsgBody = emailbody
End Function
Function that takes the revision history as an argument and extracts a field value:
Public Function GetSubject(oCensusApplication, lngDtsRecordID, oExpression, recRevisionHistory)
Dim lngLastRevisionNumber
Dim rstValue
Dim variablename
If oCensusApplication Is Nothing Then
Exit Function
End If
If recRevisionHistory Is Nothing Then
Exit Function
End If
'get the last revision number
GetRevisionNumberRange recRevisionHistory, 0, _
lngLastRevisionNumber
' Get the value for the field from the table
' of the last revision in the given set.
Set rstValue = oCensusApplication.CurrentProject._
DataStores.Item(32). _
GetConnection(1).Execute( _
"SELECT tBriefDescription FROM tblDts_History_
WHERE nID=" & lngDtsRecordID _
& " AND nRevisionNumber=" _
& lngLastRevisionNumber, , 1)
' Build subject line that looks like:
'HELPDESK [Ticket 37] - Cannot print PDF file
If Not rstValue.EOF Then
If Not IsNull(rstValue.Fields(0).Value) Then
GetSubject = "HELPDESK [Ticket " &_
lngDtsRecordID & "] - " &_
rstValue.Fields(0).Value
End If
End If
End Function
In the SELECT statement, tBriefDescription is the internal name for the Summary field. You can find the internal field names in the tName column of the tblDtsFields table in the project definitions database.
Related Topics