Incrementing recordset help

[gmagerr]

Hi all
I've got a password notification script that I run nightly. It checks when the users passwords are going to expire and sends out notifications via email. I'd like to add a counter and send an email to the sysadmins group saying something like 87 notifications sent. I tried it but the results were way off. Can someone assist?
Here's the portion of the code that loops through the recordset.

Thanks.

Do Until objRecordSet.EOF

	strName = objRecordset.Fields("samAccountName").Value
	strEmployeeID = objRecordset.Fields("employeeID").Value
	strEmailAddress = objRecordSet.Fields("mail").value
'    strFirstName = objRecordSet.Fields("givenName").Value
'    strLastName = objRecordset.Fields("SN").Value
'    If strName = "" Then
'    strName = objRecordSet.Fields("samAccountName").Value
'    Else
'    strName = strFirstName & " " & strLastName
'    End If
    strCN = objRecordSet.Fields("cn").Value
    
If strEmployeeID <> "" Then

    'objFile.WriteLine "NT Name: " & strName & ", Common Name: " & strCN
    Set objPwdLastSet = objRecordset.Fields("pwdLastSet").Value

    strPasswordChangeDate = Integer8Date(objPwdLastSet, lngTZBias)
    'objFile.WriteLine vbTab & "Password last changed at " & strPasswordChangeDate
    intPassAge = DateDiff("d", strPasswordChangeDate, Now)
    'objFile.WriteLine vbTab & "Password changed " & intPassAge & " days ago"
    
    CalcDate = (PasswordExpiry - intPassAge ) 
    strChangeDate = DateAdd("d",CalcDate,Now()) 
	strPWChangeDate = FormatDateTime(strChangeDate, vbLongDate)
    
    If intPassAge = (PasswordExpiry - 1) Then
    objFile.WriteLine strName & vbCrLf & "Your password expires in 1 day"
    objFile.WriteLine strEmailAddress & vbCrLf
    Call SendEmailMessage(strEmailAddress, 1)
    
    ElseIf intPassAge = (PasswordExpiry - 2) Then
    objFile.WriteLine strName & vbCrLf & "Your password expires in 2 days"
    objFile.WriteLine strEmailAddress & vbCrLf
    Call SendEmailMessage(strEmailAddress, 2)
    
    ElseIf intPassAge = (PasswordExpiry - 3) Then
    objFile.WriteLine strName & vbCrLf & "Your password expires in 3 days" 
    objFile.WriteLine strEmailAddress & vbCrLf
    'Call SendEmailMessage(strEmailAddress, 3)
    
    ElseIf intPassAge = (PasswordExpiry - 4) Then
    objFile.WriteLine strName & vbCrLf & "Your password expires in 4 days"
    objFile.WriteLine strEmailAddress & vbCrLf
    Call SendEmailMessage(strEmailAddress, 4)
    
    ElseIf intPassAge = (PasswordExpiry - 5) Then
    objFile.WriteLine strName & vbCrLf & "Your password expires in 5 days"
    objFile.WriteLine strEmailAddress & vbCrLf
    Call SendEmailMessage(strEmailAddress, 5)
    
    ElseIf intPassAge = (PasswordExpiry - 6) Then
    objFile.WriteLine strName & vbCrLf & "Your password expires in 6 days"   
    objFile.WriteLine strEmailAddress & vbCrLf
    Call SendEmailMessage(strEmailAddress, 6)
    
    ElseIf intPassAge = (PasswordExpiry - 7) Then
    objFile.WriteLine strName & vbCrLf & "Your password expires in 7 days"
    objFile.WriteLine strEmailAddress & vbCrLf
    Call SendEmailMessage(strEmailAddress, 7)
       
    ElseIf intPassAge = (PasswordExpiry - 14) Then
    objFile.WriteLine strName & vbCrLf & "Your password expires in 14 days"
	objFile.WriteLine strEmailAddress & vbCrLf
    Call SendEmailMessage(strEmailAddress, 14)
    
'    ElseIf intPassAge > (PasswordExpiry) Then
'    objFile.WriteLine strName & vbCrLf & "Your password is " & intPassAge & " days old. "
'    objFile.WriteLine strEmailAddress & vbCrLf
    
    End If
End If

objRecordset.MoveNext
Loop

objConnection.Close

 

[mharroff]

Prior to you loop set a variable equal to 0 (eCounter in my example). Within you loop whenever you call SendEmailMessage add 1 to the variable (eCounter = eCounter +1)


Also, since the ElseIf are basically identical you can reduce the code like below.

eCounter=0
Do Until objRecordSet.EOF

    strName = objRecordset.Fields("samAccountName").Value
    strEmployeeID = objRecordset.Fields("employeeID").Value
    strEmailAddress = objRecordSet.Fields("mail").value
'    strFirstName = objRecordSet.Fields("givenName").Value
'    strLastName = objRecordset.Fields("SN").Value
'    If strName = "" Then
'    strName = objRecordSet.Fields("samAccountName").Value
'    Else
'    strName = strFirstName & " " & strLastName
'    End If
    strCN = objRecordSet.Fields("cn").Value
    
If strEmployeeID <> "" Then

    'objFile.WriteLine "NT Name: " & strName & ", Common Name: " & strCN
    Set objPwdLastSet = objRecordset.Fields("pwdLastSet").Value

    strPasswordChangeDate = Integer8Date(objPwdLastSet, lngTZBias)
    'objFile.WriteLine vbTab & "Password last changed at " & strPasswordChangeDate
    intPassAge = DateDiff("d", strPasswordChangeDate, Now)
    'objFile.WriteLine vbTab & "Password changed " & intPassAge & " days ago"
    
    CalcDate = (PasswordExpiry - intPassAge ) 
    strChangeDate = DateAdd("d",CalcDate,Now()) 
    strPWChangeDate = FormatDateTime(strChangeDate, vbLongDate)    intExpiresIn = PasswordExpiry - intPassAge
    If (intExpiresIn >= 1 And intExpiresIn <= 7) Or intExpiresIn = 14 Then
        objFile.WriteLine strName & vbCrLf & "Your password expires in " & intExpiresIn & " day"
        objFile.WriteLine strEmailAddress & vbCrLf
        Call SendEmailMessage(strEmailAddress, intExpiresIn)
        eCounter = eCounter + 1
    End If

objRecordset.MoveNext
Loop

 

[mharroff]

Oops,

strPWChangeDate = FormatDateTime(strChangeDate, vbLongDate)    intExpiresIn = PasswordExpiry - intPassAge

should be 2 lines like

strPWChangeDate = FormatDateTime(strChangeDate, vbLongDate)    
intExpiresIn = PasswordExpiry - intPassAge

 

[gmagerr]

mharroff

Thank you so much for your help.