Just wondering if anyone else has come across this problem

I have a job that generates a user's email by getting the user who last modified an excel file. I then set this address as a variable called USER_EMAIL. I then have ${USER_EMAIL} in the destination box of the mail step.

If I run the job directly in PDI, it works fine. I created a bat file in order to schedule the job using Windows task scheduler. If I launch the bat file directly, it also works fine however if Task scheduler opens it, I get an Invalid Address error. It appears that the variable is not being substituted

2018/03/16 09:44:38 - Success Mail - ERROR (version 8.0.0.0-28, build 8.0.0.0-28 from 2017-11-05 07.27.50 by buildguy) : Problem while sending message: javax.mail.SendFailedException: Invalid Addresses;

nested exception is:

com.sun.mail.smtp.SMTPAddressFailedException: 501 5.1.3 Invalid address

2018/03/16 09:44:38 - Success Mail - ERROR (version 8.0.0.0-28, build 8.0.0.0-28 from 2017-11-05 07.27.50 by buildguy) :     ** Invalid Addresses

2018/03/16 09:44:38 - Success Mail - ERROR (version 8.0.0.0-28, build 8.0.0.0-28 from 2017-11-05 07.27.50 by buildguy) :          ${USER_EMAIL}

You will get behaviour like this if the variable is not declared anywhere.

I'm not sure if this is better behaviour than setting it to null (think what happens when ${BASE_DIR} isn't set and you do rm -rf ${BASE_DIR}/ )

When you run it from PDI, or from the batch file, set up some logging of where the value gets set.  If it's trying to pick up an OS variable, remember that Task Scheduler doesn't actually set all your usual variables.

The variable is set from a transformation which precedes the Mail step in the job

I have done something similar before where a variable was set and used in the subject of the email. This works fine so I just assumed the same could be used for the destination address

Do I need to set the variable in the batch file also?

Perhaps try doing a hard-coded trial?

Hard code your email as the destination, and the ${USER_NAME} as part of the body.  Then run that as a scheduled task to make 100% certain that the variable is being set correctly.

Using a variable as a destination address *SHOULD* be possible, so it may be a bug...  But for the most part, how things run under scheduled tasks and run in command line should be the same (it's all using JAVA at the end...)

It's a good point but I'm actually doing something a bit simpler. I have a macro on the file that stores the last modified user in a cell on a worksheet

The transformation is then simply reading the file and getting this value so task scheduler itself is not actually getting the properties of the file

I'm going to try and insert the variable into the mail subject and see if that works (as Gotn O'Guts suggested). If that works then it would appear to be an issue with using the variable in the destination box

So it turns out I'm an idiot!

I missed something in the log that showed that the variable was not actually being set because it was not receiving a row

I since narrowed that down to the fact that the file is on a network drive and the path I used was using a mapped drive path. When the transformation was run directly in PDI or by directly launching the batch file, this mapping is visible but when using task scheduler, network drive mappings are not visible. Hence it could not find the file and set the variable

For other steps in transformations (such as Get Files) the fix for this would be to use the UNC path of the file but the Excel input step does not support UNC paths (at least not consistently).

The fix therefore was to add this line to the batch file:

net use X: \\machine\share /persistent:no

Where "X" is the drive letter you want to assign, "machine" is the server name and "share" is the name of the share

More details can be found here: batch file - Task Scheduler - Access non-local drives while running task not logged in - Stack Overflow

Thanks to everyone for their help!