Author Posts

February 12, 2018 at 12:13 pm

Dear people i have the follow script:

# this is the auto logon for the sender for the email (office 365)
$O365credentials = Get-Credential


[system.reflection.assembly]::loadwithpartialname('microsoft.visualbasic') | Out-Null
$Country = [microsoft.visualbasic.interaction]::inputbox('Fill in the Country name in the short OU version','Country')
$givendate = [microsoft.visualbasic.interaction]::inputbox('Fill in the begin date of the month','Given Date')
$Lastdate = [microsoft.visualbasic.interaction]::inputbox('Fill in the last date of the month','Last Date') 
$fileLocation = [microsoft.visualbasic.interaction]::inputbox('give up filelocation were to save the file','Save As','c:\temp\data.csv')
$Emailaddress = [microsoft.visualbasic.interaction]::inputbox('give up Email address','Emailaddress','...@campofriofg.com')
$Emailsubject = [microsoft.visualbasic.interaction]::inputbox('give up Email subject','EmailSubject','New Created Users of the Month ...')
$EmailMessage = [microsoft.visualbasic.interaction]::inputbox('give up a short message about the email','Email message','With this email you find the attachement of new created users of the month ...')


$BeginDate = get-date $givendate
$EndDate = get-date $Lastdate
$OurUsers = Get-ADUser -SearchBase "OU=$country,DC=corp,DC=local" -filter * -Properties *
$OurUsers | Where-Object {$_.whencreated -gt $BeginDate} | 
Select-Object @{Name="AD DisplayName";Expression={$_.CN}}, @{Name="Accountname";Expression={$_.SamAccountName}},@{Name="Creation Date";Expression={$_.WhenCreated}}, @{Name="internal or external";Expression={$_.Extensionattribute6}}, @{Name="E-mail(if General account field can be Empty)";Expression={$_.Mail}}, @{Name="Notesfield";Expression={$_.Info}}, AccountExpirationDate, PasswordNeverExpires, CannotChangePassword, PasswordNotRequired| Sort-Object whencreated -Descending | 
Export-Csv $fileLocation -Delimiter ";" 

< #
This block has its purpose to format the email that will be sended with the .csv file to the Recipients.
Note ! in the script there are multiple "[firstname lastname ]" delete the brackets [] and the text between it and replace it with the name of the recipient.
if it will be sended to a sinlge person delete @() and text between () and just in the follow "firstname lastname "
#>
$Sender = $emailaddress
$SMTP = "outlook.office365.com"
$Recipients = @("[firstname Lastname] ", "[firstname Lastname] ", "[firstname Lastname] ")
$Subject = $Emailsubject
$Body = $EmailMessage
$Attachment = new-object Net.Mail.Attachment($fileLocation)

Start-Sleep 2

Send-MailMessage -To $Recipients -From $Sender -Subject $Subject -Body $Body -Attachments $fileLocation -SmtpServer $SMTP -Credential $O365credentials -UseSsl -Port 587 -DeliveryNotificationOption never

Is it possible to build 2 things in the script

first in the excel sheet the rows and cels are matched to te text in it? like auto scale to see all information?

second is it possible of a row to say if in this row a cel is empty when other fields are filled the cell will be colored red?

February 12, 2018 at 1:17 pm

no, you really can't do any logic within a CSV file, a CSV file is raw text.

you can explore options using excel itself, and there are a few modules out there for interacting with Excel objects.