2

I have a paperless system for mileage reimbursement sheets. We have had some issues with people submitting multiple sheets and to help supervisors check for that I created a PowerShell script that does an SQL query and creates a text file with all of the supervisors. Then, it reads that list and runs another SQL query to get all of their employees names and date ranges of previously submitted mileage sheets, saves that to a CSV file and emails it to the supervisor so they can check it when approving the next set of sheets.

When I run the script from the command line it works great. I want to schedule it to run weekly. When I test it, however, it hangs. It creates the first file of supervisors. After doing some testing, (I commented out the section that sends mail) it hangs sending the first email message. I have the task scheduled to run with the same credentials I used to create the credentials file. Any suggestions?

Here is what I have to send mail 

Param($User,$File)
$User="System_Mangler@familyenrichment.cc"
$password = Get-Content "SystemMangler.txt" | ConvertTo-SecureString 
$credential = New-Object System.Management.Automation.PsCredential($user,$password)

That is at the very beginning of the script. This is in the loop that sends mail. For testing purposes I am having it send everything to me rather than users.

$From = "System_Mangler@familyenrichment.cc"
$To = "ebosworth@familyenrichment.cc"
$Attachment = "c:\backup\tools\testing.csv"
$Subject = "Mileage Date Ranges"
$Body = "Here is a list of your employees and dates of previously submitted mileage sheets. When approving mileage sheets, Please check to make sure this is not a duplicate. `r`n Thank you `r`n The System Mangler"

$SMTPServer = "smtp.gmail.com"
$SMTPPort = "587"
Send-MailMessage -From $From -To $To -Subject $Subject `
    -Body $Body -SmtpServer $SMTPServer -UseSsl -Port $SMTPPort  `
    -Credential $Credential -Attachments $Attachment
Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
btg
  • 21
  • 4
  • Are you running it as a scheduled task on the same machine that you were testing it manually? – Mark Wragg Mar 28 '17 at 12:45
  • If the answer to Mark's question is Yes: Do your smtp logs show more information? Else that is your issue since the credentials will only work on the machine they were created on. – Matt Mar 28 '17 at 13:21
  • I'd try to run the script with the same credentials as the task, usually with [Windows Sysinternals PsExec](https://technet.microsoft.com/en-us/sysinternals/bb897553.aspx). It could be that the script is waiting for user input (for example, if the ConvertTo-SecureString or the PsCredential call fails) – Cebe Mar 28 '17 at 13:38
  • [Related](http://stackoverflow.com/a/41635982/1630171). – Ansgar Wiechers Mar 28 '17 at 23:00
  • The answer to Mark's question is yes. Looking at the logs this is the error I got Error Message = Exception calling ".ctor" with "2" argument(s): "Cannot process argument because the value of argument "password" is null. Change the value of argument "password" to a non-null value." Fully Qualified Error ID = ConstructorInvokedThrowException,Microsoft.PowerShell.Commands.NewObjectCommand Obviously it has something to do with passing the credentials but I am confused as to what. On second thought I haven't tested this but I think I see the problem. The path of the credentials file... – btg Mar 29 '17 at 11:58
  • That was it. Thanks for helping me find the obvious. I put the filename in for the credentials file and then ran it from the same directory. When it was run from the task scheduler it didn't know what directory to find the file. – btg Mar 29 '17 at 12:06

0 Answers0