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

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?
johnnyjonathanAsked:
Who is Participating?
 
QlemoConnect With a Mentor Batchelor, Developer and EE Topic AdvisorCommented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
johnnyjonathanAuthor Commented:
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
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
johnnyjonathanAuthor Commented:
Hi,
thank you but can you please elaborate more on how exactly would i use it? keep it running in the background?
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
You create a rule, and have the macro as action. Guess that's best for you to use here.
0
 
johnnyjonathanAuthor Commented:
So, i should save it as ps1 and run in as a rule?
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
No, that is VBA code, you have to put into the VBA Editor of the Outlook running that rule.
0
 
johnnyjonathanAuthor Commented:
Forgive my ignorance, but i have no idea on how to do it, do you have any reference link for me?
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
I'll prepare something.
0
 
johnnyjonathanAuthor Commented:
Thank you
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
johnnyjonathanAuthor Commented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
You've got this choices: Either omit the quotes around the group name, or use ""TEST-GROUP"" or 'TEST-GROUP'.
0
 
johnnyjonathanAuthor Commented:
Tried both of them, still got the same error

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

Expected: list separator or )
---------------------------
OK   Help  
---------------------------
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
johnnyjonathanAuthor Commented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
johnnyjonathanAuthor Commented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
johnnyjonathanAuthor Commented:
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
 
johnnyjonathanAuthor Commented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
johnnyjonathanAuthor Commented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
johnnyjonathanAuthor Commented:
The test macro pops up when ever I hit the green play button...
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
johnnyjonathanAuthor Commented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
johnnyjonathanAuthor Commented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
johnnyjonathanAuthor Commented:
Found the issue, simple bug on my side, amazing solution! thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.