myITforum.com : Sending an HTML Message to a SMS Collection

SendCollMail Script. . .This script sends an HTML message via Outlook 2000 to users who are registered to machines in a specific SMS Collection. This script is particularly useful for notifying users of upcoming rollouts.

The script reads in the members of a specific collection, then checks against a custom class (the custom class can be a custom MIF with user information). The custom class is queried to get the NT User ID of the owner of that machine which, in turn, is used as the recipient's email address in an Exchange environment.

If it cannot find that information for a specific machine (which sometimes happens with custom MIFs) it falls back to the last logged on user ID.

NOTE: Logging capability is also built in.

Copy and paste the following script (between the lines) into Notepad, making sure to have Word Wrap disabled, then save it with a .vbs extension.

==================================
CONST arrResourceID = 0, arrMachineName = 1, arrUserID = 2, arrMax = 2
CONST ForReading = 1, ForWriting = 2

Set objArgs = WScript.Arguments
If objArgs.count <> 4 Then
Wscript.Echo "USAGE: SendCollMail.vbs [CollectionName] [MessageFile(HTML)] [Server] [SiteCode] "
Wscript.Echo ""
Wscript.Echo " Pull user information for a collection"
Wscript.Echo ""
Wscript.Quit 1
End If

' Place custom class and column where UserID's can be retrieved here
strSMSIdentityClass = "SMS_G_System_CUSTOMCLASS"
strSMSIdentityColumn = "UserID"

' Assign arguments to variables and create WMI connection string
CollName = objArgs(0)
MessageFile = objArgs(1)
Server = objArgs(2)
SiteCode = objArgs(3)
SMSLoc = "winmgmts:\\" + Server + "\root\SMS\site_" + SiteCode

' Create Log file
Set fso = CreateObject("Scripting.FileSystemObject")
strLogFileName = "c:\mailtocoll" + cstr(datepart("m", Now)) + cstr(datepart("d", Now)) + cstr(datepart("h", Now)) + cstr(datepart("n", Now)) + ".log"
Set oLogFile = fso.CreateTextFile(strLogFileName, ForWriting, True)

' Read the HTML Message File into the variable strHTMLMessage
On Error Resume Next
Set oMessageFile = fso.OpenTextFile(MessageFile, ForReading)
If Err.Number <> 0 Then
WriteLog("Text file could not be opened: Error Number " + cstr(Err.Number) + ": " + Err.Description)
oLogFile.close
Wscript.Quit
Else
WriteLog("Text file opened successfully.")
End If
strHTMLMessage = oMessageFile.ReadAll
If Err.Number <> 0 Then
WriteLog("HTML message could not be read: Error Number " + cstr(Err.Number) + ": " + Err.Description)
oLogFile.close
Wscript.Quit
Else
WriteLog("HTML message read successfully.")
End If
Set oMessageFile = Nothing

' Get CollectionID for the Collection named in the argument.
sQuery = "SELECT CollectionID from SMS_Collection WHERE name = " + CHR(34) + CollName + CHR(34)
Set CollectionSet = GetObject(SMSLoc).ExecQuery(sQuery)

' If more or less than one CollectionID is found, stop the process.
If CollectionSet.Count <> 1 Then
WriteLog("Query found " + cstr(CollectionSet.Count) + " entries in the collection. Can only work with one. Make collection criteria more or less specific.")
oLogFile.close
Wscript.Quit
End If

' Assign CollectionID to Variable CollID.
For each Collection in CollectionSet
CollID = Collection.CollectionID
WriteLog("Found Collection ID " + CollID + ".")
Next

' Get the resourceid and machinename for each member of the collection, sorted by resourceid.
sQuery1 = "Select ResourceID, Name from SMS_CM_RES_COLL_" & CollID & " ORDER by ResourceID"
Set QuerySet = GetObject(SMSLoc).ExecQuery(sQuery1)

' Create a query that will get the UserIDs for the resourceid's pulled from the Collection
' and place the machine name in the array arrResource
sQuery2 = "Select ResourceID, " & strSMSIdentityColumn & " from " & strSMSIdentityClass & " WHERE "&_
"ResourceID IN ("
Dim arrResource()
UserIDCount = QuerySet.Count ' Used to control the commas put in the "IN" statement of the query
ResourceArrayCount = QuerySet.Count-1 ' Used to redim the array
ReDim arrResource(ResourceArrayCount, arrMax)
WriteLog("Found " + cstr(QuerySet.Count) + " machines in collection " + CollName + ".")
Count = 0
For each item in QuerySet
sQuery2 = sQuery2 + cstr(item.ResourceID)
arrResource(count, arrResourceID) = item.ResourceID
arrResource(count, arrMachineName) = item.Name
Count = Count + 1
If UserIDCount > Count Then
SQuery2 = SQuery2 + ", "
End If
Next
sQuery2 = sQuery2 + ") Order by ResourceID"

' Get UserID, sorted by ResourceID, and put into array arrResource
Set UIDSet = GetObject(SMSLoc).ExecQuery(sQuery2)
count = 0
For each UID in UIDSet
Do While UID.ResourceID <> arrResource(count, arrResourceID)
count = count + 1
Loop
arrResource(count, arrUserID) = UID.UserID
Next

' Now we take the array information gathered and use it to send the e-mails
For x=0 to UBound(arrResource)
If arrResource(x, arrUserID) = "NOTFOUND" or arrResource(x, arrUserID) = "" Then
WriteLog("UserID " + arrResource(x, arrUserId) + " was not found for machinename " + arrResource(x, arrMachineName) + ". Looking for Last Login ID")
sQuery3 = "Select LastLogonUserName from SMS_R_System WHERE ResourceID = " + cstr(arrResource(x, arrResourceID))
Set LogonSet = GetObject(SMSLoc).ExecQuery(sQuery3)
For each Logon in LogonSet
LastLogonUser = Logon.LastLogonUserName
wscript.echo LastLogonUser
Next
SendMail LastLogonUser, arrResource(x, arrMachineName), strHTMLMessage
Else
SendMail arrResource(x, arrUserID), arrResource(x, arrMachineName), strHTMLMessage
End If
Next
oLogFile.close

'*************************************************************
' Functions and Subroutines
'*************************************************************

Sub SendMail(strUserID, strMachineName, strBody)
olMailItem = 0
strSubject = "IMPORTANT: SMS Software Rollout for workstation " + strMachineName
strBody = strHTMLMessage
strTo = strUserID
strFrom = "SMS Support"

On Error Resume Next
Set otlOutlook = CreateObject("outlook.application")
Set otlMailItem = otlOutlook.createItem(olMailItem)
If Err.Number<>0 Then
WriteLog("Unable to retrieve Outlook Mail object - Error No. " + cstr(Err.Number) + ": " + Err.Description + ".")
oLogFile.close
Wscript.Quit
End If
otlMailItem.subject = strSubject
otlMailItem.htmlbody = strBody
otlMailItem.SentOnBehalfOfName = strFrom
otlMailItem.to = strTo
otlMailItem.send
Set otlMailItem = Nothing
Set otlOutlook = Nothing
If Err.Number<>0 Then
WriteLog("Could not send email to " + strUserID + " for machine " + strMachineName + " because of Error No. " + cstr(Err.Number) + ": " + Err.Description)
Else
WriteLog("Email sent to " + strUserID + " successfully for machine " + strMachineName + ".")
End If
End Sub

Function WriteLog(message)
wscript.echo message
oLogFile.writeline(cstr(Now) + " - " + message)
End Function
==================================

NOTE: Make sure you have the latest scripting engines on the workstation you run this script from. Download the latest scripting engines here: http://msdn.microsoft.com/scripting/

For VB Scripts that interact with Windows Management Instrumentation (WMI), apply the most current version of the WMI agents. They are downloadable here: http://msdn.microsoft.com/code/sample.asp?url=/msdn-files/027/001/576/msdncompositedoc.xml.

For scripts that interact with the file system, you can learn more about the file system object (fso) from Microsoft's FileSystemObject User's Guide: http://msdn.microsoft.com/scripting/vbscript/doc/jsFSOTutor.htm


		myITforum.com ©2010 \| Legal \| Privacy