Exchange 2007 Mail Contacts

IGB · November 10, 2009

Hi there,

We currently subscribe to ParentMail to distribute information to the parents that prefer it sent via email.

What I'd like to do is utilise our own Exchange 2007 server to do a similar role instead of paying for someone else to do it.

I have a report set up in SIMS that can output the data required (First Name, Surname, Email address) that I'd like to add to our Exchange as Mail Contacts. However I'd rather this be done via an import than a manual entry.

Any ideas?

fawkers · November 11, 2009

Hello

I've recently done this using a sims export and a powershell script.

The sims report was run against current students getting the following information: Year, Reg, Name, Full Name, Home Email, Work Email, Parental Responsibility Forname, Surname and outputting it to a CSV.

I've then run the CSV with this powershell script

Add-PSSnapin Microsoft.Exchange.Management.Powershell.Admin

Function decideEmail([string]$homeEmail,[string]$workEmail)
{
   #Check for email, home email wins
   if($homeEmail -ne '')
   {
       return $homeEmail
   }#end if
   else {return $workEmail}#end else
}#end function decideEmail

Function runImport([string]$filePath)
{    
   #Import CSV and iterate though
   Import-CSV $filePath | foreach-object{
       
       #Check that Relationship is Mother or Farther
       if($_.Correspondence -eq 'T')
       {
           $email = decideEmail $_.'home email' $_.'work email';
           
           New-MailContact -Name '$_.Full Name' -ExternalEmailAddress $email -DisplayName $_.'Full Name' -FirstName $_.forename -LastName $_.surname -OrganizationalUnit 'ou=test,ou=Creation,dc=DOMAIN,dc=local' -PrimarySmtpAddress $email -whatif
           
       } #end if
   } #end for
} #end function runImport

#Run Import
runImport 's:\temp\forms\XXXX.csv';

Hope this helps, oh and dont for get to take the safty off before you run it (-whatif)

fawkers · November 11, 2009

I had a more up to date one, this one uses the same sims report but we have distribution groups pre-created for Parents, Parents Year x , Parents REGGROUP and automaticly poplulates them.


Add-PSSnapin Microsoft.Exchange.Management.Powershell.Admin
Add-pssnapin Quest.ActiveRoles.ADManagement

Function decideEmail([string]$homeEmail,[string]$workEmail)
{
   #Check for email, home email wins
   if($homeEmail -ne '')
   {
       return $homeEmail
   }#end if
   if($workEmail -ne '')
   {
       return $workEmail
   }#end if
   else{return $null}
}#end function decideEmail

function get-dn ([string]$SAMName,[string]$type)
{
$root = [ADSI]''
$searcher = new-object System.DirectoryServices.DirectorySearcher($root)
    
   switch($type)
   {
       contact{
           $searcher.filter = "(&(objectClass=contact)(cn=$SAMName))"    
       }
       group{
           $searcher.filter = "(&(objectClass=group)(cn=$SAMName))"
       }
       user{
           $searcher.filter = "(&(objectClass=user)(sAMAccountName=$SAMName))"
       }
       computer{
           $searcher.filter = "(&(objectClass=computer)(name=$SAMName))"
       }
   }

$user = $searcher.findall() 
   
if ($user.count -gt 1){ 
	$count = 0
	foreach($i in $user){
		write-host $count ": " $i.path 
		$count = $count + 1
	}
	$selection = Read-Host "Please select item: "
	return $user[$selection].path.toString()
}
else{
	return $user[0].path
}
}

Function AssignSecurityGroups([string]$fullName,[string]$yearGroup,[string]$RegGroup)
{  
   $yearGroup = "Parents "+$yearGroup
   $RegGroup = "Parents "+$RegGroup
   
   $ParentsGroupDN = get-dn "Parents" group
   $YeargroupDN = get-dn $yearGroup group
   $regGroupDN = get-dn $regGroup group
   
   $contactDN = get-dn $fullName contact
   
   add-QADGroupMember -identity $ParentsGroupDN.substring(7) -member $contactDN.substring(7)
   Add-QADGroupMember -identity $YearGroupDN.substring(7) -member $contactDN.substring(7)
   Add-QADGroupMember -identity $regGroupDN.substring(7) -member $contactDN.substring(7)
}

Function runImport([string]$filePath)
{    
   #Import CSV and iterate though
   Import-CSV $filePath | foreach-object{
   
       #Create year and reg Group vars. Can only be done on first run
       if($_.Name -ne '')
       {
           $Script:reg = $_.reg
           $Script:year = $_.Year
       }                
       
       #Check that the contact has Parental Responsibility
       if($_.'Parental Responsibility' -eq 'T')
       {
           $email = decideEmail $_.'home email' $_.'work email';
           
           #If contact has an e-amil address create contact card
           if($email -ne $null)
           {   
               #check if already existing contact
               $check = get-dn $_.'full Name' contact
               if($check -eq $null)
               {          
                   New-MailContact -Name $_.'Full Name' -ExternalEmailAddress $email -DisplayName $_.'Full Name' -FirstName $_.forename -LastName $_.surname -OrganizationalUnit 'ou=Creation,dc=DOMAIN,dc=local' -PrimarySmtpAddress $email       
               }  
               else
               {
                   Set-MailContact -identity $_.'Full Name' -ExternalEmailAddress $email -DisplayName $_.'Full Name' -PrimarySmtpAddress $email
               }         
               
               AssignSecurityGroups $_.'full Name' $Year $Reg;
               
               if($check -eq $null){sleep 10}
               
               Set-MailContact -Identity $_.'Full Name' -HiddenFromAddressListsEnabled:$True
           }
       } #end if       
   } #end for
} #end function runImport

#Run Import
runImport 's:\temp\contacts.csv';

IGB · November 11, 2009

Thanks for the script Fawkers, the second one looks like it will do exactly what I need.

Can I just ask you about the Quest snapin, is that a paid for snapin or a freebie available on the net?

fawkers · November 11, 2009

no problem,

The Quest stuff is free and avaible from PowerShell Commands (CMDLETs) for Active Directory by Quest Software

Please do let us know if you add anything to the script, I'm always looking for ways to improve it.

IGB · November 11, 2009

Ok I've run the script but I'm getting errors and I'm not sure if I've done something wrong so I'm wandering if you can help.

You cannot call a method on a null-valued expression.

At G:\contact.ps1:66 char:59

+ add-QADGroupMember -identity $ParentsGroupDN.substring( <<<< 7) -member $contactDN.substring

You cannot call a method on a null-valued expression.

At G:\contact.ps1:67 char:56

+ Add-QADGroupMember -identity $YearGroupDN.substring( <<<< 7) -member $contactDN.substring(7)

You cannot call a method on a null-valued expression.

At G:\contact.ps1:68 char:55

+ Add-QADGroupMember -identity $regGroupDN.substring( <<<< 7) -member $contactDN.substring(7)

It has created the users as mail contacts and got them hidden from the address books, however it has not associated them with any distribution groups at all.

fawkers · November 11, 2009

Ok, what are the names of your distribution groups?

IGB · November 11, 2009

Ah right, so the groups need to be created before running the script then!

fawkers · November 11, 2009

yes they do, once you've created the groups you can just re-run the script.

IGB · November 11, 2009

Thanks Fawkers.......(IGB toddles off with tail between his legs to create these distribution groups

)

fawkers · November 12, 2009

Heres the script with a bit of modification to create the groups automaticly if they dont already exist.

Add-PSSnapin Microsoft.Exchange.Management.Powershell.Admin
Add-pssnapin Quest.ActiveRoles.ADManagement

Function decideEmail([string]$homeEmail,[string]$workEmail)
{
   #Check for email, home email wins
   if($homeEmail -ne '')
   {
       return $homeEmail
   }#end if
   if($workEmail -ne '')
   {
       return $workEmail
   }#end if
   else{return $null}
}#end function decideEmail

function get-dn ([string]$SAMName,[string]$type)
{
$root = [ADSI]''
$searcher = new-object System.DirectoryServices.DirectorySearcher($root)
    
   switch($type)
   {
       contact{
           $searcher.filter = "(&(objectClass=contact)(cn=$SAMName))"    
       }
       group{
           $searcher.filter = "(&(objectClass=group)(cn=$SAMName))"
       }
       user{
           $searcher.filter = "(&(objectClass=user)(sAMAccountName=$SAMName))"
       }
       computer{
           $searcher.filter = "(&(objectClass=computer)(name=$SAMName))"
       }
   }

$user = $searcher.findall() 
   
if ($user.count -gt 1){ 
	$count = 0
	foreach($i in $user){
		write-host $count ": " $i.path 
		$count = $count + 1
	}
	$selection = Read-Host "Please select item: "
	return $user[$selection].path.toString()
}
else{
	return $user[0].path
}
}

function CheckandCreateGroup([string]$GroupName)
{
   $check = get-dn $groupName group
   if($check -eq $null)
   {
       new-qadgroup -ParentContainer 'OU=Creation,DC=DOMAIN,DC=local' -name $GroupName -samAccountName $GroupName -grouptype 'Distribution' -groupscope 'Universal'
       sleep 10
       enable-DistributionGroup -Identity (get-dn $GroupName group).substring(7) -Alias ($groupname.replace(" ","")) -DisplayName $GroupName
   }
}#end fuction creategroups

Function AssignSecurityGroups([string]$fullName,[string]$yearGroup,[string]$RegGroup)
{  .
   CheckandCreateGroup("Parents")
   
   $yearGroup = "Parents "+$yearGroup
   CheckandCreateGroup($yearGroup)
   $RegGroup = "Parents "+$RegGroup
   CheckandCreateGroup($RegGroup)
   
   $ParentsGroupDN = get-dn "Parents" group
   $YeargroupDN = get-dn $yearGroup group
   $regGroupDN = get-dn $regGroup group
   
   $contactDN = get-dn $fullName contact
   
   add-QADGroupMember -identity $ParentsGroupDN.substring(7) -member $contactDN.substring(7)
   Add-QADGroupMember -identity $YearGroupDN.substring(7) -member $contactDN.substring(7)
   Add-QADGroupMember -identity $regGroupDN.substring(7) -member $contactDN.substring(7)
}

Function runImport([string]$filePath)
{    
   #Import CSV and iterate though
   Import-CSV $filePath | foreach-object{
   
       #Create year and reg Group vars. Can only be done on first run
       if($_.Name -ne '')
       {
           $Script:reg = $_.reg
           $Script:year = $_.Year
       }                
       
       #Check that the contact has Parental Responsibility
       if($_.'Parental Responsibility' -eq 'T')
       {
           $email = decideEmail $_.'home email' $_.'work email';
           
           #If contact has an e-amil address create contact card
           if($email -ne $null)
           {   
               #check if already existing contact
               $check = get-dn $_.'full Name' contact
               if($check -eq $null)
               {          
                   New-MailContact -Name $_.'Full Name' -ExternalEmailAddress $email -DisplayName $_.'Full Name' -FirstName $_.forename -LastName $_.surname -OrganizationalUnit 'ou=Creation,dc=DOMAIN,dc=local' -PrimarySmtpAddress $email       
               }  
               else
               {
                   Set-MailContact -identity $_.'Full Name' -ExternalEmailAddress $email -DisplayName $_.'Full Name' -PrimarySmtpAddress $email
               }         
               
               AssignSecurityGroups $_.'full Name' $Year $Reg;
               
               if($check -eq $null){sleep 10}
               
               Set-MailContact -Identity $_.'Full Name' -HiddenFromAddressListsEnabled:$True
           }
       } #end if       
   } #end for
} #end function runImport

#Run Import
runImport 's:\temp\contacts.csv';

Edited November 12, 2009 by fawkers
Error on line 62, code failed due to Spaces in strings, error corrected

IGB · November 12, 2009

I'd just like to publicly thank Fawkers for his assistance with this problem.

Thanks to him I can now create and maintain mail contacts for parents by 3 different groups

All Parents

Parents of Years

Parents of Tutuor Groups

I did run into a couple of problems that he put right almost immediately, so Thanks a lot for your help with this

marshharrier · December 12, 2009

Folks - I hope you don't mind the intrusion.

We use ParentMail not because technically we can't do it ourselves, but because they administer the data and facilitate the parents to update their own data!

The problem with electronic communication is keeping contact data accurate otherwise ... it doesn't work (obviously) :-(

I don't know a school that can honestly say they have the problem licked of getting parents to respond comprehensively and regularly to school requests for up to date information! ParentMail reminds parents each term and gets a better response rate because the parents know what it's for - specifically.

Oh yes ... plus they enable us to send TEXT messages too! It's also relatively speaking - cheap (or perhaps I should say good value)

Edited December 12, 2009 by marshharrier

IGB · December 12, 2009

All intrusions are welcome as it adds ideas and suggestions to the melting pot.

We've had a number of issues with ParentMail, the one that springs to mind was a mail to notify parents that the school would be closed due to snow earlier this year. The first email that was sent went without any body at almost midnight despite having submitted it much earlier, now whilst this may have been an Id10T error from our end the second email sent didn't go out until the following day after registration which was to late for some.

The lists we use are maintained from our SIMS database AFAIK and submitted to ParenMail to create the groups, so the data comes from us a couple of times a year, which is also the same data that I've used to create the distribution groups above.

fawkers · December 14, 2009

Thanks marshharrier for that input, I have to say the the instigater of this script was my bursar shouting "I want you to send it by e-mail.... now" dispite the fact he prints every e-mail he gets!

Anyway as you both said no system is going to be any good unless the data in you MIS (SIM's in my case) is any good. With 2010 deadline comming up regarding with online reporting were just about to start a big drive at my place to get as much data as up to date as we can so that there won't be any hickups.

As far as SMS Messages go i'm looking at some products which intergrate with exchange and I will post back as I find out more information.

marshharrier · December 14, 2009

Good luck fawkers with the data collection excercise

Trouble is .... you have to keep doing it! I think enabling parents to keep on top of it themselves (a la ParentMail) is the 'only' long term decent solution that isn't going to tie the admin team in knots every year.

IGB · December 14, 2009

We're also using SLG2 as well, so the parents can check their contact details that are held in SIMS live on there. If there are any changes they can contact the school themselves. We also do an annual data collection anyway which includes email addresses, so it doesn't really add to much to the data collection process.

fawkers · December 14, 2009

IGM - Doesn't SLG allow the parents to update there own details? We've just started look at SLG, what do you think of it over all?

fawkers · December 14, 2009

Quickly back on topic i've just updated the script to allow poeple to opt-out of receving email, just put a list of e-mails in a text file and edit line 59 for the correct path.

Add-PSSnapin Microsoft.Exchange.Management.Powershell.Admin
Add-pssnapin Quest.ActiveRoles.ADManagement

Function decideEmail([string]$homeEmail,[string]$workEmail)
{
   #Check for email, home email wins
   if($homeEmail -ne '')
   {
       return $homeEmail
   }#end if
   if($workEmail -ne '')
   {
       return $workEmail
   }#end if
   else{return $null}
}#end function decideEmail



function get-dn ([string]$SAMName,[string]$type)
{
$root = [ADSI]''
$searcher = new-object System.DirectoryServices.DirectorySearcher($root)
    
   switch($type)
   {
       contact{
           $searcher.filter = "(&(objectClass=contact)(cn=$SAMName))"    
       }
       group{
           $searcher.filter = "(&(objectClass=group)(cn=$SAMName))"
       }
       user{
           $searcher.filter = "(&(objectClass=user)(sAMAccountName=$SAMName))"
       }
       computer{
           $searcher.filter = "(&(objectClass=computer)(name=$SAMName))"
       }
   }

$user = $searcher.findall() 
   
if ($user.count -gt 1){ 
	$count = 0
	foreach($i in $user){
		write-host $count ": " $i.path 
		$count = $count + 1
	}
	$selection = Read-Host "Please select item: "
	return $user[$selection].path.toString()
}
else{
	return $user[0].path
}
}

function CheckIfOptedOut([string] $fullname, [string] $email)
{    
   $optoutlist = Get-Content \\shsg-d02-fnp01\staffuserdata$\ampickett\Documents\Powershell\exchange\OptOutList.txt
   
   if($optoutlist.Contains($email)){
       #check if already existing contact
       $check = get-dn $fullname contact
       if($check -eq $null)
       {          
           Disable-MailContact -Identity $fullname -Confirm:$false
       }#end if
       
       return $true
   }#end if

   return $false
}#end fuction CheckifOptedOut

function CheckandCreateGroup([string]$GroupName)
{
   $check = get-dn $groupName group
   if($check -eq $null)
   {
       new-qadgroup -ParentContainer 'OU=Creation,DC=olympus,DC=local' -name $GroupName -samAccountName $GroupName -grouptype 'Distribution' -groupscope 'Universal'
       sleep 10
       enable-DistributionGroup -Identity (get-dn $GroupName group).substring(7) -Alias ($groupname.replace(" ","")) -DisplayName $GroupName
   }
}#end fuction creategroups

Function AssignSecurityGroups([string]$fullName,[string]$yearGroup,[string]$RegGroup)
{  .
   CheckandCreateGroup("Parents")
   
   $yearGroup = "Parents "+$yearGroup
   CheckandCreateGroup($yearGroup)
   $RegGroup = "Parents "+$RegGroup
   CheckandCreateGroup($RegGroup)
   
   $ParentsGroupDN = get-dn "Parents" group
   $YeargroupDN = get-dn $yearGroup group
   $regGroupDN = get-dn $regGroup group
   
   $contactDN = get-dn $fullName contact
   
   add-QADGroupMember -identity $ParentsGroupDN.substring(7) -member $contactDN.substring(7)
   Add-QADGroupMember -identity $YearGroupDN.substring(7) -member $contactDN.substring(7)
   Add-QADGroupMember -identity $regGroupDN.substring(7) -member $contactDN.substring(7)
}

Function runImport([string]$filePath)
{    
   #Import CSV and iterate though
   Import-CSV $filePath | foreach-object{
   
       #Create year and reg Group vars. Can only be done on first run
       if($_.Name -ne '')
       {
           $Script:reg = $_.reg
           $Script:year = $_.Year
       }                
       
       #Check that the contact has Parental Responsibility
       if($_.'Parental Responsibility' -eq 'T')
       {
           $email = decideEmail $_.'home email' $_.'work email';
           
           #If contact has an e-mail address create contact card
           if($email -ne $null)
           {   
               #check if already existing contact
               $check = get-dn $_.'full Name' contact
               if($check -eq $null)
               {          
                   New-MailContact -Name $_.'Full Name' -ExternalEmailAddress $email -DisplayName $_.'Full Name' -FirstName $_.forename -LastName $_.surname -OrganizationalUnit 'ou=Creation,dc=olympus,dc=local' -PrimarySmtpAddress $email       
               }  
               else
               {
                   Set-MailContact -identity $_.'Full Name' -ExternalEmailAddress $email -DisplayName $_.'Full Name' -PrimarySmtpAddress $email
               }         
                                              
               AssignSecurityGroups $_.'full Name' $Year $Reg;
               
               if($check -eq $null){sleep 10}
               
               Set-MailContact -Identity $_.'Full Name' -HiddenFromAddressListsEnabled:$True
               
               #Check if the e-mail address has been opted out, disables the exchange contact but keeps the AD contact
               CheckIfOptedOut $_.'full Name' $email
           }
       } #end if       
   } #end for
} #end function runImport

#Run Import
runImport 's:\temp\contacts.csv';

IGB · March 1, 2010

IGM - Doesn't SLG allow the parents to update there own details? We've just started look at SLG, what do you think of it over all?

Appologies Fawkers, I hadn't seen this question of yours until now.

It doesn't allow parents to update their own details, which I'm glad of to be fair otherwise there would be another layer of support required.

As a product overall, I think it's not to bad and our County is adopting it at county level, however we'll still maintain our own as we'll have more control overall than those that will be tied into the county's solution. As far as I can see those that make the move into county level hosting will have to become part of a larger AD tree and will have limited access to the AD structure or they will have to maintain 2 different Active Directories, one that they have full control over for their own establishment and 1 that the County will give some control over to them. This results in duplicate credentials for all users in the school. SLA's are yet to have been sorted as far as I can see, however the upside to handing it over to county is that they can take care of the backups and security levels.

As a product itself it's pretty good and will meet the requirements for reporting before the deadline.

Exchange 2007 Mail Contacts

Recommended Posts

IGB

fawkers

fawkers

IGB

fawkers

IGB

fawkers

IGB

fawkers

IGB

fawkers

IGB

marshharrier

IGB

fawkers

marshharrier

IGB

fawkers

fawkers

IGB

Create an account or sign in to comment

Create an account

Sign in

Recent Threads

Sage 200 budget holders - test something for me?

Bromcom Support

My Fluke LinkRunner has a fault...

Home

Forums

What's New