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
66 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 16
  • 16
32 Comments
 
LVL 70

Expert Comment

by:Qlemo
ID: 40582468
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
ID: 40582522
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 70

Expert Comment

by:Qlemo
ID: 40584413
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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 

Author Comment

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

Expert Comment

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

Author Comment

by:johnnyjonathan
ID: 40584758
So, i should save it as ps1 and run in as a rule?
0
 
LVL 70

Expert Comment

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

Author Comment

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

Expert Comment

by:Qlemo
ID: 40585110
I'll prepare something.
0
 

Author Comment

by:johnnyjonathan
ID: 40588368
Thank you
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 40588451
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
ID: 40596900
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 70

Expert Comment

by:Qlemo
ID: 40597047
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
ID: 40598129
Tried both of them, still got the same error

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

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

Expert Comment

by:Qlemo
ID: 40598292
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
ID: 40598301
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
 
LVL 70

Expert Comment

by:Qlemo
ID: 40598307
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
ID: 40602904
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 70

Expert Comment

by:Qlemo
ID: 40603001
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
ID: 40603142
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 70

Accepted Solution

by:
Qlemo earned 500 total points
ID: 40603483
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
ID: 40614553
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 70

Expert Comment

by:Qlemo
ID: 40614860
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
ID: 40625866
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 70

Expert Comment

by:Qlemo
ID: 40626340
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
ID: 40626852
The test macro pops up when ever I hit the green play button...
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 40626903
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
ID: 40626940
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 70

Expert Comment

by:Qlemo
ID: 40626952
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
ID: 40628568
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 70

Expert Comment

by:Qlemo
ID: 40628602
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
ID: 40630681
Found the issue, simple bug on my side, amazing solution! thank you!
0

Featured Post

Office 365 Training for Admins

Learn how to provision tenants, synchronize on-premise Active Directory, and implement Single Sign-On with these master level course.  Only from Platform Scholar

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Previously, on our Nano Server Deployment series, we've created a new nano server image and deployed it on a physical server in part 2. Now we will go through configuration.
Auditing domain password hashes is a commonly overlooked but critical requirement to ensuring secure passwords practices are followed. Methods exist to extract hashes directly for a live domain however this article describes a process to extract u…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

737 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