We help IT Professionals succeed at work.

Powershell script that when run checks the xls file and creates the users with mailbox.

bsharath
bsharath used Ask the Experts™
on
Hi,

Powershell script that when run checks the xls file and creates the users with mailbox.

Attached the xls file i have
Need membership adding by default and a report of what was created and if possible an email to a id with the success and failures.

Regards
Sharath

Data.xls
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Meir RivkinFull stack Software Engineer

Commented:
is it for existing users?

Author

Commented:
New users creation
Meir RivkinFull stack Software Engineer

Commented:
check this
function Release-Ref ($ref) {
([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
[System.__ComObject]$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
}
# -----------------------------------------------------
cls

$objExcel = new-object -comobject excel.application 
$objExcel.Visible = $True 
$objWorkbook = $objExcel.Workbooks.Open("C:\temp\users.xls")
$objWorksheet = $objWorkbook.Worksheets.Item(1)

$intRow = 2

Do {
    $fname = $objWorksheet.Cells.Item($intRow,1).Value()
	$lname = $objWorksheet.Cells.Item($intRow,2).Value()
	$fullname = $objWorksheet.Cells.Item($intRow,3).Value()
	$email = $objWorksheet.Cells.Item($intRow,5).Value()
	$db = $objWorksheet.Cells.Item($intRow,20).Value()

	New-mailbox -UserPrincipalName $email -database $db -OrganizationalUnit Users -FirstName $fname -LastName $lname -DisplayName $fullname

    $intRow++
}
While ($objWorksheet.Cells.Item($intRow,1).Value() -ne $null)

$objExcel.Quit()

$a = Release-Ref($objWorksheet)
$a = Release-Ref($objWorkbook)
$a = Release-Ref($objExcel)

Open in new window

Author

Commented:
Thanks
The attached excel will that be fine for this code?
Meir RivkinFull stack Software Engineer

Commented:
yes, i've tested it on your excel

Author

Commented:
I get this

Exception of type 'System.OutOfMemoryException' was thrown.
Meir RivkinFull stack Software Engineer

Commented:
which line?

Author

Commented:
I get this
In this line
>> While ($objWorksheet.Cells.Item($intRow,1).Value() -ne $null)

>>
The term 'New-mailbox' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the
 spelling of the name, or if a path was included, verify that the path is correct and try again.
At line:7 char:16
+     New-mailbox <<<<  -UserPrincipalName $email -database $db -OrganizationalUnit Users -FirstName $fname -LastName $
lname -DisplayName $fullname
    + CategoryInfo          : ObjectNotFound: (New-mailbox:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException
Meir RivkinFull stack Software Engineer

Commented:
run the following before the script:
Add-PSSnapin Microsoft.Exchange.Management.PowerShell.Admin

Author

Commented:
Now i get this
cmdlet New-Mailbox at command pipeline position 1
Supply values for the following parameters:
Password: ****************
Name:
New-Mailbox : Cannot bind argument to parameter 'Name' because it is an empty string.
At line:7 char:16
+     New-mailbox <<<<  -UserPrincipalName $email -database $db -OrganizationalUnit Users -FirstName $fname -LastName $
lname -DisplayName $fullname
    + CategoryInfo          : InvalidData: (:) [New-Mailbox], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorEmptyStringNotAllowed,Microsoft.Exchange.Management.Reci
   pientTasks.NewMailbox
Meir RivkinFull stack Software Engineer

Commented:
replace line 24 with this one:

$password = Read-Host "<USER_PASSWORD>" -AsSecureString
New-mailbox -UserPrincipalName $email -database $db -OrganizationalUnit Users -FirstName $fname -LastName $lname -DisplayName $fullname -Password $password

replace <USER_PASSWORD> with the user password.

Author

Commented:
I still get the same message
Meir RivkinFull stack Software Engineer

Commented:
the Name parameter was missing, use the following:

New-mailbox -UserPrincipalName $email -database $db -OrganizationalUnit Users -FirstName $fname -LastName $lname -DisplayName $fullname -Password $password -Name $fullname

Author

Commented:
Where can i change the OU path
its erroring in OU path as many users Ou is presentI will have OU's with Spaces in there name
Chris DentPowerShell Developer
Top Expert 2010

Commented:

Modify this parameter:

-OrganizationalUnit Users

To:

-OrganizationalUnit "domain.com/somewhere/whatever"

In context that's:

New-mailbox -UserPrincipalName $email -database $db `
  -OrganizationalUnit "domain.com/somewhere/whatever" `
  -FirstName $fname -LastName $lname -DisplayName $fullname -Password $password

Chris

Author

Commented:
I get this

Name:
New-Mailbox : Cannot bind argument to parameter 'Name' because it is an empty string.
At line:8 char:12
+ New-mailbox <<<<  -UserPrincipalName $email -database $db `
    + CategoryInfo          : InvalidData: (:) [New-Mailbox], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorEmptyStringNotAllowed,Microsoft.Exchange.Management.R
   pientTasks.NewMailbox
Chris DentPowerShell Developer
Top Expert 2010

Commented:

$lname is supposed to read from the second column (B) in the spreadsheet. Does that always have a value?

Chris

Author

Commented:
Hi Chris
Yes Colum B2 has a name
Chris DentPowerShell Developer
Top Expert 2010

Commented:

Need to see if $lname has a value then.

Each of the values below is quoted, that way we can see if you have any that are just spaces (will show as ' ').

Chris
function Release-Ref ($ref) {
([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
[System.__ComObject]$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
}
# -----------------------------------------------------
cls

$objExcel = new-object -comobject excel.application 
$objExcel.Visible = $True 
$objWorkbook = $objExcel.Workbooks.Open("C:\temp\users.xls")
$objWorksheet = $objWorkbook.Worksheets.Item(1)

$intRow = 2

Do {
    $fname = $objWorksheet.Cells.Item($intRow,1).Value()
    $lname = $objWorksheet.Cells.Item($intRow,2).Value()
    $fullname = $objWorksheet.Cells.Item($intRow,3).Value()
    $email = $objWorksheet.Cells.Item($intRow,5).Value()
    $db = $objWorksheet.Cells.Item($intRow,20).Value()

    Write-Host "Creating User with:"
    Write-Host "fname: '$fname'"
    Write-Host "lname: '$lname'"
    Write-Host "fullname: '$fullname'"
    Write-Host "email: '$email'"
    Write-Host "db: '$db'"

    New-mailbox -UserPrincipalName $email -database $db `
      -OrganizationalUnit "domain.com/somewhere/whatever" `
      -FirstName $fname -LastName $lname -DisplayName $fullname `
      -Password $password

    $intRow++
}
While ($objWorksheet.Cells.Item($intRow,1).Value() -ne $null)

$objExcel.Quit()

$a = Release-Ref($objWorksheet)
$a = Release-Ref($objWorkbook)
$a = Release-Ref($objExcel)

Open in new window

Author

Commented:
Chris
it works it did create a user
But display name is as this

$objExcel.Quit()
None of these fields were filled

Groups      Description      Office      Mobile      Title      Department      Company      Address      City      State      Zipcode      Country      Home Phone        Phone      Manager name
Meir RivkinFull stack Software Engineer

Commented:
to do that u gonna have to use the following:

set-qaduser -identity $alias -city $City -company $Company -department $Department -fax $Fax -office $Office -phonenumber $Phone -postalcode $PostalZip -stateorprovince $StateProv -streetaddress $Address -PostOfficeBox $Address -webpage $web -displayname $displayname -title $JobTitle -description $Description

i'll modify the script for you
Meir RivkinFull stack Software Engineer

Commented:
i found no matched properties to Account Status and GroupStatus, other than that everything will be added to user:

function Release-Ref ($ref) {
([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
[System.__ComObject]$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
}
# -----------------------------------------------------
cls

$objExcel = new-object -comobject excel.application 
$objExcel.Visible = $True 
$objWorkbook = $objExcel.Workbooks.Open("C:\temp\users.xls")
$objWorksheet = $objWorkbook.Worksheets.Item(1)

$intRow = 2

Do {
    $fname = $objWorksheet.Cells.Item($intRow,1).Value()
    $lname = $objWorksheet.Cells.Item($intRow,2).Value()
    $fullname = $objWorksheet.Cells.Item($intRow,3).Value()
    $email = $objWorksheet.Cells.Item($intRow,5).Value()
    $db = $objWorksheet.Cells.Item($intRow,20).Value()
	$alias = $fullname
	$City = $objWorksheet.Cells.Item($intRow,14).Value()
	$Company = $objWorksheet.Cells.Item($intRow,12).Value()
	$Department = $objWorksheet.Cells.Item($intRow,11).Value()
	$Office = $objWorksheet.Cells.Item($intRow,8).Value()
	$Description = $objWorksheet.Cells.Item($intRow,7).Value()
	$PostalZip = $objWorksheet.Cells.Item($intRow,16).Value()
	$Phone = $objWorksheet.Cells.Item($intRow,19).Value()
	$JobTitle = $objWorksheet.Cells.Item($intRow,10).Value()
	$Address = $objWorksheet.Cells.Item($intRow,13).Value()
	$StateProv = $objWorksheet.Cells.Item($intRow,15).Value()
	$Manager  =$objWorksheet.Cells.Item($intRow,21).Value()
	$Mobile =$objWorksheet.Cells.Item($intRow,9).Value()
	$HomePhone = $objWorksheet.Cells.Item($intRow,18).Value()
	$ObjectAttributes = $objWorksheet.Cells.Item($intRow,23).Value()
	
    Write-Host "Creating User with:"
    Write-Host "fname: '$fname'"
    Write-Host "lname: '$lname'"
    Write-Host "alias: '$alias'"
    Write-Host "city: '$City'"
    Write-Host "company: '$Company'"
	Write-Host "department: '$Department'"
    Write-Host "office: '$Office'"
    Write-Host "description: '$Description'"
    Write-Host "zipcode: '$PostalZip'"
    Write-Host "phone: '$Phone'"
    Write-Host "jobTitle: '$JobTitle'"
    Write-Host "address: '$Address'"
    Write-Host "state: '$StateProv'"
    Write-Host "manager: '$Manager'"
    Write-Host "mobile: '$Mobile'"
    Write-Host "homePhone: '$HomePhone'"
    Write-Host "objectAttributes: '$ObjectAttributes'"

    New-mailbox -Alias $alias -UserPrincipalName $email -database $db `
      -OrganizationalUnit "domain.com/somewhere/whatever" `
      -FirstName $fname -LastName $lname -DisplayName $fullname `
      -Password $password
	  
	  set-qaduser -identity $alias -city $City -company $Company -ObjectAttributes $ObjectAttributes -department $Department -Manager $Manager -MobilePhone $Mobile -office $Office -HomePhone $HomePhone -phonenumber $Phone -stateorprovince $StateProv  -postalcode $PostalZip -streetaddress $Address -title $JobTitle -description $Description

    $intRow++
}
While ($objWorksheet.Cells.Item($intRow,1).Value() -ne $null)

$objExcel.Quit()

$a = Release-Ref($objWorksheet)
$a = Release-Ref($objWorkbook)
$a = Release-Ref($objExcel)

Open in new window

Author

Commented:
I get this

New-Mailbox : Cannot bind parameter 'Alias' to the target. Exception setting "Alias": ""Testing script" is not valid
r Alias. Valid values are: Strings formed with characters from a to z (uppercase or lowercase), digits from 0 to 9, !
#, $, %, &, ', *, +, -, /, =, ?, ^, _, `, {, |, } or ~. One or more periods may be embedded in an alias, but each one
f them should be preceded and followed by at least one of the other characters. Unicode characters from U+00A1 to U+0
F are also valid in an alias, but they will be mapped to a best-fit US-ASCII string in the email address which is gen
ated from such an alias."
At line:40 char:23
+     New-mailbox -Alias <<<<  $alias -UserPrincipalName $email -database $db `
    + CategoryInfo          : WriteError: (:) [New-Mailbox], ParameterBindingException
    + FullyQualifiedErrorId : ParameterBindingFailed,Microsoft.Exchange.Management.RecipientTasks.NewMailbox

Set-QADUser : Object reference not set to an instance of an object.
At line:44 char:18
+       set-qaduser <<<<  -identity $alias -city $City -company $Company -ObjectAttributes $ObjectAttributes -departm
t $Department -Manager $Manager -MobilePhone $Mobile -office $Office -HomePhone $HomePhone -phonenumber $Phone -state
province $StateProv  -postalcode $PostalZip -streetaddress $Address -title $JobTitle -description $Description
    + CategoryInfo          : NotSpecified: (:) [Set-QADUser], NullReferenceException
    + FullyQualifiedErrorId : System.NullReferenceException,Quest.ActiveRoles.ArsPowerShellSnapIn.Powershell.Cmdlets.
   etUserCmdlet
Meir RivkinFull stack Software Engineer

Commented:
change line 23 from
$alias = $fullname

to

$alias = $fname

Author

Commented:
I get this

cmdlet New-Mailbox at command pipeline position 1
Supply values for the following parameters:
Name: $objExcel.Quit()

and this

Set-QADUser : Cannot resolve directory object for the given identity: 'Testing'.
At line:44 char:18
+       set-qaduser <<<<  -identity $alias -city $City -company $Company -ObjectAttributes $ObjectAttributes -departmen
t $Department -Manager $Manager -MobilePhone $Mobile -office $Office -HomePhone $HomePhone -phonenumber $Phone -stateor
province $StateProv  -postalcode $PostalZip -streetaddress $Address -title $JobTitle -description $Description
    + CategoryInfo          : NotSpecified: (:) [Set-QADUser], ObjectNotFoundException
    + FullyQualifiedErrorId : Quest.ActiveRoles.ArsPowerShellSnapIn.DirectoryAccess.ObjectNotFoundException,Quest.Acti
   veRoles.ArsPowerShellSnapIn.Powershell.Cmdlets.SetUserCmdlet
Chris DentPowerShell Developer
Top Expert 2010

Commented:

New-Mailbox "should" return the created object. And that should have a DistinguishedName property. That would make an ideal Identity value for Set-QADUser.

Chris
Meir RivkinFull stack Software Engineer

Commented:
@Chris-Dent
is that correct?

$UserObject = New-mailbox -Alias $alias -UserPrincipalName $email -database $db `
      -OrganizationalUnit "domain.com/somewhere/whatever" `
      -FirstName $fname -LastName $lname -DisplayName $fullname `
      -Password $password
        
        set-qaduser -identity $UserObject._DistinguishedName  -city $City -company $Company -ObjectAttributes $ObjectAttributes -department $Department -Manager $Manager -MobilePhone $Mobile -office $Office -HomePhone $HomePhone -phonenumber $Phone -stateorprovince $StateProv  -postalcode $PostalZip -streetaddress $Address -title $JobTitle -description $Description

Author

Commented:
Hi Chris any comments on the above
Chris DentPowerShell Developer
Top Expert 2010

Commented:
Drop the underscore from $UserObject._DistinguishedName, it's a typo, but that's what I had in mind, yes :)

Are all of the fields in the spreadsheet populated? Otherwise you'll find yourself checking for lots of nulls. Still, you could set everything using ObjectAttributes, sort of like this:

$ObjectAttributes = @{}
If ($City -ne $Null) { $ObjectAttributes.Add("l", $City) }
etc

Chris

Author

Commented:
Can you help e with the full code with the last additions please...

Author

Commented:
Any views...
Meir RivkinFull stack Software Engineer

Commented:
change this:

    set-qaduser -identity $UserObject._DistinguishedName  -city $City -company $Company -ObjectAttributes $ObjectAttributes -department $Department -Manager $Manager -MobilePhone $Mobile -office $Office -HomePhone $HomePhone -phonenumber $Phone -stateorprovince $StateProv  -postalcode $PostalZip -streetaddress $Address -title $JobTitle -description $Description

to this:

    set-qaduser -identity $UserObject.DistinguishedName  -city $City -company $Company -ObjectAttributes $ObjectAttributes -department $Department -Manager $Manager -MobilePhone $Mobile -office $Office -HomePhone $HomePhone -phonenumber $Phone -stateorprovince $StateProv  -postalcode $PostalZip -streetaddress $Address -title $JobTitle -description $Description

Author

Commented:
I get this

The term 'New-mailbox' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the
 spelling of the name, or if a path was included, verify that the path is correct and try again.
At line:40 char:16
+     New-mailbox <<<<  -Alias $alias -UserPrincipalName $email -database $db `
    + CategoryInfo          : ObjectNotFound: (New-mailbox:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

Set-QADUser : Cannot validate argument on parameter 'Identity'. The argument is null or empty. Supply an argument that
is not null or empty and then try the command again.
At line:44 char:22
+ set-qaduser -identity <<<<  $UserObject.DistinguishedName  -city $City -company $Company -ObjectAttributes $ObjectAtt
ributes -department $Department -Manager $Manager -MobilePhone $Mobile -office $Office -HomePhone $HomePhone -phonenumb
er $Phone -stateorprovince $StateProv  -postalcode $PostalZip -streetaddress $Address -title $JobTitle -description $De
scription
    + CategoryInfo          : InvalidData: (:) [Set-QADUser], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationError,Quest.ActiveRoles.ArsPowerShellSnapIn.Powershell.Cmdlet
   s.SetUserCmdlet

Author

Commented:
Any help with this...
Meir RivkinFull stack Software Engineer

Commented:
try run the following first:
Add-PSSnapin Microsoft.Exchange.Management.PowerShell.Admin

Author

Commented:
Can i have the full code please a little confused with the additions
Full stack Software Engineer
Commented:
New-mailbox cmdlet is not recognized cause you need to load the appropriate powershell module prior running the script.

so make sure you installed Microsoft Exchange Server 2007 Management Tools:
http://www.microsoft.com/downloads/details.aspx?FamilyID=6be38633-7248-4532-929b-76e9c677e802&displaylang=en

then add the following line at the top of your script:
Add-PSSnapin Microsoft.Exchange.Management.PowerShell.Admin