PowerShell script not properly e-mailing Excel report file when ran automatically in the scheduler

Question

When I run the PowerSheel script manually - through a .bat file that executes it with the PowerShell interpreter - it correctly executes an SQL query and creates an Excel file and e-mails it.

If I automate it - run the .bat file that runs the PowerShell file with the interpreter through the Windows job scheduler every night - then the e-mail is sent without the file.

It is difficulty to copy the code here because I made a nice framework many functions deep with lambda-functions and all, but generally:

$ExcelWorkBook.SaveAs($Filename)

$ExcelWorkBook.Close()

$ExcelApp.Quit()

(...)

Sleep -Seconds 500

(...)

$MailMessage.Attachments.Add($Filename)

$SMTP.Send($MailMessage)

I have heard that Excel does not actually support GUI-less automation (windows task scheduler) but that actually worked for me through a different framework.

What can I do to make it work, if cannot how can I generate Excel files from PowerShell in a different way, or how can I just easily generate and e-mail reports in PowerShell?

Do you really need an Excel file? Can you make a much simpler CSV file for your report? — dpw
Debug your setup step-by-step. First off, set the task to only send an email. If it works, add attachment. If it works, then the email part is OK. Set the task to create a simple Excel document without SQL and save it to some dir. If it works, try emailing that. This way you should be able to find out which part of the process goes haywire: Excel, Sql, Mail or something completely different. — vonPryz

user1315373 user1315373 · Accepted Answer · 2012-07-30T09:33:41

@Dan yes I do. CSV is a really poor report format for corporate reporting, no colors, no formatting, nothing. A manager would be insulted to find that in his inbox.

@VonPryz - but manually it works! So you say debug the scheduling?

PowerShell script not properly e-mailing Excel report file when ran automatically in the scheduler

2 Answers