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

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?

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)

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

Select allOpen in new window

There is no error checking.

Hi,
thank you but can you please elaborate more on how exactly would i use it? keep it running in the background?

You create a rule, and have the macro as action. Guess that's best for you to use here.

So, i should save it as ps1 and run in as a rule?

No, that is VBA code, you have to put into the VBA Editor of the Outlook running that rule.

Forgive my ignorance, but i have no idea on how to do it, do you have any reference link for me?

I'll prepare something.

Thank you

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.

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

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

Tried both of them, still got the same error

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

Expected: list separator or )
---------------------------
OK   Help  
---------------------------

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)

Select allOpen in new window

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?....

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.

Hi,

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



what do you think i'm doing wrong?

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)

Select allOpen 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)

Select allOpen in new window

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?

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?

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.

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

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.

The test macro pops up when ever I hit the green play button...

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.

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?

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.

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.

This part

get-aduser -filter {emailaddress -eq '" & strEmail & "'}

Select allOpen 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.

Found the issue, simple bug on my side, amazing solution! thank you!