REST to email

Epicor ERP 10
,
1

10.1.600.37
Hello EpiUsers!
I am tasked with emailing out estimating information once a week.
I know that I can use Windows Task Scheduler to make the once a week email execute. And I know I can trigger PowerShell code from the Scheduler.
“Invoke-RestMethod” can be executed in PowerShell so I figured this was a good path to pursue.
I reading that the output from REST data is JSON. I want to have the data be the body of the email. I’m not having luck getting with the code I’m trying. I’m not very well versed in JSON.

Can someone point me to a good resource? Is there a better way to accomplish this?

Here’s my attempts and results:

Powershell%20Rest
Powershell Rest.jpg1202×364 173 KB

Result%20email
Result email.jpg1120×247 52 KB

2
3 Likes
3

Thank you!

I’ve adjusted the end of my Invoke to match yours:
‘api/v1/BaqSvc/EstimatorEmail/ -Credential $Cred -ContentType “Application/Json”’

Then I need to make the $message.Body like this?
“$message.Body = $data.value”

My test returns a new email with a completely blank Body.
I know I have data:
{
“odata.metadata”: “https://XXXXXXXXXXXXXXXXXXX/EpicorERP10/api/v1/BaqSvc/EstimatorEmail/$metadata#Epicor.DynamicQuery.QueryResults”,
“value”: [
{
“JobAsmbl_JobNum”: “SO12054-002-10”,
“JobHead_ProdQty”: “5.00000000”,
“JobHead_PartNum”: “Douglas County Courthouse DPV Name Insert”,
“JobHead_RevisionNum”: “A”,
“JobHead_PartDescription”: “Ready to apply digitally printed vinyl graphic.”,
“JobHead_ProdCode”: “Vinyl”,
“Calculated_Total_Est_Hours”: “0.28”,
“Calculated_Total_Actual_Hours”: “0.66”,
“ECORev_ECO”: “Greg”,
“RowIdent”: “fc745497-9b00-4168-8262-cf19ea77b3b2”

4

I see that I can get the data into a CSV file following your example.
Is that the viable solution? I’m asked to fill the body of the email. Is that can of worms I’m not aware of?

image JobAsmbl_JobNum JobHead_ProdQty JobHead_PartNum JobHead_RevisionNum JobHead_PartDescription JobHead_ProdCode Calculated_Total_Est_Hours Calculated_Total_Actual_Hours ECORev_ECO
SO12054-002-10 5 Douglas County Courthouse DPV Name Insert A Ready to apply digitally printed vinyl graphic. Vinyl 0.28 0.66 Greg
SO12469-038-1 4 DOTSQBAP A 2" x 2" x 12’ square DOT post with 2.25" x 2.25" x 3’ breakaway base post with breakaway hardware. Post price includes cast hardware for double street blade on top DOT 2.12 0 Eric
SO12651-002-0 1 Cortland Partners Corp - Wall 6 - Led test A “REVISED LED -
lighting and components required to custom color the lensed lighting at the corporate office accent wall to achieve the color effect closer to the PMS value.” Neon 1.25 0 Eric
SO14326-007-0 2 Overlook Parkway Monument Rework A Install NEW channel letters onto a 3/16" painted aluminum cover to go over existing concrete base. FABLET 76 69.16 bert
SO14357-024-0 1 Gateway Center Arena - Other 1 - Brackets A BRACKETS -(10) aluminum clips to be painted and mounted to existing sign. PANFAB 7.9 8.55 jb

5

Just attach it in an email it is more useable

1 Like
6

Jose,
Again, thank you!
I sent out the email with an attached CSV file for the data. I did NOT receive smiley faces for accomplishing my task. :slightly_frowning_face: I figured as much but was willing to try with the easy solution you provided. They wanted the body of the email.
None of this is your problem, of course.
The problem is that I’ve duplicated a formatted email body from Epicor data for Receipts (from a post from @hkeric.wci ) and they expect something similar for this email.
@hkeric.wci’s cool PO Email
My adaptation results in this:

Receipt%20Email
Receipt%20Email1109×352 111 KB

Everybody loves it. F*ck me for trying too hard!

So how screwed am I? Can I unpack that PowerShell object to a formatted table in an email?
I searched all that I know what to search for. I’m not sure I know the correct terminology to hack this together myself.
If anyone could point me to some resources, I’ll dig in on my time.
Help is greatly appreciated,
Ben

1 Like
7

You’re already setting your email to be html, so you can loop through the results and build an html table with your results. $data.value is an array of objects. For each object in the array, create a table row and table data cells for the values you need displayed. You’ll have to look up the syntax for looping in powershell. I’m not fluent in powershell syntax, but I can usually get it to work with a bit of trial and error, and I’m sure you’ll be able to, too.

The double-edged sword of “wowing” people strikes again!

2 Likes
8

:smiley:
It seems adding the BOLD bit to the end of my “Invoke…” method has made the world become friendlier:

EpicorERP10/api/v1/BaqSvc/EstimatorEmail/ -Credential $Cred -ContentType “Application/Json; charset=UTF-8”

Result%20email2
Result email2.jpg1271×615 132 KB

9

So here is the working version:

$ToEmail = @("bstohr@XXXX.com","XXXX@XXXX.com")
$From = "XXXX@XXXX.com "
$username = “XXXX”
$password = “XXXX”
$secstr = New-Object -TypeName System.Security.SecureString
$password.ToCharArray() | ForEach-Object {$secstr.AppendChar($_)}
$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $username, $secstr

$data = Invoke-RestMethod -Uri https://XXXX/EpicorERP10/api/v1/BaqSvc/EstimatorEmail/ -Credential $Cred -ContentType "Application/Json; charset=UTF-8"
$Header = @"
<style>
TABLE {border-width: 1px; border-style: solid; border-color: #CCCCCC; border-collapse: collapse;}
TH {border-width: 1px; padding: 3px; border-style: solid; border-color: black; background-color: #00FF00;}
TD {border-width: 1px; padding: 3px; border-style: solid; border-color: black;}
</style>
"@
$smtpServer = "XXXX.com"
$message = New-Object System.Net.Mail.MailMessage $From, $ToEmail
$message.Subject = "Hours of Completed Jobs Estimated by You"
$message.IsBodyHTML = $true
$message.Body = $data.value | ConvertTo-Html -Head $Header


$smtp = New-Object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($message)
#$ht = $data.value 
#Out-String -InputObject $ht
3 Likes
10

In case this helps anyone else that is trying to use REST data from a BAQ into a email body using Powershell and doesn’t like the crappy column header names. I figured out how to rename them.

Replace the $message.Body… line in the above code with this:

$message.Body = $data.value | ConvertTo-Html -Property @{N="PONum";E={$_.ARR_PONum}},@{N="Packslip";E={$_.ARR_PackSlip}},@{N="Vendor Name";E={$_.ARR_Vendor_Name}},@{N="Arrived Date";E={$_.ARR_Arrived_Date.ToShortDateString()}},@{N="Days Arrived";E={$_.ARR_Days_Arrived}},@{N="First Name";E={$_.ARR_FirstName}},@{N="Last Name";E={$_.ARR_LastName}},@{N="Order Num";E={$_.ARR_OrderNum}},@{N="Opportunity ID";E={$_.ARR_OpportunityID}},@{N="Opportunity Name";E={$_.ARR_Name}} -Head $Header
3 Likes