Link to home
Start Free TrialLog in
Avatar of rakkad
rakkadFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Powershell script that will match the strings in two files and send the file as attachment to the reciepient

Hi

I need to extend my powershell script that will do the following please:-

firstly, A file will be generated as recipients.csv

the recipients.csv file contains a file that produces a list as shown below, with column names (NetworkName, NetworkGroup and Email  

NetworkName                               NetworkGroup                             Email                                  
-----------                                            ------------                                           -----                                  
Home Of Choice                            First Complete                             first.complete@gmail.com
IN Partnership                               IN Partnership/Whitechurch     whitechurch@gmail.com
Intrinsic Financial                          Intrinsic                                         intrinsic@gmail.com

Secondly, a list of files will be generated in a directory c:\cogtest , which will create files e.g

First Complete.pdf
Intrinsic.pdf
John Charcol.pdf
Openwork Limited.pdf

Please bear in mind new files will be generated on a daily basis, which will have new names, so I don't want any hard-coded string searches to be put in place.

What I would like the powershell script to do will be as follows:-

a) Search for the string names in c:\cogtest with the column name defined in receipients.csv and if there is a match send this file with the email address as defined in receipients.csv
b) list of files sent and delivered successfully or failed

Hope this makes sense

Thank-you :-)
Avatar of David Johnson, CD
David Johnson, CD
Flag of Canada image

does network group column match the filename? Will it be just 1 file or can it be many files?
I think this is what you are getting at..
function Send-LocatedFile
{
    [cmdletbinding()]
    param(
        [parameter(ValueFromPipelineByPropertyName = $true)]
        [ValidateScript({Test-path -Path $_})]
        [string]$FilePath = '.\recipients.csv', 

        [Parameter(ValueFromPipelineByPropertyName = $true)]
        [ValidateScript({Test-Path -path $_ })]
        [string]$SearchDirectory = 'c:\cogtest',

        [parameter(ValueFromPipelineByPropertyName = $true)]
        [string]$Delimiter = ',', 

        [parameter(ValueFromPipelineByPropertyName = $true)]
        [string]$SMTPFrom = 'pdfsearcher@domain.tld',

        [parameter(ValueFromPipelineByPropertyName = $true)]
        [string]$SMTPServer = 'smtp.domain.tld',

        [parameter(ValueFromPipelineByPropertyName = $true)]
        [string]$SummarySMTPTo = 'administrator@domain.tld'
    )

    $CSV = Import-CSV -Path $FilePath -Delimiter $Delimiter
    
    $LookupTable = New-Object -TypeName hashtable
    foreach ($Record in $CSV)
    {
        $LookupTable.Add($Record.NetworkName, $Record.Email)
    }

    $SuccessFiles = New-Object -TypeName System.Collections.ArrayList
    $FailedFiles = New-Object -TypeName System.Collections.ArrayList 

    foreach ($Pattern in $CSV.NetworkName)
    {
        $SMTPTo = $LookupTable.Item($Pattern)
        $files = Get-ChildItem -Path $SearchDirectory | Where-Object { $_.Name -like "*$Pattern*"}

        if ($files)
        {
            $MessageBody = "Checked $SearchDirectory and located these files:`r`n"
            foreach ($file in $files)
            {
                $SuccessFiles.Add($file.FullName)
                $MessageBody += "$($file.name)`r`n"
            }
            Send-MailMessage -To $SMTPTo -From $SMTPFrom -Subject 'Located file' -SmtpServer $SMTPServer -Body $MessageBody -Attachments $files 
        }
        else 
        {
            $FailedFiles.Add($Pattern)
        }
    }

    $SummaryBody = "List of located files and missing files`r`n"
    $SummaryBody += "Succeeded files:`r`n"
    $SummaryBody += $SuccessFiles -join "`r`n"
    $SummaryBody += "`r`n`r`n"
    $SummaryBody += "Missing Files:`r`n"
if ($FailedFiles)
    {
        $SummaryBody += $FailedFiles -join "`r`n"
    }
    else 
    {
        $SummaryBody += 'No missing files'    
    }
    Send-MailMessage -To $SummarySMTPTo -From $SMTPFrom -SmtpServer $SMTPServer -Subject 'Send-LocatedFile summary' -Body $SummaryBody
}

Open in new window


This creates the function, so you just need to dot source it to run it..  (. .\Send-LocatedFiles.ps1)
The parameters are pretty straightforward, and most of it is set with defaults
-FilePath = '.\recipients.csv'    this is the output file that is generated as the listed input.. ideally, the correct path should be fixed and not just the current directory
-SearchDirectory = 'c:\cogtest' - this is the directory to search through
-Delimiter = ',' - the delimiter for the CSV file in $filepath in case it ever gets changed
-SMTPFrom = 'pdfsearcher@domain.tld' - this is the FROM address for the individual emails
-SMTPServer = 'smtp.domain.tld' - this is the mail server
-SummarySMTPTo = 'administrator@domain.tld' - this is the address where the summary email should be sent..

Basic steps:
1. Import the CSV file
2. Create a dictionary of the NetworkNames, and the corresponding email addresses.. the Network Group doesn't seem to be used, so we just ignore it
3. Create  empty lists to hold the names of the files sent, and another one for the names of the missing files (I think that's what you were looking for)
4. start a loop of all the NetworkNames from the CSV file, and for each one:
4a. Get the email address as the TO address
4b. Get a list of all the files in the search directory that look like the NetworkName with any prefix or any suffix
4c. IF the list of files is actually created (some files were found)
4c1. Create a success message body
4c2. add the full path to the collection of successful files and to the message body
4c3. Send the email to the TO address from 4a
4d. if there are no files, then add the NetworkName to the FailedFiles list
5. Create a new Summary email, and include the SucceededFiles
5a. If there are any failed/missing NetworkNames, they are added to the Summary email
6. Send the summary email to the 'administrator' or whoever..

This hasn't been tested, but it should work and should only need a small amount of tweaking, or just overrides with the parameters..

Coralon
Avatar of rakkad

ASKER

I made the necessary changes to reflect our environment, the search needs to be matched with NetworkGroup and not NetworkName.  I ran the script but nothing seems to happen.  Can you test the script out please.  I have enclosed the recpients.csv, as you can see, the email section contains multiple e-mails addresses, can you accommodate the change please to allow the script to work.

Thank-you
There's no recipients.csv attached...
Avatar of rakkad

ASKER

Oh!

Please see the enclosed recipients.csv file
recipients.csv
Ok.. the first major issue.. the file didn't actually match your description, but with the sample, that's a huge help.  

So.. I did a rewrite.. another one.. in theory this should work.. but rather than having you dot source it, you should be able to just run it directly.
Save this as a ps1 file, and then run it with the parameters..
[cmdletbinding()]
param(
    [parameter(ValueFromPipelineByPropertyName = $true)]
    [ValidateScript({Test-path -Path $_})]
    [string]$FilePath = '.\recipients.csv', 
	
	[Parameter(ValueFromPipelineByPropertyName = $true)]
    [ValidateScript({Test-Path -path $_ })]
    [string]$SearchDirectory = 'c:\cogtest',
	
	[parameter(ValueFromPipelineByPropertyName = $true)]
    [string]$SMTPFrom = 'pdfsearcher@domain.tld',

    [parameter(ValueFromPipelineByPropertyName = $true)]
    [string]$SMTPServer = 'smtp.domain.tld',

    [parameter(ValueFromPipelineByPropertyName = $true)]
    [string]$SMTPTo = 'administrator@domain.tld'
)

$FoundFiles = New-Object -TypeName system.Collections.ArrayList
$MissingEmails = New-Object -TypeName system.Collections.ArrayList
$NoFilesFound = New-Object -TypeName system.Collections.ArrayList

Get-Content -Path $FilePath | ForEach-Object 
{
	$Record = $_ -split ','
	$ShouldSearch = $true 
	$Group = $Record[0]
	$Email = $Record[1]
	if ($Email)
	{
		$EmailAddresses = $Email -split ';'
	}
	else
	{
		$MissingEmails.Add($Group)
		$ShouldSearch = $false 
	}
	
	if ($ShouldSearch)
	{
		$files = Get-ChildItem -Path "$SearchDirectory\*$Group*" -Recurse
		if ($files)
		{
			Send-MailMessage -To $SMTPTo -From $SMTPFrom -SmtpServer $SMTPServer -Subject "Located files" -Body "These files were located for you by xyz" -Attachments $files
			$FoundFiles.Add($Group)
		}
		else
		{
			$NoFilesFound.Add($Group)
		}
	}
}
$Properties = @{
	'GroupsWithFiles' = ($FoundFiles -join ';')
	'GroupsWithoutFiles' = ($NoFilesFound -join ';')
	'GroupsWithoutEmail' = ($MissingEmails -join ';')
}
Write-Output (New-Object -TypeName PSCustomObject -Property $Properties

Open in new window


$FilePath - The path to the file
$SearchDirectory - The directory to search
$SMTPFrom - The email address to send 'From'
$SMTPServer - The email address server

Also note.. wherever this script runs from will need to be allowed to send email as an SMTP Relay by the mail server, otherwise the emails will be rejected and will fail.

So..
script.ps1 -FilePath c:\temp\recipients.csv -SearchDirectory ''c:\cogtest' -SMTPFrom 'administrator@domain.tld' -SMTPServer 'smtp.domain.tld' 

Open in new window


Coralon
Avatar of rakkad

ASKER

Thanks this has been a huge help.  Can you modify the existing script that you wrote so it includes the same format, but it handles multiple emails as I attached previously

Thanks
It should be already.. its passing the existing email addresses as an array, and the send-mailmessage should pick it up.

Is it not doing that?

Coralon
Avatar of rakkad

ASKER

Hi

If you can update the script which you did in ID: 42031948 to include the processing of multiple emails that would be great

I want the format to be the same

Sorry not being clear
That's what I'm saying.. it should already be sending out emails to all the recipients for each group..
Are you saying its not sending out those emails?  or did you want each person to have their own separate email?

Coralon
Avatar of rakkad

ASKER

Hi

Sorry if i sound confusing.  I modified your script that you wrote in ID 42031948 and I managed to get it working, so much thanks for that, so I'll use that.  

The script works in terms of sending emails if there is one email address per NetworkGroup so one-to-one e.g:-

NetworkGroup              Email
First Complete               first.complete@xyz.com

However, as shown in the attached receipients.csv file uploaded earlier,
NetworkGroup names may have mutiple multiple email addresses,  so what should happen is if a NetworkGroup has more than one email address associated with the NetworkGroup name, the script should sent it to multiple addresses, e.g:-

NetworkGroup        Email
First Complete         first.complete@xyz.com, wollaton@thenottingham.com

So if you can modify the ID 42031948 script that you created to include sending mutiple emails per NetworkGroup name that would be so grateful

The script written in ID 42031948 does work, just needs modifying as discussed

I hope it makes sense now

Thank-you :)
SOLUTION
Avatar of Qlemo
Qlemo
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial