Exclude elements are already in file - Foreach loop powershell Please help me to correct the below code . I am trying to exclude the filename which already received email but -and $i.file -ne $SentUsers not excluding the already sent filenames . Please help me .

I am trying below things :


Write filename to a file
Get filename to a file
Exclude filename which already recieved an email by using -and $i.file -ne $SentUsers
Copy new current file name to file by overwriting existing file


Below is my code full :

$Q = 

"
*** SQLCode Here ***

"
$ab    = Invoke-Sqlcmd -ServerInstance Serv1 -Database DB1 -Query $Q 

## Write filename to a file

$l = $ab | select file | Out-File C:\temp\fname.txt

## Get filename to a file

$SentUsers = Get-Content -Path C:\temp\fname.txt

 foreach($i in $cr  ){
 ## Exclude filename which already recieved an email by using -and $i.file -ne $SentUsers

                    If ( $i.Uid -eq 'No' -and $i.UIntheTeam -eq 'No' -and $i.file -ne $SentUsers ){ 
                        $user = $i.User

Send-MailMessage -To @("abc") -Cc $user -From 'abc1' -SMTPServer abc.co.jp  -Subject "TestSub " -Body "

** Mail Message Body Here **

" } 
                    Else {
                        'Exit'
                    }


                }

# Copy new current file name to file by overwriting existing file

$ab | select file | out-file -FilePath C:\temp\fname.txt
As presented here, your code has many problems.

First off, avoid non-descriptive variable names.  Your code is very difficult to read. Until you read the entire script, you have no idea what any variable is supposed to represent.  Every time you find a new variable, you have to hunt down where it comes from and then keep it all in your head.  It's difficult to follow the logic of your script because there's no contextual information.  If you were trying to remove sensitive information so you could post it on the web, you've removed too much.

Next:

$l = $ab | Select-Object file | Out-File C:\temp\fname.txt


This line makes no sense.  Out-File has no standard output to assign to $l, so $l is always going to be assigned the value of $null.  You also never appear to use $l again.  I don't know what you intend this line to do.

However, if I assume you only want this line to write the file column to a file and that you don't need $l at all, then as written this is going to output a header line.  Your file contents will be:

File
----
MyFile1
MyFile2


You probably don't want that because when you read it back the system will assume File and ---- are files.  The pattern you want is more like this:

$ab | Select-Object -ExpandProperty File | Set-Content C:\temp\fname.txt

$SentUsers = Get-Content -Path C:\temp\fname.txt


I tend to avoid pairing Out-File with Get-Content because they have different encoding defaults, while Get-Content and Set-Content are the same.

Next:

foreach ($i in $cr) {


The variable $cr has no value assigned to it.  If you're not assigning it elsewhere, this entire loop will never run.

Next:

If ( $i.Uid -eq 'No' -and $i.UIntheTeam -eq 'No' -and $i.file -ne $SentUsers ) { 


As @ScottHodgin mentions, $SentUsers is likely going to be an array of values.  -ne treats both operands as a scalar.  You probably want one of the containment operators:

$Value -in $Array
$Value -notin $Array
$Array -contains $Value
$Array -notcontains $Value


So, I'd expect this to do what you want:

If ( $i.Uid -eq 'No' -and $i.UIntheTeam -eq 'No' -and $i.file -notin $SentUsers ) { 


See Get-Help about_Comparison_Operators for more information on the above operators.

Next:

Else {
    'Exit'
}


Huh?  "Else, output the string 'Exit'."  What are you intending this to do?  Is it supposed to stop the script?

Next:

# Copy new current file name to file by overwriting existing file

$ab | select file | out-file -FilePath C:\temp\fname.txt  


Why do you need to do this?  You did the same thing earlier in the script, and $ab hasn't modified since then.  I mean, the file path is the same and everything.  Is that an error?  Did you decide to use the same file path throughout your posted code without leaving in the fact that you're actually changing different files?