Character setting (utf-8) - Sending SMS via Access

[Petros73]

Hi, I am building a code to send SMS messages via access. The code is working fine when sending English text. The problem I have refers to the greek text. When I send a Greek message, the result is ????

The first option is to be able to convert the message to UTF-8, for example the Greek "Α" should be equal to %CE%91.
Does anyone have any code on this?

Alternatively, is their anyway to set the url which is posted to UTF-8 or code page 65001.

Below is the code I use to send the message.

Option Compare Database

Sub sendSMS()
'Added Reference Microsoft XML v 6.0'

Dim RsRec As Recordset
Set RsRec = CurrentDb.OpenRecordset("SELECT SMSTO, SMSMESSAGE FROM SMS_WIP WHERE SMSUSER='ANDREAS';")

Dim strResult As String
Dim URL_base As String
Dim str_POST As String
Dim XMLHttpRequest As XMLHTTP

Set XMLHttpRequest = New MSXML2.XMLHTTP

'example
'http://www.microsms.net/sendapiinter.asp?usr=frangxxxxxx@lwb.org.cy&psw=xxxxxx&mobnu=35799xxxxxx&title=Petros&message=ΚΑΛΗΜΕΡΑ'

URL_base = "http://www.microsms.net/sendapiinter.asp?"

Do While RsRec.EOF = False

str_POST = URL_base & _
            "usr=" & "frangΧΧΧΧ@gmail.com" & _
            "&psw=" & "ΧΧΧΧΧΧΧ" & _
            "&mobnu=" & "35799123456" & _
            "&title=" & "TEST" & _
            "&Batchid=" & "200" & _
            "&Dtype=" & "4" & _
            "&message=" & StrConv(RsRec.Fields("SMSmessage").Value, vbUpperCase)

Debug.Print str_POST

XMLHttpRequest.Open "GET", str_POST, False
XMLHttpRequest.send

' next record
RsRec.MoveNext
Loop

End Sub

[topher23]

Found this on another forum and tweaked it. Use an ADO stream object to do the conversion in a function. I'm pretty sure this will work in VBA.

Public Function pfnConvertStringToUTF8(str as String)
Dim stream as Object
Dim utfStr

     Set stream = CreateObject("ADODB.Stream")
     stream.Open
     stream.Type     = 2 'text
     stream.Position = 0
     stream.Charset  = "utf-8"
     stream.WriteText str
     stream.Flush
     stream.Position = 0
     stream.Type     = 1 'binary
     stream.Read(3)      'skip BOM
     utfStr = stream.Read
     stream.Close
     pfnConvertStringToUTF8=utfStr

End Function

[Petros73]

Hi, Thanks for the initial help. I have posted your code in access VBA but I get ????? as a response to the Str "ΚΑΛΗΜΕΡΑ¨" . Below is the code after some adjustments.

Option Compare Database

Public Function pfnConvertStringToUTF8() As String

Dim Str As String
Dim stream As Object
Dim utfStr As String
 
 Str = StrConv("ÊÁËÇÌÅÑÁ¨", vbUpperCase)
 
      Set stream = CreateObject("ADODB.Stream")
     stream.Open
     stream.Type = 2    'text
     stream.Position = 0
     stream.CharSet = "utf-8"
     stream.WriteText = Str
     stream.Flush
     stream.Position = 0
     stream.Type = 1     'binary
     stream.Read (3)     'skip BOM
     utfStr = stream.Read
     stream.Close
     pfnConvertStringToUTF8 = utfStr
 
Debug.Print pfnConvertStringToUTF8
End Function

Hope you can help!

[topher23]

Right, The ??????'s display because it's actually giving you binary UTF-8. I'd think that, if your provider supports UTF-8 encoding, then they would do the decoding on their end to convert it back into text. You could try keeping the stream type as 2, commenting out "stream.Rea d (3)" and changing "utfStr = stream.Read" to "utfStr = stream.ReadText " - you'll output the same exact text, but the encoding might be correct for your SMS provider. Only testing the actual SMS would show that for sure.

[Petros73]

OK, THANKS. I Will give it a try and let you know.

[Petros73]

Topher you are a genius! It worked just great! After more than a week of researching this issue...I am relieved! Have a great evening and many thanks for your support.