Check excel cell for username against AD and retrieve e-mail

I have an excel report that is generated daily with usernames in column B, below the header line. I need something that would check the first 3 letters of cells b2, b3, b4, etc and query AD to find the username. Once the username is found it would look for the e-mail address associated with that user and e-mail them this excel report.

I am able to send the e-mails fine but am having trouble looking at specific cells and only searching based on the first 3 letters found in those cells. I realize that only using 3 letters for a search might possibly result in more than i want, but i can filter those out in the get-aduser filter. I’m a bit reluctant to post any sample code i have as it clearly has not worked at all

I do not have any sample code to provide for you, but have you had a look at one of the available modules to work with Excel files? One such module is the “ImportExcel” module maintained by Doug Finke:

If the excell data is simple text I always save it as a csv file much easier to work with. If you have no control over the input file then you will need a module like stated before once you import the date into an custom object the method you want to use to combine the first 3 letters of the first 3 cells would be substrings

$userid = ( $exl.cell1.substring(0,2) + $exl.cell2.substring(0,2) + $exl.cell3.substring(0,2))

Does that make sense?

I’m trying to follow but you’ll need to bear with me. I literally started learning PS this week and just have what I’ve learned from the MVA. The module for ImportExcel would certainly solve a future plan i have to color code other cells that have reached a date criteria. I’ve put the folder into the module path: C:\Users\USERNAME\Documents\WindowsPowerShell\Modules and i’ll start poking around the help files to see if i can make heads or tails of this.

I ended up getting this to work, but i’m sure you guys can point out way to make this more efficient or shorter. Again, i’m just starting out so I’m just glad it works.

I had to delete the top 5 lines of the report, because it always contained junk rows. I also added the start-sleep’s just to make things a bit easier to read when it’s echo’ing the data on the console. The function i got from somewhere on the web, i cannot remember where to give the credit due for that one. The function basically takes the DNS name and cuts off anything after the first 3 characters. I then added a wildcard (*) to the end of every array value and searched AD with that to get e-mail addresses. The script is followed up with a send-mailmessage cmdlet that attaches the report and sends it to the users it found. Really messy, but hey it works

$filepath = "\\Servershare\public\Desktop_Software_$((Get-Date).ToString('MM-dd-yyyy')).csv"

$modreport = import-csv -Path $filepath
$modreport | Select-Object -Skip 5 | Export-Csv -Path $filepath -NoTypeInformation
$modreport = import-csv -Path $filepath

Write-Host " Parsing the following report..."
$modreport | ft -AutoSize 

Start-Sleep 3

$DNS = $modreport | select -ExpandProperty DNS

#declare new function
function Get-Matches {
 begin {
    try {
   $regex = New-Object Regex($pattern) 
  catch {
   Throw "Get-Matches: Pattern not correct. '$Pattern' is no valid regular expression."
  $groups = @($regex.GetGroupNames() | 
  Where-Object { ($_ -as [Int32]) -eq $null } |
  ForEach-Object { $_.toString() })

process { 
  foreach ($line in $InputObject) {
   foreach ($match in ($regex.Matches($line))) {
    if ($groups.Count -eq 0) {
    } else {
     $rv = 1 | Select-Object -Property $groups
     $groups | ForEach-Object {
      $rv.$_ = $match.Groups[$_].Value

$myregex = '^\S{3}'

$shortname = $DNS | Get-Matches $myregex


$search = $shortname | foreach {$_ + "*"}

$getemail = ForEach($dns in $search){get-aduser -ldapfilter "(anr=$dns)" -properties mail | select-object -property mail -unique | where mail -ne $null}

Write-Host " E-mailing the following users this report..."

Start-Sleep 3