API error when sending text data from database

Hi,
I’m using MailJet to send transactional emails.

Im populating the Body of the email with text data that is stored in the Bubble DB. (As shown in image above)
This errors out from the API.


While simply passing a text that I typed works fine. (See image below)

I thought at first the data in the DB wasn’t being populated correct so I dumped everything into a Text field. It looks perfectly fine.

What am I doing wrong?

Thanks,
Deepak

Are you sending html mail? It would be worth checking the value sent doesn’t contain un-escaped double quotes, that messes with the JSON body, or html characters that mess with the html layout.

Thanks mishav.

I suspected this and found that the double quotes have been escaped.
MailJet’s template code (CURL example here) shows this:

curl -s \
  -X POST \
  --user "$MJ_APIKEY_PUBLIC:$MJ_APIKEY_PRIVATE" \
  https://api.mailjet.com/v3/send \
  -H 'Content-Type: application/json' \
  -d '{
    "FromEmail": "hi@geniusmechanic.com",
    "FromName": "Autto",
    "Subject": "Booking Confirmation",
    "MJ-TemplateID": "107958",
    "MJ-TemplateLanguage": true,
    "Recipients": [
      { "Email": "passenger@mailjet.com" }
    ],
    "Vars": {
    "Name": "\"Bond\"",
    "CarModel": "\"AstonMartin\"",
    "Body": "\"Testing this body\""
    }
  }'

My JSON Body is setup as follows in the API Plugin:

{
        "FromEmail":"hi@geniusmechanic.com",
        "FromName":"Autto",
        "Subject":"Vehicle inspection report",
        "MJ-TemplateID":"107958",
        "MJ-TemplateLanguage":"true",
        "Mj-TemplateErrorReporting": "dmurthy@gmail.com",
        "Recipients":[
                {
                        "Email": "<email>"
                }
          ],
            "Vars": 
            {
          "Name": "<name>",
          "CarModel": "\"<CarModel>\"",
            "Body": "\"<body>\""
    }
}

Every variable is rightly assigned when I use dynamic data except the “Body”:"" body “”.
I’ll try a few more things.

Thanks for the suggestion.

Ok. It looks like there is more to it and I don’t think it is the API route which is causing it.

I tried sending an email directly from Bubble using the option “Send Email” from the workflow.
I populated the Body field with my data from the DB like so:

And surprise surprise my Inbox does not contain any email. Nothing in my spam folder as well.

Next I removed the reference to the DB table and only the typed text remained. Like so.

I receive this email!

My DB with the table that I am calling does contain values as shown below.

So seems like Bubble doesn’t like sending an email that contains text data from a table.

Thanks,
Deepak

Ok. This was solved by using the solution by @mishav in

Basically removed rich text formatting using the regex string [.+?]

1 Like

Any double quotes in the data also need to be escaped, can do this with another find & replace.

Well done finding the solution. Thanks for explaining your steps, they will likely help other people.

1 Like

On a side note I also found that MailJet transactional templates hate multiline text.
I therefore had to remove line breaks using :find & replace regex string -

/(\r\n)+|\r+|\n+|\t+/i

I found the solution on https://kaspars.net/blog/web-development/regex-remove-all-line-breaks-from-text

Deepak

3 Likes

Maybe they can be replaced with html line breaks? <br>

hmm… I will try that out.