By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,455 Members | 2,293 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,455 IT Pros & Developers. It's quick & easy.

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

P: 8
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Option Compare Database
  3.  
  4. Sub sendSMS()
  5. 'Added Reference Microsoft XML v 6.0'
  6.  
  7. Dim RsRec As Recordset
  8. Set RsRec = CurrentDb.OpenRecordset("SELECT SMSTO, SMSMESSAGE FROM SMS_WIP WHERE SMSUSER='ANDREAS';")
  9.  
  10. Dim strResult As String
  11. Dim URL_base As String
  12. Dim str_POST As String
  13. Dim XMLHttpRequest As XMLHTTP
  14.  
  15. Set XMLHttpRequest = New MSXML2.XMLHTTP
  16.  
  17. 'example
  18. 'http://www.microsms.net/sendapiinter.asp?usr=frangxxxxxx@lwb.org.cy&psw=xxxxxx&mobnu=35799xxxxxx&title=Petros&message=ΚΑΛΗΜΕΡΑ'
  19.  
  20. URL_base = "http://www.microsms.net/sendapiinter.asp?"
  21.  
  22. Do While RsRec.EOF = False
  23.  
  24. str_POST = URL_base & _
  25.             "usr=" & "frangΧΧΧΧ@gmail.com" & _
  26.             "&psw=" & "ΧΧΧΧΧΧΧ" & _
  27.             "&mobnu=" & "35799123456" & _
  28.             "&title=" & "TEST" & _
  29.             "&Batchid=" & "200" & _
  30.             "&Dtype=" & "4" & _
  31.             "&message=" & StrConv(RsRec.Fields("SMSmessage").Value, vbUpperCase)
  32.  
  33. Debug.Print str_POST
  34.  
  35. XMLHttpRequest.Open "GET", str_POST, False
  36. XMLHttpRequest.send
  37.  
  38. ' next record
  39. RsRec.MoveNext
  40. Loop
  41.  
  42. End Sub
  43.  
  44.  
Nov 5 '13 #1

✓ answered by 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.

Expand|Select|Wrap|Line Numbers
  1. Public Function pfnConvertStringToUTF8(str as String)
  2. Dim stream as Object
  3. Dim utfStr
  4.  
  5.      Set stream = CreateObject("ADODB.Stream")
  6.      stream.Open
  7.      stream.Type     = 2 'text
  8.      stream.Position = 0
  9.      stream.Charset  = "utf-8"
  10.      stream.WriteText str
  11.      stream.Flush
  12.      stream.Position = 0
  13.      stream.Type     = 1 'binary
  14.      stream.Read(3)      'skip BOM
  15.      utfStr = stream.Read
  16.      stream.Close
  17.      pfnConvertStringToUTF8=utfStr
  18.  
  19. End Function
  20.  

Share this Question
Share on Google+
5 Replies


topher23
Expert 100+
P: 234
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.

Expand|Select|Wrap|Line Numbers
  1. Public Function pfnConvertStringToUTF8(str as String)
  2. Dim stream as Object
  3. Dim utfStr
  4.  
  5.      Set stream = CreateObject("ADODB.Stream")
  6.      stream.Open
  7.      stream.Type     = 2 'text
  8.      stream.Position = 0
  9.      stream.Charset  = "utf-8"
  10.      stream.WriteText str
  11.      stream.Flush
  12.      stream.Position = 0
  13.      stream.Type     = 1 'binary
  14.      stream.Read(3)      'skip BOM
  15.      utfStr = stream.Read
  16.      stream.Close
  17.      pfnConvertStringToUTF8=utfStr
  18.  
  19. End Function
  20.  
Nov 6 '13 #2

P: 8
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.
Expand|Select|Wrap|Line Numbers
  1.  
  2. Option Compare Database
  3.  
  4. Public Function pfnConvertStringToUTF8() As String
  5.  
  6. Dim Str As String
  7. Dim stream As Object
  8. Dim utfStr As String
  9.  
  10.  Str = StrConv("ΚΑΛΗΜΕΡΑ¨", vbUpperCase)
  11.  
  12.       Set stream = CreateObject("ADODB.Stream")
  13.      stream.Open
  14.      stream.Type = 2    'text
  15.      stream.Position = 0
  16.      stream.CharSet = "utf-8"
  17.      stream.WriteText = Str
  18.      stream.Flush
  19.      stream.Position = 0
  20.      stream.Type = 1     'binary
  21.      stream.Read (3)     'skip BOM
  22.      utfStr = stream.Read
  23.      stream.Close
  24.      pfnConvertStringToUTF8 = utfStr
  25.  
  26. Debug.Print pfnConvertStringToUTF8
  27. End Function
  28.  
Hope you can help!
Nov 6 '13 #3

topher23
Expert 100+
P: 234
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.Read (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.
Nov 6 '13 #4

P: 8
OK, THANKS. I Will give it a try and let you know.
Nov 6 '13 #5

P: 8
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.
Nov 6 '13 #6

Post your reply

Sign in to post your reply or Sign up for a free account.