Solved

NEED a VBScript to query users in an AD Security Group and then send an email to the users manager

Posted on 2009-03-30
13
570 Views
Last Modified: 2012-05-06
I am needing a VBScript that query all the members a security group from active directory, and then email each of the members assigned manager.  

The subject will be the same, however the body of the email will need to have the users name at the begining and then the message will be the same for all. This way they know who the email is regarding.

So I will need to know where to specify the security group the script needs to query, and then where to customize the subject and the body of the email.
0
Comment
Question by:seaninman
  • 8
  • 5
13 Comments
 
LVL 14

Expert Comment

by:rejoinder
Comment Utility
Can you clarify something... did you want the script to loop through a security group and list the members, then for each member, an email will be sent to the manager to include a static subject line and a body which will include the users name?
0
 
LVL 4

Author Comment

by:seaninman
Comment Utility
Exactly correct
0
 
LVL 14

Accepted Solution

by:
rejoinder earned 500 total points
Comment Utility
Can you try this.  It will need you to edit some of the variables so that the right group is selected and that emails are being sent through the right server etc.
I commented the areas that need your attention.
'Enter the LDAP path to the security group in question

Set objGroup = GetObject("LDAP://CN=Accountants,OU=Finance,DC=fabrikam,DC=com")
 

For Each strUser in objGroup.Member

    Set objUser =  GetObject("LDAP://" & strUser)

    strUserName = objUser.CN

    strManagerDN = objUser.Manager

    strUsersEmailAddress = objUser.mail

    if strManagerDN <> "" then

        Set objManager =  GetObject("LDAP://" & strManagerDN)

        strManagerEmailAddress = objManager.mail

        if strManagerEmailAddress <> "" then

            EmailManager strUserName,"your.email@domain.com",strManagerEmailAddress

        end if

    end if

Next
 

Sub EmailManager(UserName,FromField,ManagerEmailAddress)

    On Error Resume Next

    Dim objMessage

    Set objMessage = CreateObject("CDO.Message")

    with objMessage

        .From = FromField				'<--From field being pulled from sub variable

        .To = ManagerEmailAddress			'<--To field being pulled from sub variable

        .Subject = "Here is the subject line to change"	'<--Subject line for you to change

        .TextBody = "Here is the email body followed by the username '" & UserName & "' and finishes with more email body."	'<--Email body for you to change.  Note the break in the line for inserting the user name.

        .Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 

        .Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mailserver"	'<--Enter you email server name here

        .Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 

        .Configuration.Fields.Update

        .Send

    end with 

    Set objMessage = Nothing

End Sub

Open in new window

0
 
LVL 4

Author Comment

by:seaninman
Comment Utility
I am getting an error on Line 4
Error: Object not a collection
0
 
LVL 14

Expert Comment

by:rejoinder
Comment Utility
Is line 2 pointing to the correct security group?
("LDAP://CN=Accountants,OU=Finance,DC=fabrikam,DC=com")
For instance it might look like this&
("LDAP://CN=Test Group,OU=Test,OU=My Groups,DC=domain,DC=com")
Also, can you peek inside the group and double check that it has members and that the membership has propagated to other DC's.
0
 
LVL 4

Author Comment

by:seaninman
Comment Utility
Okay, so I'm not getting any errors now, but its not sending an email.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 4

Author Comment

by:seaninman
Comment Utility
Nevermind... LOL  I forgot to change the mailserver.. :)  Thanks for the help..
0
 
LVL 4

Author Closing Comment

by:seaninman
Comment Utility
Great JOB!!!
0
 
LVL 14

Expert Comment

by:rejoinder
Comment Utility
You are welcome - glad to help out!
0
 
LVL 4

Author Comment

by:seaninman
Comment Utility
One more thing I could use with this script..  Is there a way that we can have it write a log file (txt) that shows who an email was sent to and for what user?
0
 
LVL 14

Expert Comment

by:rejoinder
Comment Utility
OK same as before but this time there will be logging.  See line 1 for the file name.
Like before, please test it out and let me know if you run into any problems.
strFile     = "email-manager-log.csv"

Set objFSO  = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile(strFile, 2, True)

objFile.WriteLine """DATE"",""USER"",""MANAGER"",""ACTION"""
 

'Enter the LDAP path to the security group in question

Set objGroup = GetObject("LDAP://CN=Accountants,OU=Finance,DC=fabrikam,DC=com")
 

For Each strUser in objGroup.Member

    Set objUser =  GetObject("LDAP://" & strUser)

    strUserName = objUser.CN

    strManagerDN = objUser.Manager

    strUsersEmailAddress = objUser.mail

    if strManagerDN <> "" then

        Set objManager =  GetObject("LDAP://" & strManagerDN)

        strManagerEmailAddress = objManager.mail

        if strManagerEmailAddress <> "" then

            EmailManager strUserName,"your.email@domain.com",strManagerEmailAddress

            objFile.WriteLine """" & now() & """,""" & strUserName & """,""" & strManagerEmailAddress & """,""Email sent"""

        end if

    else

        objFile.WriteLine """" & now() & """,""" & strUserName & ""","" "",""Manager not assigned - no email sent"""

    end if

Next
 

Sub EmailManager(UserName,FromField,ManagerEmailAddress)

    On Error Resume Next

    Dim objMessage

    Set objMessage = CreateObject("CDO.Message")

    with objMessage

        .From = FromField				'<--From field being pulled from sub variable

        .To = ManagerEmailAddress			'<--To field being pulled from sub variable

        .Subject = "Here is the subject line to change"	'<--Subject line for you to change

        .TextBody = "Here is the email body followed by the username '" & UserName & "' and finishes with more email body."	'<--Email body for you to change.  Note the break in the line for inserting the user name.

        .Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 

        .Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mailserver"	'<--Enter you email server name here

        .Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 

        .Configuration.Fields.Update

        .Send

    end with 

    Set objMessage = Nothing

End Sub

Open in new window

0
 
LVL 4

Author Comment

by:seaninman
Comment Utility
Works perfect.. THANKS
0
 
LVL 4

Author Comment

by:seaninman
Comment Utility
Here is another one I have that you might be able to help me with...

http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_24293789.html
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

In this article we want to have a look at the directory attributes which are used by Microsoft to store the so called Security Identifiers (SID). These SIDs plays an important role in delegating and granting permissions and in authentication of trus…
This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now