0

I am developing an Excel macro, allowing the user to send an e-mail via Gmail, with Excel data converted to PDF as an attachment. To do this, I am using Eledo PDF generator and Integromat. I am using Integromat Webhook feature by sending to it the data taken directly from Excel with the use of HTTP request. Overall, the code works correctly. However, I cannot manage to send properly special characters, especially the polish ones i.e. "ą", "ę", "ż", "ź", "ś", "ć", "ł". These, as far as I know, are not included in the ASCII character codes provided in Excel. The JSON is sent, yet those characters are converted to some inappropriate, random ones, such as these: "ążóÃ", corresponding to the letters: "ążóò". I tried making the HTTP request directly from browser and manually inserting such characters as the values in JSON, and they seem to be retrieved correctly, thus, this is definitely an Excel issue. I tried setting the request header charset to UTF-8, replacing those letters in strings with their char equivalents, changing encoding with Integromat, to no avail. I have run out of ideas, none of the forum posts seem to answer my question, I found only a tiny clue as to what may be happening:

"The symptoms indicate that the JSON string which was originally in UTF-8 encoding was written to the HTTP response using ISO-8859-1 encoding and the webbrowser was instructed to display it as UTF-8. If it was written using UTF-8 and displayed as ISO-8859-1, then you would have seen aériennes. If it was written and displayed using ISO-8859-1, then you would have seen a�riennes." - I tried sending the word "aériennes" and it was retrieved as "aériennes".

Below, I am attaching my code. I will be eternally grateful for any advice!

Private Sub CommandButton7_Click()
Dim objHTTP As Object
Dim Mesg As String, Subj As String, URL As String, Json As String, WebhookID As String, HookString As String, Email As String
Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP.6.0")
Subj = Range("O125").Value
Mesg = Range("O127").Text
Email = Range("O126").Value

HookString = "Title=" & Range("N13").Value & "&" & "Material=" & Range("S13").Value & "&" & "Adhesive=" & Range("T13").Value & _
"&" & "Dimensions=" & Range("N14").Value & "&" & "Width=" & Range("O14").Value & "&" & "Length=" & Range("P14").Value & "&" & "Colours=" & Range("Q14").Value & _
"&" & "NoC=" & Range("R14").Value & "&" & "Mtype=" & Range("S14").Value & "&" & "Adtype=" & Range("T14").Value & "&" & "NoE=" & Range("N16").Value & _
"&" & "1=" & Range("O16").Value & "&" & "2=" & Range("P16").Value & "&" & "3=" & Range("Q16").Value & "&" & "4=" & Range("R16").Value & "&" & "5=" & Range("S16").Value & _
"&" & "6=" & Range("O17").Value & "&" & "7=" & Range("O18").Value & "&" & "8=" & Range("P18").Value & "&" & "9=" & Range("Q18").Value & "&" & "10=" & Range("R18").Value & "&" & "11=" & Range("S18").Value & _
"&" & "12=" & Range("O19").Value & "&" & "13=" & Range("P19").Value & "&" & "14=" & Range("Q19").Value & "&" & "15=" & Range("R19").Value & "&" & "16=" & Range("S19").Value & _
"&" & "17=" & Range("O20").Value & "&" & "18=" & Range("P20").Value & "&" & "19=" & Range("Q20").Value & "&" & "20=" & Range("R20").Value & "&" & "21=" & Range("S20").Value & _
"&" & "NoEoR=" & Range("N17").Value & "&" & "Value=" & Range("N18").Value & "&" & "Po1000=" & Range("N19").Value & "&" & "PoR=" & Range("N20").Value & "&" & "Photopolymers=" & Range("N22").Value & _
"&" & "NaS=" & Range("N23").Value & "&" & "Date=" & Range("N24").Value & "&" & "piece=" & Range("O22").Value & "&" & "PoPiece=" & Range("P22").Value & "&" & "Diecut=" & Range("R22").Value & _
"&" & "PoD=" & Range("S22").Value & "&" & "Offer=" & Range("O24").Value & "&" & "OfferN=" & Range("P24").Value

WebhookID = "xyzxyzxyzxyzxyzxyzxyzxyz"

URL = "https://hook.integromat.com/" & WebhookID & "?&Subj=" & Subj & "&Mesg=" & Mesg & "&Email=" & Email & "&" & HookString

objHTTP.Open "GET", URL, False
objHTTP.setRequestHeader "Content-type", "application/json"
objHTTP.setRequestHeader "accept-language", "pl-PL,pl;q=0.9,en-US;q=0.8,en;q=0.7"
objHTTP.setRequestHeader "user-agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.190 Safari/537.36"
objHTTP.send (Json)

End Sub
Dawid Plona
  • 1
  • 1
  • 2
    Looks like you're just sending an empty string in `Json`? `HookString` is not Json. – Tim Williams Mar 01 '21 at 17:34
  • If you're using a GET request and sending the information in the URL, then typically you want to URLencode your data items: see https://stackoverflow.com/questions/218181/how-can-i-url-encode-a-string-in-excel-vba/24301379#24301379 – Tim Williams Mar 01 '21 at 21:45
  • Thank you, I used the EncodeUrl built-in Excel function and it worked like a charm! I am extremely grateful for your input. I have another question now, descended from your first comment: I know I am in fact not sending Json, just an empty string - this part of code is taken almost directly from an Integromat tutorial and I left it as it was - without sending this empty string, the macro won't work. The Webhook seems to be converting the URL "HookString" part into Json on its own, yet this empty string has to be sent - any ideas as to why? – Dawid Plona Mar 02 '21 at 07:06
  • Well you have to use `Send`, or the request won't get fired off, but when using a GET request the server shouldn't be looking in the request body (which is where anything immediately after `Send` ends up). `Send someContentHere` is more like something you'd use in a POST request. – Tim Williams Mar 02 '21 at 07:09
  • ...if you could share the tutorial link I can take a look. – Tim Williams Mar 02 '21 at 07:18
  • It makes sense, so the Json variable is probably sort of a strawman, used only to be able to make the request, I assume. Here's the tutorial, it's a bit long because the process is shown, literally, step by step: https://www.youtube.com/watch?v=qeQmrADtFjw – Dawid Plona Mar 02 '21 at 08:24
  • Typically GET has no body content and also no "Content-type" header (but you might well have an "accept" header which tells the server what type of content you want back from your call), so it's unclear why that's there either. Looks odd to me. That the tutorial says nothing about URLencoding might be a bit of a red flag.... – Tim Williams Mar 02 '21 at 16:35
  • Thank you, that makes it way more clear to me. Besides, the URLencoding made it work, so my issue is essentially solved! – Dawid Plona Mar 03 '21 at 07:01

0 Answers0