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

[isladogs]

[SOLVED] Using Twilio with Access to send & receive text & voice messages

I have successfully added VBA code to a database to send SMS & voice messages using Twilio. I have setup detailed logging procedures for all sent messages & it works well.

Next I want to add the ability to record details of all received messages from the Twilio account in Access. The website does provide the ability to export call & SMS logs as CSV files so I could if necessary import these manually.

However there must be a way of automating this using VBA
Does anyone have experience of doing this (or something similar from another website?

Any ideas would be appreciated

 

[Uncle Gizmo]

I have occasionally used some of the techniques from this 1997 (20-year-old online MS Access book) I haven't actually use the technique I am directing you to see this link:-

http://c85.cemi.rssi.ru/access/books/A97ExSol/index13.htm

and then navigate to the "Listing" shown below.

Listing 13.6 shows the code that drives this strategy.
Listing 13.6 AES_Frm1.Mdb-Loading and Refreshing a Combo Box from a Disk File

I think the basic idea will get you started, and will hopefully Lead you to a solution.

Sent from my SM-G925F using Tapatalk

 

[Uncle Gizmo]

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

Twilio has an API
https://www.twilio.com/docs/api/rest/response

That returns XML...

Access can import XML...
https://arstechnica.com/civis/viewtopic.php?f=20&t=529257
(I don't think that's quite what you need/want...)

See also:-
https://bytes.com/topic/access/answers/953040-import-xml-file-ms-access-table


Sent from my SM-G925F using Tapatalk

 

[Uncle Gizmo]

I suspect for a completely automated system you would be better off getting Json from a URL provided by Twilio.

I haven't attempted to pull Json in to Access yet. It's on my bucket list. I know Bruce McPherson has done Excel VBA - Json integration and I think that's the way to do it in MS Access, following Bruce's lead.

Sent from my SM-G925F using Tapatalk

 

[Uncle Gizmo]

I haven't attempted to pull Json in to Access yet

Actually, I have dabbled, so far unsuccessfully...



Sent from my SM-G925F using Tapatalk

 

[isladogs]

Hi Uncle Gizmo
Thanks for multiple replies
I don't think the first one is relevant - the log files are easily available as csv files on the website
Obviously I know how to import these but originally wondered if there was a way of automating the export from the website at regular intervals. e.g. code to use with a scheduled task.

I've done very little tinkering with XML but that's certainly worth a try - I've added the XML reference library & will look into this. Hopefully the Twilio REST API can be used to extract the data

Excuse my ignorance but I have no idea what JSON is or how to use it. Do you have a link for Bruce McPherson?

Many thanks

 

[CJ_London]

wondered if there was a way of automating the export from the website at regular intervals. e.g. code to use with a scheduled task.

think you mean automate the import from the website - don't think the website can be automated to send you reports on a regular basis, you need to go fetch them.

That should be straight forward, within Access use a form timer event or within windows, create a 'fetch' routine to run off the windows events manager (routine might be an access db with just an autoexec function to call code to do the fetch, then close)

 

[isladogs]

Hi CJ
Yes of course I meant automate the import from the website
Not sure how to create the fetch routine however

 

[Uncle Gizmo]

think you mean automate the import from the website

Oops Yes! I've Corrected it.... Thanks Chris

Ah! it wasn't me this time! Oops again!

 

[CJ_London]

Not sure how to create the fetch routine however

If you are paying for responses via Twilio they should provide an API for you to receive them, you'll probably need to talk to them directly or use a twilio forum for specifics

I would expect it to work in much the same way as your 'send' code

check this link out

https://www.twilio.com/docs/api/twiml/sms/twilio_request

 

[isladogs]

Hi CJ
I'd already tried asking this question at Twilio but the first attempt wasn't helpful.
I've now tried asking the question at the webpage you listed.
Will report back on any progress

In the meantime, I would like to follow up use of XML or JSON(?) as Uncle gizmo suggested

Thanks

 

[sxschech]

Here is a link to a free tool that can convert json to excel files. Been using it for couple months and works pretty well, if you don't mind doing copy paste of the json string. They do have automation tools, but there is a cost for that and haven't tried them, only used the free online version. Down the road if workload increases, probably will look into automation.

https://json-csv.com/

 

[isladogs]

Hi
Just a note to say I've now got this working as follows.
1. Used HTTP GET together with the Twilio API to download the message logs as a CSV file. If preferred, this can instead be done as XML or JSON
2. Import the downloaded file into Access in the usual way.
3. Append the new records to the log file in Access

It works well and only takes a few seconds so I've added it to a Form Timer event to run every 5 minutes

The downloaded data includes senders phone number, message text & cost for text messages
For voice messages, it includes phone number, cost & duration but annoyingly not a transcript of the message itself! This is also true for the voice logs on the Twilio website
& renders this feature almost useless. I know you can record incoming messages so I've contacted Twilio to ask for a solution

 

[shadow9449]

Hi
Just a note to say I've now got this working as follows.
1. Used HTTP GET together with the Twilio API to download the message logs as a CSV file. If preferred, this can instead be done as XML or JSON
2. Import the downloaded file into Access in the usual way.
3. Append the new records to the log file in Access

It works well and only takes a few seconds so I've added it to a Form Timer event to run every 5 minutes

The downloaded data includes senders phone number, message text & cost for text messages

I am interested in using Twilio. I found a blog post with code to send messages so setting up an account and making a form that sends messages took all of 20 minutes. However, I have no understanding of how to use GET as you described to download the responses. Would you mind posting your code or an example, please?

Thanks in advance!

 

[isladogs]

I am interested in using Twilio. I found a blog post with code to send messages so setting up an account and making a form that sends messages took all of 20 minutes. However, I have no understanding of how to use GET as you described to download the responses. Would you mind posting your code or an example, please?

Thanks in advance!

Have a look at the links in this post, particularly the final one:
https://www.access-programmers.co.uk/forums/showpost.php?p=1547444&postcount=7

As for HTTP GET, this type of code is available in many places & can easily be adapted. For example, see my currency exchange rates example database which includes the use of HTTP GET with JSON files
https://www.access-programmers.co.uk/forums/showthread.php?t=297121

 

[shadow9449]

Have a look at the links in this post, particularly the final one:
https://www.access-programmers.co.uk/forums/showpost.php?p=1547444&postcount=7

That's actually the post that got me going with being able to send messages, but I didn't see anything there for receiving.

As for HTTP GET, this type of code is available in many places & can easily be adapted. For example, see my currency exchange rates example database which includes the use of HTTP GET with JSON files
https://www.access-programmers.co.uk/forums/showthread.php?t=297121

My problem is that I don't know how to adapt the syntax for Twilio, specify the download format, specify where it gets downloaded to and so on. I really don't have much of a starting point. Like most APIs or SDKs, the languages accommodated are Python, Ruby, Java, .NET and so on. For the past 10 years there's little accommodation for VBA, so I don't even know where to look.

The Twilio page on the response part of the API doesn't show how to make the GET call, it seems to just show the formats of the responses. I did searching online for receiving messages with Twilio using Access or VBA and they all showed about sending but nothing that I could find explained receiving.

Thank you

 

[isladogs]

That's also what got me started.
Using HTTP GET is very similar though it took me some time to get the fine details correct.

Below is the important part of a procedure I use for the purpose
A similar procedure works for received calls

You need to add the VBA reference Microsoft XML v6.0

Use your own Twilio account details for the parts in BOLD RED type

I used CSV files. Change .csv to .xml or .json as appropriate

Function GetReceivedSMSMessages()

'import received SMS message log from Twilio website

On Error GoTo Err_Handler

    DoCmd.SetWarnings False
    
    StatusBar "Importing new text messages . . ."
    DoCmd.Hourglass True

    Dim MessageURL As String, BaseURL As String

    BaseURL= "https://api.twilio.com"

    'setup the URL
    MessageUrl = BaseURL & "/2010-04-01/Accounts/" & [COLOR="DarkRed"][B]ACCOUNTSID[/B][/COLOR] & "/SMS/Messages"
    
    'setup the request and authorization
    Set http = New MSXML2.XMLHTTP60
    
    http.Open "GET", MessageUrl, False, [COLOR="darkred"][B]ACCOUNTSID, AUTHTOKEN[/B][/COLOR]
    
    http.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    http.Send ""
    
   'Debug.Print http.responseText
   
   'populate a CSV file with downloaded data; overwrite existing file (if any)
    Dim stm As Object
    Set stm = CreateObject("ADODB.Stream")
    stm.Type = 2  ' acTypeText
    stm.Open

    stm.WriteText http.responseText
    stm.SaveToFile Application.CurrentProject.Path & "\ImportedTextMessages.csv", 2   'adSaveCreateOverWrite
    
    stm.Close
    Set stm = Nothing
    Set http = Nothing
       
  
   'import messages data to Access table
   'create your own import specification manually then use below when you know it works
    DoCmd.TransferText acImportDelim, "ImportedTextMessagesImportSpecification", _
        "tblImportedTextMessages", Application.CurrentProject.Path & "\ImportedTextMessages.csv"
        
  'add your own code here to do any other tasks as necessary
   .......
 
   
EndRoutine:
    StatusBar ""
    DoEvents
    DoCmd.Hourglass False

    'delete the CSV file
    Kill Application.CurrentProject.Path & "\ImportedTextMessages.csv"
    
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 & " in GetReceivedSMSMessages procedure :" & vbNewLine & _
        "Description: " & Err.Description
        Resume Exit_Handler

    End Select
    
End Function

 

[shadow9449]

I have NO idea how you figured that out but I really appreciate it!

The message I'm getting back is an XML message that the AccountSid or AuthToken was incorrect. I am using the same info as I used for the send! I am using a trial account which is pretty limited. Do you happen to know if perhaps this is not supported unless you have a real account?

From what I'm understanding, the GET command builds a URL and sends it off so it can receive a response. Is there a way to get a msgbox or debug.print to see exactly what it's sending so I can make sure it's sending the right info?

Thank you

 

[isladogs]

I can't remember whether trial accounts can receive messages but I suspect not. Setting up a real account is inexpensive and gets rid of Twilio 'branding' on messges

Suggest you email Twilio support. I found them very helpful.

 

[shadow9449]

Ok my bad!

If you look at the code, there is no / between the ACCOUNTSID and "SMS", so it sent it off as one string! I should have scrutinized that more carefully.

Anyway, it worked and a HUGE thank you for the assistance!

Another tip: it actually comes back in XML and I don't mind writing a parser if necessary but I appended ".csv" to the URL to get it into rows in the CSV file.