Exchange 2007 Mail Contacts

[IGB]

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]

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, <contact> 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]

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]

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]

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]

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]

Ok, what are the names of your distribution groups?

[IGB]

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

[fawkers]

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

[IGB]

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

[fawkers]

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';

[IGB]

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]

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)

[IGB]

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]

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.