asked on

Send emails to customers with attachments using powershell-csv

Hi all,

I am trying to send emails to customers with attachments using powershell-csv. I have successfully sent emails using csv but only without attachments. When I am trying to point the script to their relevant attachment folders, it is giving error:

Send-MailMessage: Access to the path 'E:\Attachments\customerA' is denied
Send-MailMessage: Access to the path 'E:\Attachments\customerB' is denied

Which probably means that the script IS able to read the relevant folder but unable to access it. I have named the folder names to the names of the customers for ease. Can you please help. Here is the script:

Clear-Host
$SMTPServer = "mail.abc.com"
$From = "admin@abc.com"
# Import list of customers From CSV into $Userlist

$UserList=IMPORT-CSV C:\Test123.csv

FOREACH ($Person in $UserList) {

$custb = Get-ChildItem "E:\Attachments\"$Person.customer | Select -exp FullName
Send-MailMessage -From $From -to $Person.email1, $Person.email2 -cc -SmtpServer $SMTPServer -Subject "Sending File to "$Person.customer -Attachments $custb

}

Open in new window

Chris Dent

If the attachment you're passing is a folder you'll get an Access Denied message. This is because the thing that reads the attachment data gets access denied opening a stream used to encode the file.

Are you absolutely sure what you have in the variable custb is a file?

Chris

Roshan Mohammed

Instead of runing,calling, rading the files from C;\Test, adn E;\, please create a folder on your my documents and modify your script to read.attach from that path, see if it works. [It'll work]

In addition, if you run your script as an administrator, then your script should have access to the path you have defined.

disable UAC [and a reboot], to test it out.

Chris Dent

Run as Administrator is not required, this requires Read access to files, not Write.

Chris

Exchange User

ASKER

Thanks guys. I got it working. I just made 1 change in the script.

Now I am stuck in another thing,

Send-MailMessage -From $From -to $Person.email1, $Person.email2

Open in new window

For some customers, I have email2 but for some I dont. But if I am leaving email2 blank for some customers, the script is not running for those customers. Can you tell how can I address this issue ?

Chris Dent

I'd build an array to push into the To parameter. For example, I'd start with email1, adding email2 if it were there.

[Array]$To = $Pserson.email1
if ($Person.email2) {
    $To += $Person.email2
}
Send-MailMessage -To $To ....

Open in new window

I'd make it a lot more dynamic than that, but to do so I'd have to know quite a lot about the data you're reading.

Chris

Exchange User

ASKER

Thanks Chris,

So the data is primarily customer name, customer number, email1, email2, email3, email4. Now some customers need their files to be sent on multiple email addresses and some only give us one. So yes, you are right, we need the script to be dynamic.

So do you want me to copy paste exactly the above code in my script ? I'm sorry I am not a scripting guy but I have been trying to learn Powershell.

Thanks

Chris Dent

If it's formatted like that I would go with...

Clear-Host
$SMTPServer = "mail.abc.com"
$From = "admin@abc.com"
# Import list of customers From CSV into $Userlist

$UserList=IMPORT-CSV C:\Test123.csv

FOREACH ($Person in $UserList) {
    # This will get all the fields from the CSV which start with "email"
    $To = $Person.PSObject.Properties |
        Where-Object Name -like 'email*' |
        Select-Object -ExpandProperty Value

    $custb = Get-ChildItem "E:\Attachments\"$Person.customer | Select -exp FullName

    Send-MailMessage -From $From -to $To -cc -SmtpServer $SMTPServer -Subject "Sending File to "$Person.customer -Attachments $custb

Open in new window

Exchange User

ASKER

Hi Chris,

It is still giving error for those customer rows which have email2 missing.

Chris Dent

Oops, sorry, my fault. I check for the field, but not whether or not it has something in it. Here's the ammendment:

Clear-Host
$SMTPServer = "mail.abc.com"
$From = "admin@abc.com"
# Import list of customers From CSV into $Userlist

$UserList=IMPORT-CSV C:\Test123.csv

FOREACH ($Person in $UserList) {
    # This will get all the fields from the CSV which start with "email"
    $To = $Person.PSObject.Properties |
        Where-Object { $_.Name -like 'email*' -and $_.Value } |
        Select-Object -ExpandProperty Value

    $custb = Get-ChildItem "E:\Attachments\"$Person.customer | Select -exp FullName

    Send-MailMessage -From $From -to $To -cc -SmtpServer $SMTPServer -Subject "Sending File to "$Person.customer -Attachments $custb
}

Open in new window

Exchange User

ASKER

Hi Chris,

Just letting you know that you are AWESOME ! :)

One last question, even though it is working perfectly, but still I am seeing errors saying

Send-MailMessage : Cannot validate argument on parameter 'To'. The argument is null or empty. Supply an argument that is not null or empty and then try the command
again.

So any ideas how do we clear them up ?

ASKER CERTIFIED SOLUTION

Chris Dent

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Exchange User

ASKER

Thanks Chris. You are now my PowerShell Guru !

Best Regards