Solved

Script to take the sender of an email (in outlook) and add that user to an AD group

Posted on 2015-02-01
32
61 Views
Last Modified: 2016-02-10
Hi,
I'm looking for a vbscript\powershell to take an email sender and add that username to an existing AD group.
anyway to do it?
0
Comment
Question by:johnnyjonathan
  • 16
  • 16
32 Comments
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
The sender is part of the AD already, and this is just a way to automate group membership on request?
And you want to run this how?
0
 

Author Comment

by:johnnyjonathan
Comment Utility
The sender would send an email. Once the email is received in the outlook by a specific user then a script would run to take the sender and add him to an ad group.
I'm thinking maybe to use the outlook rules (one of them can run a script once an email is received)
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
You can either use an event trigger (solely VBA) or a VBA "macro" in combination with a rule.
Because of the naughty nature of AD operations in VBA or VBS, it's most likely best to just use a (Power)shell command to perform the addition.
The trigger macro is
public sub AddToADGroup((Item As Outlook.MailItem)
Dim WshShell, strEmail
  Set WshShell = WScript.CreateObject("WScript.Shell")
  strEmail = Item.SenderEmailAddress
  wshShell.Run("%windir%\powershell -command ""& { " _
     "set-adgroupmember TheGroup -Members (get-aduser -filter {emailaddress -eq '" & strEmail & "'}) " _
     "}"" ",7)
end sub

Open in new window

There is no error checking.
0
 

Author Comment

by:johnnyjonathan
Comment Utility
Hi,
thank you but can you please elaborate more on how exactly would i use it? keep it running in the background?
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
You create a rule, and have the macro as action. Guess that's best for you to use here.
0
 

Author Comment

by:johnnyjonathan
Comment Utility
So, i should save it as ps1 and run in as a rule?
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
No, that is VBA code, you have to put into the VBA Editor of the Outlook running that rule.
0
 

Author Comment

by:johnnyjonathan
Comment Utility
Forgive my ignorance, but i have no idea on how to do it, do you have any reference link for me?
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
I'll prepare something.
0
 

Author Comment

by:johnnyjonathan
Comment Utility
Thank you
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
I've non-english Office, so I cannot provide screenshots, and my description might be slightly off:
In Outlook, press Alt-F11. This opens VBA Editor.
Make sure the select "ThisOutlookSession" on the left pane.
Paste the code of http:#a40584413 into the right pane (code).
Save (Floppy symbol or via File menu), and close VBA Editor.

Now create a rule by selecting:
  on receive
  (maybe further restrictions, like "with words in Subject")
  run a script
click on the resulting hyperlink to get a choice of macros. You should see "AddToADGroup"
  finish

That should be it.
0
 

Author Comment

by:johnnyjonathan
Comment Utility
Hi,
i'm getting the attached error when i tried to save in the VBA editor.
Can you please advise?
2015-02-08-1637.png
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
You've got this choices: Either omit the quotes around the group name, or use ""TEST-GROUP"" or 'TEST-GROUP'.
0
 

Author Comment

by:johnnyjonathan
Comment Utility
Tried both of them, still got the same error

---------------------------
Microsoft Visual Basic for Applications
---------------------------
Compile error:

Expected: list separator or )
---------------------------
OK   Help  
---------------------------
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
Using any of this should work:
  wshShell.Run("%windir%\powershell -command ""& { " _
     "set-adgroupmember Test-Group -Members (get-aduser -filter {emailaddress -eq '" & strEmail & "'}) " _
     "}"" ",7)
  wshShell.Run("%windir%\powershell -command ""& { " _
     "set-adgroupmember ""Test-Group"" -Members (get-aduser -filter {emailaddress -eq '" & strEmail & "'}) " _
     "}"" ",7)
  wshShell.Run("%windir%\powershell -command ""& { " _
     "set-adgroupmember 'TheGroup' -Members (get-aduser -filter {emailaddress -eq '" & strEmail & "'}) " _
     "}"" ",7)

Open in new window

0
 

Author Comment

by:johnnyjonathan
Comment Utility
Something doesn't make sense, it works well as long as i keep the "Test-Group" name, if i change it to the actual name of the group in AD it stops working?....
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 68

Expert Comment

by:Qlemo
Comment Utility
Did you try to issue the command manually in PowerShell with that group?
You can also use -NoExit -Command in above lines to keep PS open, seeing the error message if any.
0
 

Author Comment

by:johnnyjonathan
Comment Utility
Hi,

1. Adding the -NoExit -Command gives me the same error  -NoExit
2. i've tried running the command manually in Powershell, but it will only let me run "get-adgroupmember" not "set-adgroupmember" Powershell



what do you think i'm doing wrong?
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
With http:#a40598307 I wanted you to write:
wshShell.Run("%windir%\powershell -NoExit -Command ""& { " _
     "set-adgroupmember 'users' -Members (get-aduser -filter {emailaddress -eq '" & strEmail & "'}) " _
     "}"" ",7)

Open in new window

But it  is wrong anyway (sorry for that, my fault). The cmdlet is Add-ADGroupMember:
wshShell.Run("%windir%\powershell -NoExit -Command ""& { " _
     "add-adgroupmember 'users' -Members (get-aduser -filter {emailaddress -eq '" & strEmail & "'}) " _
     "}"" ",7)

Open in new window

0
 

Author Comment

by:johnnyjonathan
Comment Utility
Hi,
Thank you but i'm getting the same error...tried to save it despite the error but got a syntax error....
maybe there's a ( or a " somewhere that's wrong?
0
 
LVL 68

Accepted Solution

by:
Qlemo earned 500 total points
Comment Utility
No clue what happened with the code I've tested with, but what I've posted up to now is rubbish :/. Try this one:
Public Sub AddToADGroup(Item As Outlook.MailItem)
Dim WshShell, strEmail
  Set WshShell = CreateObject("WScript.Shell")
  strEmail = Item.SenderEmailAddress
  WshShell.Run "%windir%\powershell -NoExit -Command ""& { " & _
     "add-adgroupmember 'users' -Members (get-aduser -filter {emailaddress -eq '" & strEmail & "'}) " & _
     "}"" ", 7
End Sub

Open in new window

0
 

Author Comment

by:johnnyjonathan
Comment Utility
Hi,
Thanks! looks like it's free of errors, however, it doesn't seem to be doing anything, i've looked at the group and it doesn't change, i've made sure the user that runs outlook has permissions to modify the group, anyway i can run this command on powershell and see if it's stuck or what's causing it not to work?
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
The sender email address might be the issue. But you should see a PowerShell console window as soon as the macro is triggered.
Go into VBA Editor, position cursor at line 5 of my code above (the Run), press F9, and the run the rule on a message. VBA should stop in the marked line. Press F5 to continue running the macro.
0
 

Author Comment

by:johnnyjonathan
Comment Utility
Hi,
i've done as you asked but when i click the Run button it let's me save the macro as a new model? please see printscreen

2015-02-23-1754.png
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
What you showed us makes no sense. What is the Test macro for? Are you using the Test macro, or run via the green triangle, or ...?
Whatever you do, you will not be asked to save the modul.
0
 

Author Comment

by:johnnyjonathan
Comment Utility
The test macro pops up when ever I hit the green play button...
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
Got it. You can only run parameter-less macros directly, anything else has to be run different. You need to "run the rule on a message", as said.
0
 

Author Comment

by:johnnyjonathan
Comment Utility
Ok. But in that case I have no way of knowing what doesn't work. Because it doesn't do anything. Any other way to check it?
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
As soon as the macro is called, it will halt at the lines you marked with F9 in VBA Editor. At last then you know ;-). And you can examine strEmail in e.g. Quick Watch window.
0
 

Author Comment

by:johnnyjonathan
Comment Utility
just tried ti but i don't see any changes, the emails come in but nothing works in the background.
I'm trying to understand which part of the powershell command takes the username from the email address and separates it  from the email address to add to the group - meaning, what part knows to take user@company.com to just "user". - that way i can run the command in powershell and see what happens.
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
This part
get-aduser -filter {emailaddress -eq '" & strEmail & "'}

Open in new window

retrieves the AD user object based on the email address. The AD user object (not the name) then is used for setting membership.
0
 

Author Closing Comment

by:johnnyjonathan
Comment Utility
Found the issue, simple bug on my side, amazing solution! thank you!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…

762 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

6 Experts available now in Live!

Get 1:1 Help Now