Using Twilio with Access to send & receive text & voice messages

String to date conversion works perfectly. Thanks once again!
 
In case someone else is using this thread as a reference:

The time stamp in the downloaded logs from Twilio is in UTC. If you are in the UK that is not a problem, but I am in Canada. I was able to overcome this by implementing the functions I found here:

https://stackoverflow.com/questions/23903872/convert-utc-time-to-local

Unrelated to this, I am finding Twilio to be an excellent service, very affordable and the tech support is very helpful. As I said, we Access programmers are quite limited as their programming docs use Python, Ruby, Java, .NET and so on but using the information the Colin has kindly shared in this thread and some help from Twilio you can really get a good Access-based SMS app going.
 
I've spent the past month or so building a fancy interface around the code that Colin posted to receive messages and I am just realizing that I have a problem. I earlier reported that it's taking 10 or more minutes for the messages to be received but I now realized that one I click the receive button, it will not receive new messages until the application is shut down and then restarted.

I have experimented by deleting the downloaded CSV file and then clicking the download button again and for some reason, it keeps downloading the same CSV file that does not include new messages until the Access application is restarted. Shutting the form and reopening does not help.

I don't know much about the HTTP GET protocol but I am wondering if something stays in memory while the application is running and something needs to be refreshed? I used the exact code posted so the variables are properly released when the function is finished. There is a statement "Set http = Nothing" but I'm wondering if it needs to be closed in some way before it can be accessed again.

Thank you
 
Last edited:
Upon further reading, it seems that the protocol caches the results somewhere and doesn't release the cache until the application is restarted. I see posts about sending a random URL request to clear the cache. Does anyone know about this? It's likely this is the problem.
 
Normally you put a ? after the url, I *thought* that got the page again and did not use cache?
I use that method when I have changed pages on my websites and cannot see the changes until I do that.?

HTH
 
Hi Gasman

Thanks for your reply. This is a first for me using MSXML GET so it's a bit of a learning curve:)

You are suggesting that I put a ? after the URL. In Colin's example, I am using the URL

"https://api.twilio.com/2010-04-01/Accounts/" & ACCOUNTSID & "/SMS/Messages.csv"

in this case, where would I insert the question mark?

Thank you!
 
Well I'd try right at the end with csv?, but then I have only used it on non download urls, but worth a try? :D
 
Gasman said:
Well I'd try right at the end with csv?, but then I have only used it on non download urls, but worth a try? :D
Click to expand...

Unfortunately, that didn't work :(

I tried after the //api.twilio.com part and the application just froze up completely.

Thank you
 
I've been out all day and haven't checked my code for a long time. However:
1. I used a timer event as stated in an earlier post to check for received messages every 5 minutes. The code ensures only messages received in that time are imported to prevent duplication. The app does NOT need to be restarted for this to work.
2. It also works for received voice messages and optionally downloads a transcription of the messages or a recording.
3. As previously mentioned I chose to use CSV as it is the default and because at the time I did this I knew very little about working with XML or JSON. One year later and I'd probably use JSON.
4. As CSV is the default it doesn't have to be specified. From memory, if using XML you need to end the GET statement with ?xml

I apologise for any vagueness in my answers but I haven't used the code for some time. It was originally written for an existing client but that contract has since ended for unrelated reasons. However I know that it works.
I can check my code this weekend if you can be specific what you want me to check
 
Hi Colin

ridders said:
1. I used a timer event as stated in an earlier post to check for received messages every 5 minutes. The code ensures only messages received in that time are imported to prevent duplication. The app does NOT need to be restarted for this to work.
Click to expand...

I don't get why it works for you but not for me! I did solve this, though. More about that later.



2. It also works for received voice messages and optionally downloads a transcription of the messages or a recording.
Click to expand...

I note that you are receiving voice messages as well. Is there documentation somewhere that is compatible with Access VBA users? As I've said, Twilio is thoroughly documented but other than one blog post I haven't seen anything for us Access users. Are you able to do cool stuff like automating calls and responses (e.g. the message says "press 1 to confirm and 2 to cancel" and then import the results? I see that it can be done using Python on a cloud server but nothing about Access.

3. As previously mentioned I chose to use CSV as it is the default and because at the time I did this I knew very little about working with XML or JSON. One year later and I'd probably use JSON.
4. As CSV is the default it doesn't have to be specified. From memory, if using XML you need to end the GET statement with ?xml
Click to expand...

I just tested it without the .csv and it downloaded in XML

So, after hours of looking online I found a post on a blog that says that XMLHTTP60 caches for some historic reason and you have to clear the cache by calling a random URL (I've seen many posts on this topic with people having the same problem) whereas ServerXMLHTTP does not have the problem. I took a stab at it by replacing the mentions of XMLHTTP with ServerXMLHTTP and it works perfectly. I hope there is no downside but I've been testing it all day and it's working flawlessly.

I've written a complete SMS inbox with a little popup notification set on a 1 minute timer and it's really a nice project. Once again, thank you, Colin. I could not have done it without you.

Cheers
 
Hi Shadow

shadow9449 said:
1. I don't get why it works for you but not for me! I did solve this, though. More about that later.
Click to expand...
Ah - clearly I'm special! ;)

shadow9449 said:
2. I note that you are receiving voice messages as well. Is there documentation somewhere that is compatible with Access VBA users? As I've said, Twilio is thoroughly documented but other than one blog post I haven't seen anything for us Access users. Are you able to do cool stuff like automating calls and responses (e.g. the message says "press 1 to confirm and 2 to cancel" and then import the results? I see that it can be done using Python on a cloud server but nothing about Access.
Click to expand...

The only documenbtation I found for Access was the article I told you about previously
That got me started & all other code was my own
I never attempted to do code like "press 1 to confirm" as my ex-client didn't request those features.
Unfortunately the whole project became a redundant exercise in the end.
The client used it briefly & successfully as part of a larger schools database.
End users were very happy with it
Unfortunately my contract was ended soon after following a whole school review of all software applications.
Since then this messaging project has been dormant. Perhaps its time to resurrect it as a stand-alone application

shadow9449 said:
3. I just tested it without the .csv and it downloaded in XML
Click to expand...
Sorry - clearly my memory was faulty on that

shadow9449 said:
4. So, after hours of looking online I found a post on a blog that says that XMLHTTP60 caches for some historic reason and you have to clear the cache by calling a random URL (I've seen many posts on this topic with people having the same problem) whereas ServerXMLHTTP does not have the problem. I took a stab at it by replacing the mentions of XMLHTTP with ServerXMLHTTP and it works perfectly. I hope there is no downside but I've been testing it all day and it's working flawlessly.
Click to expand...
For whatever reason, its never been an issue for me - I used CSV and then later tested using JSON output - didn't bother with XML
This is part of my code for receiving SMS messages

Code: 
Dim http As MSXML2.XMLHTTP60

  'MessageUrl = GetBaseURL() & "/2010-04-01/Accounts/" & GetMessageAccountSID() & "/Messages.csv"
    
    'filter for received messages sent after last message check e.g.
    'MessageUrl = GetBaseURL() & "/2010-04-01/Accounts/" & GetMessageAccountSID() & "/Messages.csv?To=44.....&DateSent>=2017-02-11"
    strCriteria = "/Messages.csv?&To=" & GetTrimmedFromPhoneNumber() & "&DateSent>=" & GetModifiedLastReceivedSMSMessageCheck() & ""
    'Debug.Print strCriteria
    MessageUrl = GetBaseURL() & "/2010-04-01/Accounts/" & GetMessageAccountSID() & GetCriteria()
    'Debug.Print MessageUrl
    ' setup the request and authorization
    Set http = New MSXML2.XMLHTTP60
    
    http.Open "GET", MessageUrl, False, GetMessageAccountSID(), GetMessageAuthToken()
    
    http.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    http.Send ""
    
   'Debug.Print http.responseText


shadow9449 said:
5 I've written a complete SMS inbox with a little popup notification set on a 1 minute timer and it's really a nice project. Once again, thank you, Colin. I could not have done it without you.
Click to expand...

You're welcome - it would be interesting to see what you've done

For info I've attached a PDF which I created as a user guide for this feature - to reduce file size, image quality was also reduced
There is a separate guide for setting up the feature
 

Attachments

Last edited:
ridders said:
The only documentation found for Access was the article I told you about previously
Click to expand...

Ok and how did you modify the SMS send code to enable sending voice messages?

You're welcome - it would be interesting to see what you've done
Click to expand...

Sure. I've attached images of the Inbox as well as the notification. The latter is on a 3 second transparancy fade in/fade out.

For info I've attached a PDF which I created as a user guide for this feature - to reduce file size, image quality was also reduced
There is a separate guide for setting up the feature
Click to expand...

I browsed through it and it looks great! I'll check it out when I have a chance.
 

Attachments

  • SMSInbox.JPG
    SMSInbox.JPG
    43.2 KB · Views: 259
  • SMSNotify.JPG
    SMSNotify.JPG
    20.5 KB · Views: 257
shadow9449 said:
Ok and how did you modify the SMS send code to enable sending voice messages?
Click to expand...

This was the main part of the code though there lots else to it as well

Code: 
Function MakeCall(FromNumber As String, ToNumber As String, Message As String)
        
    On Error GoTo Err_Handler
    
    If IsLoaded("frmSendMessageTest") Then Forms!frmSendMessageTest.lblInfo.visible = False
    
    'get start time - needed here to calculate duration of call
    dblStart = CDbl(Now())
    
    ' setup the URL
    CallUrl = GetBaseURL() & "/2010-04-01/Accounts/" & GetMessageAccountSID() & "/Calls"
    
    ' setup the request and authorization
    Set http = New MSXML2.XMLHTTP60
    
    http.Open "POST", CallUrl, False, GetMessageAccountSID(), GetMessageAuthToken()
    http.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    
    PostData = "From=" & URLEncode(FromNumber) _
                & "&To=" & URLEncode(ToNumber) _
                & "&Url=http://twimlets.com/message?Message=" & URLEncode(URLEncode(Message, True))
    
    'Debug.Print postData
    
    ' send the POST data
    http.Send PostData
    
    ' optionally write out the response if you need to check if it worked
    'Debug.Print http.Status
    'Debug.Print http.responseText
    
    'calculate time taken
    dblEnd = CDbl(Now())
    strTimeTaken = Format(CDate(dblEnd - dblStart), "hh:mm:ss")
    lngDuration = CDbl(DateDiff("s", CDate(dblStart), CDate(dblEnd)) / 60) 'duration in minutes
        
    'Debug.Print dblStart; dblEnd; strTimeTaken; lngDuration
    
    strHTTPResponseText = Replace(http.responseText, "><", ">" & vbNewLine & "<")
    
    intHTTPStatus = http.Status
    'Debug.Print strHTTPResponseText
    
     'setup message response text
        
    Select Case intHTTPStatus
    
    Case 201  'success
        int201 = int201 + 1
        'Debug.Print strHTTPResponseText
    Case 400 'bad request
        int400 = int400 + 1
        strErrorText = "Message failed with HTTP status error " & http.Status & " " & http.statusText & vbNewLine & _
            "===================================================" & vbNewLine & _
            "HTTP response text: " & strHTTPResponseText
            
    Case 401 'unauthorised
        int401 = int401 + 1
        If strErrorText <> "" Then
            strErrorText = strErrorText & vbNewLine & vbNewLine & _
                "Message failed with HTTP status error " & http.Status & " " & http.statusText & vbNewLine & _
                "===================================================" & vbNewLine & _
                "HTTP response text: " & strHTTPResponseText
        Else
            strErrorText = "Message failed with HTTP status error " & http.Status & " " & http.statusText & vbNewLine & _
                "===================================================" & vbNewLine & _
                "HTTP response text: " & strHTTPResponseText
        End If
        
    Case Else 'other failure
        intOther = intOther + 1
        If strErrorText <> "" Then
            strErrorText = strErrorText & vbNewLine & vbNewLine & _
                "Message failed with HTTP status error " & http.Status & " " & http.statusText & vbNewLine & _
                "===================================================" & vbNewLine & _
                "HTTP response text: " & strHTTPResponseText
        Else
            strErrorText = "Message failed with HTTP status error " & http.Status & " " & http.statusText & vbNewLine & _
                "===================================================" & vbNewLine & _
                "HTTP response text: " & strHTTPResponseText
        End If
        
    End Select

Exit_Handler:
    On Error Resume Next
    ' clean up
    Set http = Nothing
    Exit Function

Err_Handler:
    Select Case Err.Number

    Case NOINTERNETAVAILABLE
        MsgBox "Connection to the internet cannot be made or " & _
            "Twilio website address is wrong"

    Case Else
        MsgBox "Error: " & Err.Number & "; Description: " & Err.Description
        Resume Exit_Handler

    End Select
    
End Function

The part you are likely to ask about is the PostData section - if so, I'll need to study it to remind myself what it all means!

I also used separate functions to log all messages (both text & voice)

I've attached images of the Inbox as well as the notification. The latter is on a 3 second transparancy fade in/fade out.
Click to expand...

Looks good. Was that a system tray notification (balloon text)?
If so did you manage to make it interactive i.e. click on it to do something?
 
ridders said:
This was the main part of the code though there lots else to it as well

Code: 
Function MakeCall(FromNumber As String, ToNumber As String, Message As String)
        
    On Error GoTo Err_Handler
    
    If IsLoaded("frmSendMessageTest") Then Forms!frmSendMessageTest.lblInfo.visible = False
    
    'get start time - needed here to calculate duration of call
    dblStart = CDbl(Now())
    
    ' setup the URL
    CallUrl = GetBaseURL() & "/2010-04-01/Accounts/" & GetMessageAccountSID() & "/Calls"
    
    ' setup the request and authorization
    Set http = New MSXML2.XMLHTTP60
    
    http.Open "POST", CallUrl, False, GetMessageAccountSID(), GetMessageAuthToken()
    http.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    
    PostData = "From=" & URLEncode(FromNumber) _
                & "&To=" & URLEncode(ToNumber) _
                & "&Url=http://twimlets.com/message?Message=" & URLEncode(URLEncode(Message, True))
    
    'Debug.Print postData
    
    ' send the POST data
    http.Send PostData
    
    ' optionally write out the response if you need to check if it worked
    'Debug.Print http.Status
    'Debug.Print http.responseText
    
    'calculate time taken
    dblEnd = CDbl(Now())
    strTimeTaken = Format(CDate(dblEnd - dblStart), "hh:mm:ss")
    lngDuration = CDbl(DateDiff("s", CDate(dblStart), CDate(dblEnd)) / 60) 'duration in minutes
        
    'Debug.Print dblStart; dblEnd; strTimeTaken; lngDuration
    
    strHTTPResponseText = Replace(http.responseText, "><", ">" & vbNewLine & "<")
    
    intHTTPStatus = http.Status
    'Debug.Print strHTTPResponseText
    
     'setup message response text
        
    Select Case intHTTPStatus
    
    Case 201  'success
        int201 = int201 + 1
        'Debug.Print strHTTPResponseText
    Case 400 'bad request
        int400 = int400 + 1
        strErrorText = "Message failed with HTTP status error " & http.Status & " " & http.statusText & vbNewLine & _
            "===================================================" & vbNewLine & _
            "HTTP response text: " & strHTTPResponseText
            
    Case 401 'unauthorised
        int401 = int401 + 1
        If strErrorText <> "" Then
            strErrorText = strErrorText & vbNewLine & vbNewLine & _
                "Message failed with HTTP status error " & http.Status & " " & http.statusText & vbNewLine & _
                "===================================================" & vbNewLine & _
                "HTTP response text: " & strHTTPResponseText
        Else
            strErrorText = "Message failed with HTTP status error " & http.Status & " " & http.statusText & vbNewLine & _
                "===================================================" & vbNewLine & _
                "HTTP response text: " & strHTTPResponseText
        End If
        
    Case Else 'other failure
        intOther = intOther + 1
        If strErrorText <> "" Then
            strErrorText = strErrorText & vbNewLine & vbNewLine & _
                "Message failed with HTTP status error " & http.Status & " " & http.statusText & vbNewLine & _
                "===================================================" & vbNewLine & _
                "HTTP response text: " & strHTTPResponseText
        Else
            strErrorText = "Message failed with HTTP status error " & http.Status & " " & http.statusText & vbNewLine & _
                "===================================================" & vbNewLine & _
                "HTTP response text: " & strHTTPResponseText
        End If
        
    End Select

Exit_Handler:
    On Error Resume Next
    ' clean up
    Set http = Nothing
    Exit Function

Err_Handler:
    Select Case Err.Number

    Case NOINTERNETAVAILABLE
        MsgBox "Connection to the internet cannot be made or " & _
            "Twilio website address is wrong"

    Case Else
        MsgBox "Error: " & Err.Number & "; Description: " & Err.Description
        Resume Exit_Handler

    End Select
    
End Function

The part you are likely to ask about is the PostData section - if so, I'll need to study it to remind myself what it all means!

I also used separate functions to log all messages (both text & voice)
Click to expand...

I see that you are using Twimlets. Does that require the user to set it up on the Twilio account or is that just the URL that you hit with the POST message?

Looks good. Was that a system tray notification (balloon text)?
If so did you manage to make it interactive i.e. click on it to do something?
Click to expand...

Thanks :)

No, it's just a form that I set to popup. I figured out where the bottom right of the Access screen is using:

PHP: 
 h = GetSystemMetrics32(1) * TwipsPerPixelY
    i = GetSystemMetrics32(0) * TwipsPerPixelX

And then I moved it within the Access screen. It's not the bottom of the Windows screen because I assume that when users are in the application they will see it anyway. I probably could have moved it to the bottom of the Windows screen if I want to. It's not interactive. I considered using a balloon but I don't think you can use a custom icon like I did and I like how mine looks.
 
Ok I'm just posting to say that I used your function and it worked out of the box. That's brilliant!

Sooo....how do you pick up the call log? is it the same GET as to the SMS but to the /Calls URL?

I see that Twilio has faxing as well. Have you seen code that can fax an Access report?
 
shadow9449 said:
1. Ok I'm just posting to say that I used your function and it worked out of the box. That's brilliant!

2. Sooo....how do you pick up the call log? is it the same GET as to the SMS but to the /Calls URL?

3. I see that Twilio has faxing as well. Have you seen code that can fax an Access report?
Click to expand...

1. Excellent!!!

2. Its very similar. Easy enough to modify the SMS version for use with calls
BUT the standard call log only gives the details of number, date/time/duration.
To get the details of the message, you either need to get a transcription or a recording. Neither s difficult to do if you read the API examples but there are additional costs involved. Best to discourage voice messages if possible

3. Faxing!!!! I thought we were moving forward not reverting to old technology! Why on earth would you want to fax a report? Save as PDF and email as an attachment

I see that you are using Twimlets. Does that require the user to set it up on the Twilio account or is that just the URL that you hit with the POST message?
Click to expand...

I'm not trying to be awkward but I coded all of this over a year ago. I really can't remember
Once again, the answers must be somewhere in the API documentation
 
ridders said:
3. Faxing!!!! I thought we were moving forward not reverting to old technology! Why on earth would you want to fax a report? Save as PDF and email as an attachment
Click to expand...

I couldn't agree more but there are MANY offices, especially in the medical industry, who are stuck in the 80s and use faxing far more than email.

I'm not trying to be awkward but I coded all of this over a year ago. I really can't remember
Once again, the answers must be somewhere in the API documentation
Click to expand...

No problem. The answer is actually in the POST statement and on the blog. It's actually a simple Twimlet that converts a URL.
 
shadow9449 said:
I couldn't agree more but there are MANY offices, especially in the medical industry, who are stuck in the 80s and use faxing far more than email.
Click to expand...

A fax printer driver is supplied with Windows. Why not use that?
Around 20 years ago I used it regularly .... before email came along.
 
ridders said:
A fax printer driver is supplied with Windows. Why not use that?
Around 20 years ago I used it regularly .... before email came along.
Click to expand...

Do you need a specific type of phone line or modem or does it just use the internet to send faxes? I don't know a lot about the topic.
 
You must log in or register to reply here.

Similar threads

isladogs
Access Europe User Group - Wed 3 Dec: Using Twilio for texting from Access (Adolph Dupré)
Replies
1
Views
446
isladogs
isladogs
N
How to send filtered transactions in the MS Access Combo box with one Click
Replies
15
Views
852
Mark_
M
isladogs
Access Europe User Group - Wed 5 Nov: Using Power Automate with Office Apps (Ynte Jan Kuindersma)
Replies
2
Views
566
isladogs
isladogs
N
Solved I want to be able to send a few emails in access 2016 using gmail but am having challenges
Replies
7
Views
993
nector
N
B
.send - anyway to continue processing & not wait. Using CDO. schemas to send emails
Replies
18
Views
2,438
AngelSpeaks
AngelSpeaks

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom