Using Twilio with Access to send & receive text & voice messages (1 Viewer)

isladogs · Feb 19, 2018

I think its my bad!!! I've corrected it for anyone else who reads this in the future

This is my own code for that line (with ... replacing my account number / phone number

Code:

https://api.twilio.com/2010-04-01/Accounts/AC........./Messages.csv?&To=44.......&DateSent>=2017-02-19

Using .csv means my output is returned as CSV files so no need to parse further

shadow9449 · Feb 19, 2018

ridders said:
I think its my bad!!! I've corrected it for anyone else who reads this in the future

This is my own code for that line (with ... replacing my account number / phone number

Code:

https://api.twilio.com/2010-04-01/Accounts/AC........./Messages.csv?&To=44.......&DateSent>=2017-02-19

Using .csv means my output is returned as CSV files so no need to parse further

So, some questions:

1) How do you construct the URL to filter dates? I see you do it there but I don't know the syntax. I suppose the best way to do it is to keep a stamp in the database for the latest download date/time so i can tell it to only import transactions since then.

2) Have you been able to convert the dates in the received file to an Access date/time format?

3) It seems that there's a delay in between the time the messages are sent/received and when they show up in the download. I see they are hitting the download but not immediately. Have you figured out the lag?

Thank you

isladogs · Feb 19, 2018

shadow9449 said:
So, some questions:

1) How do you construct the URL to filter dates? I see you do it there but I don't know the syntax. I suppose the best way to do it is to keep a stamp in the database for the latest download date/time so i can tell it to only import transactions since then.

2) Have you been able to convert the dates in the received file to an Access date/time format?

3) It seems that there's a delay in between the time the messages are sent/received and when they show up in the download. I see they are hitting the download but not immediately. Have you figured out the lag?

Thank you

I haven't used the code for some months so I can't answer all questions

1. I have a function GetLastReceivedSMSMessageCheck which checks the last received message date/time & only imports messages on/after that date/time. I then filter to remove any arriving earlier that same date to ensure no duplicates

Code:

    DoCmd.RunSQL "DELETE tblReceivedTextMessages.*, tblReceivedTextMessages.MessageDateTime" & _
        " FROM tblReceivedTextMessages" & _
        " WHERE ((Format(tblReceivedTextMessages.MessageDateTime,'mm/dd/yyyy hh:nn:ss')<=Format(GetLastReceivedSMSMessageCheck(),'mm/dd/yyyy hh:nn:ss')));"

2. Yes - I don't recall this being an issue - see above

3. As I haven't used the code recently, I can't remember anything about a lag being an issue - suggest you ask Twilio

Also I have this code in the module declaration section - suggest you add it

Code:

Const NOINTERNETAVAILABLE = -2147012889

Dim MessageUrl, CallUrl, PostData, strHTTPResponseText As String
Dim http As MSXML2.XMLHTTP60

shadow9449 · Feb 19, 2018

I am not understanding how you convert from their format to a Date/Time.

When it's imported, what format does it come into the table as? I'm importing it as Text and then trying to convert it to a date/time but really no luck. I've tried using DateSerial and all kinds of string functions to pick out the day/date/year but that doesn't include the time stamp.

Using the format function like this: Format([datesent],"mm/dd/yyyy hh:nn:ss")

doesn't work.

In short: how can I convert: Mon, 19 Feb 2018 20:14:47 +0000 to a date/Time?

Thank you

isladogs · Feb 20, 2018

I haven't looked how I did it before but the trick is just to strip out the leading and end sections:

Two different solutions below which give the same answers

Code:

Sub TestDateTime()
    Dim strDate As String, N As Integer
  '  strDate = "Mon, 19 Feb 2018 20:14:47 +0000"
  '  strDate = "Wed, 7 Feb 2018 08:15:49 +0000"
   
   'strip out the leading section before the comma & the end section after the + sign
   'Both the next 2 lines do the same thing
  '  strDate = Mid(strDate, InStr(strDate, ",") + 2, InStr(strDate, "+") - InStr(strDate, ",") - 2)
    strDate = Trim(Mid(strDate, InStr(strDate, ",") + 1, InStr(strDate, "+") - (InStr(strDate, ",") + 1)))
    Debug.Print Format(strDate, "mm/dd/yyyy hh:nn:ss")
End Sub

Results:
Mon, 19 Feb 2018 20:14:47 +0000 gives 02/19/2018 20:14:47
Wed, 7 Feb 2018 08:15:49 +0000 gives 02/07/2018 08:15:49

Just use one of the formulas in your VBA code or a query

EDIT: I've just been onto my Twilio account & grabbed a message log from Feb 2017 as a CSV download.
This is an edited screenshot showing the times

As you can see, the format is slightly easier to manage using CSV output

shadow9449 · Feb 21, 2018

String to date conversion works perfectly. Thanks once again!

shadow9449 · Mar 14, 2018

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.

shadow9449 · Mar 29, 2018

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

shadow9449 · Mar 29, 2018

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.

Gasman · Mar 29, 2018

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

shadow9449 · Mar 29, 2018

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!

Gasman · Mar 29, 2018

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?

shadow9449 · Mar 29, 2018

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?

Unfortunately, that didn't work

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

Thank you

isladogs · Mar 29, 2018

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

shadow9449 · Mar 29, 2018

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.

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.

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

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

isladogs · Mar 30, 2018

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.

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.

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

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.

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.

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

shadow9449 · Mar 30, 2018

ridders said:
The only documentation found for Access was the article I told you about previously

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

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

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

isladogs · Mar 30, 2018

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

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.

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?

shadow9449 · Mar 30, 2018

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)

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?

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.

shadow9449 · Mar 30, 2018

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?

Using Twilio with Access to send & receive text & voice messages (1 Viewer)

MVP / VIP

Registered User.

MVP / VIP

Registered User.

MVP / VIP

Attachments

Registered User.

Registered User.

Registered User.

Registered User.

Enthusiastic Amateur

Registered User.

Enthusiastic Amateur

Registered User.

MVP / VIP

Registered User.

MVP / VIP

Attachments

Registered User.

Attachments

MVP / VIP

Registered User.

Registered User.

Similar threads

Users who are viewing this thread