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

Changing the Format of Attachments

Attaching Information

Adding Custom Mail Contents

Adding Notification Reports