Steffan Cline Posted January 5, 2022 Share Posted January 5, 2022 Im trying to make a very simple solution to make an excel workbook and attach it to an email. The email works flawlessly, I just cant get it to find the file and attach it. I must be missing something simple but I cant figure it out. It would seem to be related to the path of where the file is being written. Suggestions ENGINE SQLORA SET DEFAULT_CONNECTION MyCONN SQL SQLORA PREPARE HOLDNAME FOR SELECT * FROM myView; END TABLE FILE HOLDNAME PRINT * ON TABLE HOLD AS DUPES FORMAT EXL07 END -SET &TMPPATH = TEMPPATH(255,'A255'); -SET &TMPPATH = TRUNCATE(&TMPPATH); -SET &ATTACHMENT_FILENAME = &TMPPATH || 'DUPES.XLSX'; -SET &SMTP_SERVER = 'mailserver'; -SET &SENDER ='john@doe.com'; -SET &RECEIVER ='john@doe.com'; -SET &SUBJECT = 'My Subject'; -SET &HTML_BODY = '<html><body>mybody</body></html>'; -TYPE powershell "$SmtpClient = new-object system.net.mail.smtpClient('&SMTP_SERVER|'); $MailMessage = New-Object system.net.mail.mailmessage; $att = new-object Net.Mail.Attachment('&ATTACHMENT_FILENAME|'); $mailmessage.Attachments.Add($att); $mailmessage.from = '&SENDER|'; $mailmessage.To.add('&RECEIVER|'); $mailmessage.Subject = '&SUBJECT|'; $MailMessage.IsBodyHtml = $true; $mailmessage.Body = '&HTML_BODY|'; $smtpclient.Timeout = 30000; $smtpclient.Send($mailmessage);" -DOS powershell "$SmtpClient = new-object system.net.mail.smtpClient('&SMTP_SERVER|'); $MailMessage = New-Object system.net.mail.mailmessage; $att = new-object Net.Mail.Attachment('&ATTACHMENT_FILENAME|'); $mailmessage.Attachments.Add($att); $mailmessage.from = '&SENDER|'; $mailmessage.To.add('&RECEIVER|'); $mailmessage.Subject = '&SUBJECT|'; $MailMessage.IsBodyHtml = $true; $mailmessage.Body = '&HTML_BODY|'; $smtpclient.Timeout = 30000; $smtpclient.Send($mailmessage);" END Link to comment Share on other sites More sharing options...
David Beagan Posted January 5, 2022 Share Posted January 5, 2022 One quick thought. You probably would need a -RUN after your last END statement. Link to comment Share on other sites More sharing options...
SATHEESH B Posted January 5, 2022 Share Posted January 5, 2022 Cant you schedule the report in Report caster Link to comment Share on other sites More sharing options...
Steffan Cline Posted January 5, 2022 Author Share Posted January 5, 2022 This worked! Thanks! I dont use it too often. Link to comment Share on other sites More sharing options...
Steffan Cline Posted January 5, 2022 Author Share Posted January 5, 2022 I would use ReportCaster but we are using an EOL version and the support person cant figure out how to fix it for me so I use a powershell request via windows scheduler to call the url and trigger the report/email. Link to comment Share on other sites More sharing options...
David Briars Posted January 5, 2022 Share Posted January 5, 2022 Hopefully, your site will get ReportCaster set up, as it is a great way to disseminate information. In the meantime, if you have a requirement to send out an email, from a focexec, you might want to take a look at the EDAMAIL application tool. EDAMAIL is documented in the Developing Reporting Applications manual. SET EXCELSERVURL = ' ' APP PREPENDPATH IBISAMP TABLE FILE CAR PRINT * ON TABLE HOLD AS Car_Report FORMAT XLSX END -RUN -MRNOEDIT EX EDAMAIL fred@barney.org, wilma@barney.org , Car Report, A, XLSX, Car_Report image.png580545 31.1 KB The -MRNOEDIT was needed since I was running from a Domain. EDAMAIL requires a configured SMTP Server node for the server workspace. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now