Avatar of Simon Allaway
Simon Allaway
Flag for United Kingdom of Great Britain and Northern Ireland asked on

powershell or VB scripts required to carry out over night administration on Exchange Online

Hi All

I have a really hard script that I need desperate help on. If anyone can help so that I can schedule to run over night, every night? PS my scripting knowledge is as good as a hamster.

I need help creating a powershell script and or VB scripts for Exchange online to automatically add contacts and add them to a mail enabled group (not public folder or tradition distribution list).

Step one
convert a xlsx file in to a csv files (this xlsx file is created every night probably with a different file name every night).  The cloumns in the cvs will be first name , last name, ExternalEmailAddress, OrganizationUnit.  I will probably have a batch file run to delete any other excel or csv files in this folder before the new xlsx file is created

step two
To be able to run a scheduled powershell to automatically connect to exchange online without any interaction

I got as far as this but it keeps prompting for username and password:

Set-ExecutionPolicy RemoteSigned -Scope Currentuser
$credential = get-credential
Import-Module MSOnline
Connect-MsolService -Credential $credential
$ExchangeSession = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri "https://outlook.office365.com/powershell-liveid/" -Credential $credential -Authentication "Basic" -AllowRedirection
Import-PSSession $ExchangeSession


step three
Delete all of my contacts in exchange on line

Step four
Using the converted CSV file automatically import contacts to a exchange group (first name , Last name and email address only).  
If the exchange group does not exist then create a group with the email address (groupname@example.com)

step five
All of the above will be a scheduled task from the on premises application server.

Background
My bespoke management system is constantly being updated on a daily basis with clients constantly being added and removed from distribution lists.  This is becoming a nightmare to administer so I thought that if I could get my management system to export a report and then use that report to carry out the admin at night time it would save me hours of work each week.

I know its a hard one but I'm sure one or more of you have the knowledge(fingers crossed)


Many thanks


Simon
PowershellVB ScriptMicrosoft 365Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Sam Jacobs

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Sam Jacobs

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Simon Allaway

ASKER
Hey Sam

Many thanks for helping at my hour of need.  Very kind of you.

Just to confirm would I be right in saying that once I have created the flder and txt on the root of c: the script should look like this;

Set-ExecutionPolicy RemoteSigned -Scope Currentuser
$sAMAccountName = "simon@x.com"
$securePW = Get-Content "C:\secure\creds.txt" | ConvertTo-SecureString
$credential = New-Object System.Management.Automation.PsCredential($sAMAccountName, $securePW)
Import-Module MSOnline
Connect-MsolService -Credential $credential
$ExchangeSession = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri "https://outlook.office365.com/powershell-liveid/" -Credential $credential -Authentication "Basic" -AllowRedirection
Import-PSSession $ExchangeSession



Regards

Simon
Sam Jacobs

Simon,

Drop the "@x.com" from $sAMAccountName.

Sam
Simon Allaway

ASKER
Hi Sam

even though thats the full username for office 365?

Regards


Simon
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Sam Jacobs

Come to think of it, you may need to try both. I've only used it in conjunction with Enter-PSSession to get to another server, not witb Exchange.
Simon Allaway

ASKER
Hi Sam
After the first line executes

the script returns a question:

Execution Policy Change
The execution policy helps protect you from scripts that you do not trust.
Changing the execution policy might expose you to the security risks described
in the about_Execution_Policies help topic at
http://go.microsoft.com/fwlink/?LinkID=135170. Do you want to change the
execution policy?
[Y] Yes  [N] No  [S] Suspend  [?] Help (default is "Y"):

Open in new window


is there a way of answering yes in the script so it doesn't stop it running?

Regards


Simon
Sam Jacobs

If you create a ps1. file, you can use the following syntax:
Powershell.exe -ExecutionPolicy ByPass -File C:\pathtoScript\script.ps1

Open in new window

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.