Powershell Snd-MailMessage Fail to Attach File

Post Reply
kenship
Regular Participant
Posts: 194
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: Planning Analytics 2.0.7
Excel Version: 2010

Powershell Snd-MailMessage Fail to Attach File

Post by kenship »

Hi all,

I have a functioning TI process to send email. I'm trying to modify it to attach file but failed.

Code: Select all

sFrom = 'TM1_Alert@123XYZ.com';
sTo1 = pRecipient1;
sSubject =pSubject;
sBody = pBody;
sSMTPServer = 'smtp.123XYZ.com';
sAttachment = 'E:\System\ExtractFiles\Test.csv';

# build powershell command
sPsCmd1 = '"& {
Send-MailMessage 
-From ' | sFrom | ' 
-To ' | sTo1 | ' 
-Subject ' | '\"' | sSubject | '\
-Body ' | '\"' | sBody | '\"
-Attachments  ' | sAttachment | '
-SmtpServer ' | sSmtpServer | '
}"';

# execute command
ExecuteCommand ( 'powershell.exe -command ' | sPsCmd1, 1 );
" -Attachments ' | sAttachment | ' " is the new line I added. The process runs fine if I exclude it, but failed (not email generated) if included.

Regarding the file path, "E:\System\Data" is where the TM1 data is stored.

Any help is appreciated.

Kenneth
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: Powershell Snd-MailMessage Fail to Attach File

Post by MarenC »

Hi,

You do have 2 spaces between:

-Attachments '

Maren
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Powershell Snd-MailMessage Fail to Attach File

Post by gtonkin »

And more likely -Attachments should be -Attachment
Niko
Posts: 50
Joined: Tue Feb 15, 2022 6:43 am
OLAP Product: IBM Cognos TM1
Version: PA 2.09 / TM1 11.7.00000.42
Excel Version: EXCEL 2019
Location: Asia

Re: Powershell Snd-MailMessage Fail to Attach File

Post by Niko »

kenship wrote: Fri Sep 02, 2022 3:21 pm Hi all,

I have a functioning TI process to send email. I'm trying to modify it to attach file but failed.

Code: Select all

sFrom = 'TM1_Alert@123XYZ.com';
sTo1 = pRecipient1;
sSubject =pSubject;
sBody = pBody;
sSMTPServer = 'smtp.123XYZ.com';
sAttachment = 'E:\System\ExtractFiles\Test.csv';

# build powershell command
sPsCmd1 = '"& {
Send-MailMessage 
-From ' | sFrom | ' 
-To ' | sTo1 | ' 
-Subject ' | '\"' | sSubject | '\
-Body ' | '\"' | sBody | '\"
-Attachments  ' | sAttachment | '
-SmtpServer ' | sSmtpServer | '
}"';

# execute command
ExecuteCommand ( 'powershell.exe -command ' | sPsCmd1, 1 );
" -Attachments ' | sAttachment | ' " is the new line I added. The process runs fine if I exclude it, but failed (not email generated) if included.

Regarding the file path, "E:\System\Data" is where the TM1 data is stored.

Any help is appreciated.

Kenneth
Can powershell cmd send email with attachment?
kenship
Regular Participant
Posts: 194
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: Planning Analytics 2.0.7
Excel Version: 2010

Re: Powershell Snd-MailMessage Fail to Attach File

Post by kenship »

To answer all:

https://edu.cubewise.com/2019/11/13/tip ... using-tm1/

- Tested 2 spaces and 1 space have no difference
- "Attachments" not "Attachment"
shahhere
Posts: 10
Joined: Thu Mar 03, 2022 7:13 pm
OLAP Product: IBM PA
Version: 2.0.9.12
Excel Version: O365

Re: Powershell Snd-MailMessage Fail to Attach File

Post by shahhere »

kenship wrote: Sat Sep 03, 2022 1:59 pm To answer all:

https://edu.cubewise.com/2019/11/13/tip ... using-tm1/

- Tested 2 spaces and 1 space have no difference
- "Attachments" not "Attachment"
If you want to use VBS and a Batch script I have a script I've used and modified to send attachments along with an optional HTML body as well...let me know.

Shahhere
ascheevel
Community Contributor
Posts: 286
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: PA 2.0.9.1
Excel Version: 365
Location: Minneapolis, USA

Re: Powershell Snd-MailMessage Fail to Attach File

Post by ascheevel »

kenship wrote: Fri Sep 02, 2022 3:21 pm

Code: Select all

sAttachment = 'E:\System\ExtractFiles\Test.csv';
Regarding the file path, "E:\System\Data" is where the TM1 data is stored.
Are you saying the file you're trying to attach and your tm1 data directory are in similar location on the E:? If not, have you confirmed your TM1 server can read/write files from E:\System\ExtractFiles\? You perhaps already know this, but for anyone stumbling on this who doesn't, when you run a command from a TI process using ExecuteCommand, it's executed on the server and user account that's hosting that specific TM1 server.

When I've done things like this in powershell in the past, I always make sure the powershell works first on my local machine before trying to run it from TI process on tm1 server. Have you done that already?
kenship
Regular Participant
Posts: 194
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: Planning Analytics 2.0.7
Excel Version: 2010

Re: Powershell Snd-MailMessage Fail to Attach File

Post by kenship »

Just wondering but I suppose I can still call the script from TI right? If so, then yes please share I definitely appreciate it.

Kenneth
shahhere wrote: Sat Sep 03, 2022 2:37 pm
kenship wrote: Sat Sep 03, 2022 1:59 pm To answer all:

https://edu.cubewise.com/2019/11/13/tip ... using-tm1/

- Tested 2 spaces and 1 space have no difference
- "Attachments" not "Attachment"
If you want to use VBS and a Batch script I have a script I've used and modified to send attachments along with an optional HTML body as well...let me know.

Shahhere
kenship
Regular Participant
Posts: 194
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: Planning Analytics 2.0.7
Excel Version: 2010

Re: Powershell Snd-MailMessage Fail to Attach File

Post by kenship »

The file to be attached is actually on the same drive as TM1 data (E: drive).

I suppose I can test it in my local machine but then if works in local machine but not on TM1 server then what's does it mean?

Kenneth
ascheevel wrote: Sat Sep 03, 2022 3:22 pm
kenship wrote: Fri Sep 02, 2022 3:21 pm

Code: Select all

sAttachment = 'E:\System\ExtractFiles\Test.csv';
Regarding the file path, "E:\System\Data" is where the TM1 data is stored.
Are you saying the file you're trying to attach and your tm1 data directory are in similar location on the E:? If not, have you confirmed your TM1 server can read/write files from E:\System\ExtractFiles\? You perhaps already know this, but for anyone stumbling on this who doesn't, when you run a command from a TI process using ExecuteCommand, it's executed on the server and user account that's hosting that specific TM1 server.

When I've done things like this in powershell in the past, I always make sure the powershell works first on my local machine before trying to run it from TI process on tm1 server. Have you done that already?
User avatar
Elessar
Community Contributor
Posts: 332
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Contact:

Re: Powershell Snd-MailMessage Fail to Attach File

Post by Elessar »

Hi,

Here is mine:

Code: Select all

$serverSmtp = "smtp.XXXXXX.XXX"

#May be different! Check with mail server administrator
$port = 587
$encoding = [System.Text.Encoding]::UTF8
$From = "XX@XXXX.XXX"
$To1 = "XXX"
$To2 = "XXXXX"
$subject = "My subject"
$user = "XXX"
$pass = "XXXX"
$date = Get-Date -Format "dd.MM.yyyy"
$mes = New-Object System.Net.Mail.MailMessage
$mes.BodyEncoding = $Encoding
$mes.SubjectEncoding = $Encoding
$mes.From = $from
$mes.To.Add($to1)
$mes.To.Add($to2)
$mes.Subject = $subject
$mes.IsBodyHTML = $true
$mes.Body = "<h1>Bla bla blah " + $date + " </h1>"

$file = "D:\Folder\MyFile.txt" 
$mes.Attachments.Add($file)
$smtp = New-Object Net.Mail.SmtpClient($serverSmtp, $port)
$smtp.EnableSSL = $true
$smtp.Credentials = New-Object System.Net.NetworkCredential($user, $pass)

$smtp.Send($mes)
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
ascheevel
Community Contributor
Posts: 286
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: PA 2.0.9.1
Excel Version: 365
Location: Minneapolis, USA

Re: Powershell Snd-MailMessage Fail to Attach File

Post by ascheevel »

kenship wrote: Sun Sep 04, 2022 7:56 pm I suppose I can test it in my local machine but then if works in local machine but not on TM1 server then what's does it mean?
It rules out the obvious culprits. Briefly looking at your ps code, I don't see anything glaringly wrong. I normally don't construct powershell code right in a TI, preferring to save off to a ps1 file and then call that via TI ExecuteCommand. This allows me to ensure that the script itself is sound before trying to call via TI and if TI call doesn't work then it may be an execution policy or some other factor.

This is grasping at straws, but although I don't see any spaces in your attachment filename, you could try wrapping it in double quotes.
kenship
Regular Participant
Posts: 194
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: Planning Analytics 2.0.7
Excel Version: 2010

Re: Powershell Snd-MailMessage Fail to Attach File

Post by kenship »

After trying a few things I got the answer.

The command has to be run in Epilog not Prolog. Not sure why because the working TI has been in Prolog all the time.

Also the attachment operator should be: -Attachments ' | ' \ " ' |pAttachment| '\"

Now it's working.
ascheevel
Community Contributor
Posts: 286
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: PA 2.0.9.1
Excel Version: 365
Location: Minneapolis, USA

Re: Powershell Snd-MailMessage Fail to Attach File

Post by ascheevel »

kenship wrote: Tue Sep 06, 2022 1:57 pm The command has to be run in Epilog not Prolog. Not sure why because the working TI has been in Prolog all the time.
Were you doing something else in the prolog like generating the export file you were trying to attach to email?
kenship
Regular Participant
Posts: 194
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: Planning Analytics 2.0.7
Excel Version: 2010

Re: Powershell Snd-MailMessage Fail to Attach File

Post by kenship »

When I was testing -Attachments, it's all run from Prolog. I was testing to attach a file already in the data directory.

Now it's run from Epilog, attaching a file in the data directory. There's no asciiOutput run at all.

Kenneth
ascheevel wrote: Tue Sep 06, 2022 2:01 pm
kenship wrote: Tue Sep 06, 2022 1:57 pm The command has to be run in Epilog not Prolog. Not sure why because the working TI has been in Prolog all the time.
Were you doing something else in the prolog like generating the export file you were trying to attach to email?
ascheevel
Community Contributor
Posts: 286
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: PA 2.0.9.1
Excel Version: 365
Location: Minneapolis, USA

Re: Powershell Snd-MailMessage Fail to Attach File

Post by ascheevel »

Great, thanks for confirming. Wanted to ensure it wasn't due to file lock issue by some other script above your ExeucteCommand.

Glad to hear you've got it working now.
shahhere
Posts: 10
Joined: Thu Mar 03, 2022 7:13 pm
OLAP Product: IBM PA
Version: 2.0.9.12
Excel Version: O365

Re: Powershell Snd-MailMessage Fail to Attach File

Post by shahhere »

kenship wrote: Tue Sep 06, 2022 2:15 pm When I was testing -Attachments, it's all run from Prolog. I was testing to attach a file already in the data directory.

Now it's run from Epilog, attaching a file in the data directory. There's no asciiOutput run at all.

Kenneth
ascheevel wrote: Tue Sep 06, 2022 2:01 pm
kenship wrote: Tue Sep 06, 2022 1:57 pm The command has to be run in Epilog not Prolog. Not sure why because the working TI has been in Prolog all the time.
Were you doing something else in the prolog like generating the export file you were trying to attach to email?
I am attaching the Process, VBS script and the Batch file that's used in conjunction with a separate HTML create process. The items I have will create an HTML email body along with an attachment if you need that as well.

Shahhere
Attachments
ADMIN - Send HTML Email.pro
PRO file assumes all the paths are correct. All variables saved in Global Cube.
(7.33 KiB) Downloaded 101 times
SendHTMLMail.vbs.log
Remove extension of .log
(2.37 KiB) Downloaded 98 times
SendHTMLEMail.bat.LOG
Remove extension of .log
(687 Bytes) Downloaded 96 times
kenship
Regular Participant
Posts: 194
Joined: Thu May 30, 2013 1:41 pm
OLAP Product: Cognos
Version: Planning Analytics 2.0.7
Excel Version: 2010

Re: Powershell Snd-MailMessage Fail to Attach File

Post by kenship »

Thanks! I'll probably stick with what I have for now. We wanna keep things as simple as possible since we don't have the admin. right to the Windows server and we can only work with whatever we can in Architect.

Kenneth
shahhere wrote: Tue Sep 06, 2022 2:33 pm
kenship wrote: Tue Sep 06, 2022 2:15 pm When I was testing -Attachments, it's all run from Prolog. I was testing to attach a file already in the data directory.

Now it's run from Epilog, attaching a file in the data directory. There's no asciiOutput run at all.

Kenneth
ascheevel wrote: Tue Sep 06, 2022 2:01 pm

Were you doing something else in the prolog like generating the export file you were trying to attach to email?
I am attaching the Process, VBS script and the Batch file that's used in conjunction with a separate HTML create process. The items I have will create an HTML email body along with an attachment if you need that as well.

Shahhere
Post Reply