MS PowerShell or Graph API collapsing Chr(32) (TEXT) and Cut's off html email (HTML)

Question

I am updating an older email notification ms office app (vba calling a powershell script) to use GRAPH for the email Auth/function. All is firing correctly except that the email body is returning incorrect in two scenarios. As "HTML" the body gets cut off in the middle of a tag and the complete portion of the tag is printed. And "TEXT"... Char(32)'s are collapsing to a single space effecting formatting. Tried replacing with Char(160) and character is printing in Char(160) spaces.

Please advise. TEXT - Char(32):

TEXT - Char(160):

HTML Example:

Script

$mailuser = $args[0]
$emailTo = $args[1]
$emailCC = $args[2]
$emailSubject = $args[3]
$emailBody = $args[4]

$ClientID       = "XXX"        # Should be a ~36 hex character string; insert your info here
$ClientSecret   = "XXX"    # Should be a ~44 character string; insert your info here
$tenantdomain   = "xxx"    # For example, contoso.onmicrosoft.com

$loginURL       = "https://login.microsoft.com"
$resource       = "https://graph.microsoft.com"

$body       = @{grant_type="client_credentials";resource=$resource;client_id=$ClientID;client_secret=$ClientSecret}
$oauth      = Invoke-RestMethod -Method Post -Uri $loginURL/$tenantdomain/oauth2/token?api-version=1.0 -Body $body 

$getheaderParams = @{'Authorization'="$($oauth.token_type) $($oauth.access_token)"} 
$postheaderParams = @{'Authorization'="$($oauth.token_type) $($oauth.access_token)"; 'Content-Type' = 'application/json'} 
$userurl = "https://graph.microsoft.com/v1.0/users/$UID" 

$mailurl = "https://graph.microsoft.com/v1.0/users/$mailuser/sendMail"

$mailjson = @"
{
"message": {
"subject": "$emailSubject",
"body": {
      "contentType": "HTML",
      "content": "$emailBody"
    },
    "toRecipients": [
      {
        "emailAddress": {
          "address": "$emailTo"
        }
      }
    ],
    "ccRecipients": [
      {
        "emailAddress": {
          "address": "$emailCC"
        }
      }
    ]
  },
  "saveToSentItems": "false"
}
"@ 

Invoke-WebRequest -UseBasicParsing -Headers $postheaderParams -Uri $mailurl -method POST -body $mailjson

    email_from_name = Me.sender_name_txt.Value
email_from_email = Me.sender_email_txt.Value
email_subject = Me.subject_txt
email_body_top = Me.email_body_1_txt
email_body_bot = Me.email_body_2_txt
email_to_email = ""

Set b_email = CurrentDb.OpenRecordset("Bank_Email_List")
b_email.MoveFirst

If Me.Test_Emails_chk = True Then
    email_to_email = email_from_email
    email_cc_email = Me.cc_email_txt.Value
Else
    Do Until b_email.EOF
        email_to_email = email_to_email & b_email("Email").Value & "; "
        b_email.MoveNext
    Loop
    email_cc_email = Me.cc_email_txt.Value
End If

Set rstdiscrepancies = CurrentDb.OpenRecordset("Bank_Aging_Schedule")
rstdiscrepancies.MoveFirst

VBA - email construction

email_as_total = 0
email_as_LT30 = 0
email_as_GT30 = 0
email_as_GT60 = 0
email_as_GT90 = 0
email_as_GT120 = 0
email_as_GT180 = 0

'Table header
email_body_as = "<html><b>Aging Schedule:</b><br />"
email_body_as = email_body_as & "<table style='width:75%'><tr><th align = 'right' >Total Outstanding</th><th align = 'right' >&lt= 30 Days</th><th align = 'right'>&gt 30 Days</th><th align = 'right'>&gt 60 Days</th><th align = 'right'>&gt 90 Days</th><th align = 'right'>&gt 120 Days</th><th align = 'right'>&gt 180 Days</th></tr>"

email_body_stmt = "<b>Location Summary:</b><br/><table style='width:75%'><tr><th align = 'Left' >Location</th><th align = 'right' >Total</th><th align = 'right' >&lt= 30 Days</th><th align = 'right'>&gt 30 Days</th><th align = 'right'>&gt 60 Days</th><th align = 'right'>&gt 90 Days</th><th align = 'right'>&gt 120 Days</th><th align = 'right'>&gt 180 Days</th></tr>"
    
'Table body
Do While rstdiscrepancies.EOF = False
    email_body_stmt = email_body_stmt & "<tr><td>" & Format(rstdiscrepancies("Store").Value, "000") & "</td> "
    email_body_stmt = email_body_stmt & "<td align = 'right'>" & Format(rstdiscrepancies("SumOfAmount").Value, "Standard") & "</td>"
    email_body_stmt = email_body_stmt & "<td align = 'right'>" & Format(rstdiscrepancies("LT30").Value, "Standard") & "</td>"
    email_body_stmt = email_body_stmt & "<td align = 'right'>" & Format(rstdiscrepancies("GT30").Value, "Standard") & "</td>"
    email_body_stmt = email_body_stmt & "<td align = 'right'>" & Format(rstdiscrepancies("GT60").Value, "Standard") & "</td>"
    email_body_stmt = email_body_stmt & "<td align = 'right'>" & Format(rstdiscrepancies("GT90").Value, "Standard") & "</td>"
    email_body_stmt = email_body_stmt & "<td align = 'right'>" & Format(rstdiscrepancies("GT120").Value, "Standard") & "</td>"
    email_body_stmt = email_body_stmt & "<td align = 'right'>" & Format(rstdiscrepancies("GT180").Value, "Standard") & "</td></tr>"
    email_as_total = email_as_total + rstdiscrepancies("SumOfAmount").Value
    email_as_LT30 = email_as_LT30 + rstdiscrepancies("LT30").Value
    email_as_GT30 = email_as_GT30 + rstdiscrepancies("GT30").Value
    email_as_GT60 = email_as_GT60 + rstdiscrepancies("GT60").Value
    email_as_GT90 = email_as_GT90 + rstdiscrepancies("GT90").Value
    email_as_GT120 = email_as_GT120 + rstdiscrepancies("GT120").Value
    email_as_GT180 = email_as_GT180 + rstdiscrepancies("GT180").Value
    rstdiscrepancies.MoveNext
    
    If rstdiscrepancies.EOF = True Then
        email_body_as = email_body_as & "<td align = 'right'><b>" & Format(email_as_total, "Currency") & "</b></td> "
        email_body_as = email_body_as & "<td align = 'right'>" & Format(email_as_LT30, "Standard") & "</td> "
        email_body_as = email_body_as & "<td align = 'right'>" & Format(email_as_GT30, "Standard") & "</td> "
        email_body_as = email_body_as & "<td align = 'right'>" & Format(email_as_GT60, "Standard") & "</td> "
        email_body_as = email_body_as & "<td align = 'right'>" & Format(email_as_GT90, "Standard") & "</td> "
        email_body_as = email_body_as & "<td align = 'right'>" & Format(email_as_GT120, "Standard") & "</td> "
        email_body_as = email_body_as & "<td align = 'right'>" & Format(email_as_GT180, "Standard") & "</td></tr> "
        email_as_total = 0
        email_as_LT30 = 0
        email_as_GT30 = 0
        email_as_GT60 = 0
        email_as_GT90 = 0
        email_as_GT120 = 0
        email_as_GT180 = 0
    End If
Loop

email_body_stmt = email_body_stmt & "<tr><td>" & "" & "</td> "
email_body_stmt = email_body_stmt & "<td>" & "" & "</td> "
email_body_stmt = email_body_stmt & "<td>" & "" & "</td> "
email_body_stmt = email_body_stmt & "<td>" & "" & "</td> "
email_body_stmt = email_body_stmt & "<td>" & "" & "</td> "

email_body = email_body_top & "<br><br>"
email_body = email_body & email_body_as & "</table><br> "
email_body = email_body & email_body_stmt & "</table><br> "
email_body = email_body & email_body_bot & "<br></html> "

Test = Send_MS_GRAPH_Email(email_from_email, email_to_email, email_cc_email, email_subject, email_body)

End If

Me.Test_Emails_chk = True

End Sub 'Sends email Function Send_MS_GRAPH_Email(EmailFrom, EmailTo, Emailcc, Subject, Body)

PID = Shell("POWERSHELL.exe -noexit C:\ExcelAddin\email_script.ps1 '" & EmailFrom & "' '" & EmailTo & "' '" & Emailcc & "' '" & Subject & "' '" & Body & "'", vbHide)

End Function

In html you shouldn't try to align text using spaces because spaces get normalized **unless** you put that text inside `
..
` tags. Why not create a nice table? — Theo, Oct 14 '21 at 14:13
Thanks Theo. All formatting is done using tags in HTML version. They are two seperate issues. Roadblocked in both directions. — Dan Mumford, Oct 14 '21 at 17:59
PowerShell Script added. Using for both situations changing "contentType": "HTML" to "contentType": "TEXT" for the scenario. — Dan Mumford, Oct 14 '21 at 18:08
..but `$emailBody = $args[4]` still does not reveal what actually is inside that variable.. — Theo, Oct 14 '21 at 18:46
The html code has not been changed and functioned correctly in the past. There's actually too much to post. I'm wondering if it's a payload issue with the graph api. — Dan Mumford, Oct 14 '21 at 19:02
Please trim your code to make it easier to find your problem. Follow these guidelines to create a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example). — Community, Oct 18 '21 at 02:07

MS PowerShell or Graph API collapsing Chr(32) (TEXT) and Cut's off html email (HTML)

0 Answers0