Powershell - Email multiple excel files to users

marcwoods · 2018-12-04T13:22:23.000Z

Hi All,

Each month we get alot of excel .xlsx sheets to email each month, the file name contains the users name so i would like to do the following if possible

marc woods.xlsx email that file to marc.woods@domain.com

joe blog.xlsx email that file to joe.blog@domain.com

and so on

Anyone point me in the right direction?

Thank you

jb04 · 2018-12-04T13:39:36.000Z

Can you export the Files directly with the right names like joe.blog.xlsx instead of joe blog?
Maybe this helps you (I used this for an automatic pst import into our Exchange servers)

$path= C:\some\lokal\folder

Get-Childitem -File -Filter *.pst -Path $path | % {$_.BaseName}
so yours would be like:

$files= Get-Childitem -File -Filter *.xlsx -Path $path| % {$_.BaseName}

Foreach ($address in $files){

send mail to $address + “domain.com”
}

edit:
I checked something and you could use -repülace to replace the spaces with dots!

so

Foreach ($address in $files){
$address = $address -replace " “,”."

send mail to $address + “domain.com”
}

Neally · 2018-12-04T14:21:23.000Z

Yeah, if it’s just the file name, and formatted like this every time, that’s easy.

If you need to read the excel file in, I’d reccomend, look into the ‘ImportExcel’ tool by Doug Finke .

If you post code, please use the ‘Insert Code’ button. Please and thank you!

PLEASE READ BEFORE POSTING! Read if you're new to the PowerShell forum! Programming & Development

Hi, and welcome to the PowerShell forum! Don’t apologize for being a “noob” or “newbie” or “n00b.” There’s just no need – nobody will think you’re stupid, and the forums are all about asking questions. Just ask! Use a descriptive subject. Don’t say “Need help” or “PowerShell Help”, actually summarize what the problem is. It helps the rest of us keep track of which problem is which. Don’t post massive scripts. We’re all volunteers and we don’t have time to read all that, nor will we copy…

@jb04

marcwoods · 2018-12-04T14:44:09.000Z

Excellent seems to be ok, how do i then attach that specific .xlsx for that user?

thanks

marcwoods · 2018-12-04T14:49:52.000Z

i actually did it like this just need the attachment bit

$address = "C:\IT"
$files= Get-Childitem -File -Filter *.xlsx -Path $path| % {$_.BaseName}
Foreach ($address in $files){
$address = $address -replace " ","."
$domain = "@domain.com"
$email = $address+$domain
Send-MailMessage -To $email -SmtpServer exchange.com -From user@ -subject main 
}

jb04 · 2018-12-04T17:00:01.000Z

Hi,

good it works for you.

I moved the variable domain out of the for-each statement, cause its always the same.

I also changed the name from address in path (for C:\IT) in a few months you may not know why it is called that way.

after set with the -Attachement option I added the excel file with some strings (please check if its right)

$path = "C:\IT"
$domain = "@domain.com"
$files= Get-Childitem -File -Filter *.xlsx -Path $path| % {$_.BaseName}
Foreach ($address in $files){
$address = $address -replace " ","."
$email = $address+$domain
Send-MailMessage -To $email -SmtpServer exchange.com -From user@ -subject main -Attachement $path + "\" + $address + ".xlsx"
}

marcwoods · 2018-12-06T11:22:21.000Z

Legend, had to remove the + symbols but it all worked.

Thank you very much