I HAVE A VBA CODE BUT ITS NOT COMUNICATING WITH THE BULKSMS PROVIDE
Private Sub SMS_Click()
Dim wb As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lastRow As Long, i As Long
Dim HTTPReq As Object
Dim successCount As Long
Dim Message As String
Dim APIToken As String
Dim APIEndpoint As String
' Set API token and endpoint
APIToken = "137|gkEBLgZmliFUSQy1FrdwQYHx8qXOWmQ7WRuT4llO "
APIEndpoint = "https://bulksms.talksasa.com/api/v3/sms/send"
' Initialize HTTP request object
Set HTTPReq = CreateObject("MSXML2.XMLHTTP")
' Initialize success count
successCount = 0
' Set the workbook and worksheets
Set wb = ThisWorkbook
Set ws1 = wb.Sheets("StudentsBiodata")
Set ws2 = wb.Sheets(Me.ComboBox7.value & "_" & Me.ComboBox9.value & "_" & Me.ComboBox8.value)
' Iterate through each row with data in column B starting from row 6 in ws2
lastRow = ws2.Cells(ws2.Rows.Count, "B").End(xlUp).Row
For i = 6 To lastRow
' Check if column B is not empty
If Not IsEmpty(ws2.Cells(i, "B")) Then
' Construct the message body for each row
Message = "Dear Parent," & vbCrLf & "Results for " & Me.ComboBox9.value & "_" & Me.ComboBox8.value & ":" & vbCrLf & _
" NAME - " & ws2.Cells(i, "C").value & vbCrLf & _
" ENG - " & ws2.Cells(i, "D").value & " " & ws2.Cells(i, "E").value & vbCrLf & _
" KISW - " & ws2.Cells(i, "F").value & " " & ws2.Cells(i, "G").value & vbCrLf & _
" MAT - " & ws2.Cells(i, "H").value & " " & ws2.Cells(i, "I").value & vbCrLf & _
" BIO - " & ws2.Cells(i, "J").value & " " & ws2.Cells(i, "K").value & vbCrLf & _
" PHY - " & ws2.Cells(i, "L").value & " " & ws2.Cells(i, "M").value & vbCrLf & _
" CHEM - " & ws2.Cells(i, "N").value & " " & ws2.Cells(i, "O").value & vbCrLf & _
" HIST - " & ws2.Cells(i, "P").value & " " & ws2.Cells(i, "Q").value & vbCrLf & _
" GEO - " & ws2.Cells(i, "R").value & " " & ws2.Cells(i, "S").value & vbCrLf & _
" C.R.E - " & ws2.Cells(i, "T").value & " " & ws2.Cells(i, "U").value & vbCrLf & _
" H/SCI - " & ws2.Cells(i, "V").value & " " & ws2.Cells(i, "W").value & vbCrLf & _
" AGRI - " & ws2.Cells(i, "Z").value & " " & ws2.Cells(i, "AA").value & vbCrLf & _
" COMP - " & ws2.Cells(i, "AB").value & " " & ws2.Cells(i, "AC").value & vbCrLf & _
" B/ST - " & ws2.Cells(i, "AF").value & " " & ws2.Cells(i, "AG").value & vbCrLf & _
" MEANGRADE - " & ws2.Cells(i, "AK").value & vbCrLf & _
" POS - " & ws2.Cells(i, "A").value
' Send SMS request
HTTPReq.Open "POST", APIEndpoint, False
HTTPReq.setRequestHeader "Authorization", "Bearer " & APIToken
HTTPReq.setRequestHeader "Content-Type", "application/json"
HTTPReq.setRequestHeader "Accept", "application/json"
HTTPReq.Send "{""recipient"":""" & ws2.Cells(i, "AN").value & """," & _
"""sender_id"":""TALKSASA""," & _
"""type"":""plain""," & _
"""message"":""" & Message & """}"
' Introduce a delay of 1 second between SMS requests
Application.Wait Now + TimeValue("00:00:01")
' Check response status
If HTTPReq.Status = 200 Then
successCount = successCount + 1
Else
MsgBox "Failed to send SMS to ", vbExclamation
End If
Else
MsgBox "Phone number not found for row " & i, vbExclamation
End If
' Create a new Word document
Dim WordApp As Object
Set WordApp = CreateObject("Word.Application")
WordApp.Visible = False
Dim WordDoc As Object
Set WordDoc = WordApp.Documents.Add
' Add the message to the Word document
WordDoc.Content.Text = Message
' Save the Word document as PDF
Dim FilePath As String
FilePath = "C:\Users\USER 14\Documents\New folder (2)\" & ws2.Cells(i, "C").value & ".pdf"
' Specify the file path where you want to save the PDF
WordDoc.ExportAsFixedFormat OutputFileName:=FilePath, ExportFormat:=17 ' 17 represents PDF format
' Close the Word document
WordDoc.Close False
' Quit Word application
WordApp.Quit
Next i
' Display success message
MsgBox "SMS Sent successfully to " & successCount & " recipients!", vbInformation
' Assume Message contains the text you want to print as a PDF
End Sub GIVES SUCCES MESSAGE BUT WONT SEND THE SMS