FJ Software

Happy new year to all the geeks!

I herein revive an old subject for which I never ended.

The objective is to automatically send SMSs to multiple recipients based on an excel sheet containing a list of phone numbers and individualized messages.

Let us say that
column 1 contains the phone numbers (from cell A5)
column 2 contains the message to be sent (from B5)

We know (from the following topic https://www.fjsoft.at/forum/viewtopic.php?t=373 link that the appropriate syntax required for MPE is below

"C:\Program Files (x86)\MyPhoneExplorer\MyPhoneExplorer.exe", action=sendmessage savetosent=1 number=+33611236962 text="This is the message to be sent"

and when this command is sent from a DOS windows, it works perfectly.

My concern is that I cannot succeed in sending such a line from an Excel macro

The macro below (between >>>> and <<<<<) is almost operational, but
I am puzzled when finding the appropriate syntax for sending line itself

>>>>>>>>>>
Sub Send_SMS_Via_MPE()
'
'
' this macro should send SMS based
' on the table starting at A5
' given that A5 is the phone number and B5 the message to send
'
Dim a, b As String
Dim x As Integer
For x = 5 To Range("a32000").End(xlUp).Row 'We guess this is enough

a = Cells(x, 1)
b = Cells(x, 2)

' the goal is to elaborate below the command to send

'''''Something like : shell ("C:\Program Files (x86)\MyPhoneExplorer\MyPhoneExplorer.exe, action=sendmessage savetosent=1 number="&a&" text= & b &"))

'Now we wait a little before moving to next line
Application.Wait (Now + TimeValue("00:00:05"))

Next
End Sub
<<<<<<<<<<

Could a VBA guru give me a hand on this point?

Many thanks

Loic

Code: Alles auswählen

Dim txt as String
Dim num as String

txt = "Hello"
num = "0123456789"

RunMPE("action=sendmessage number=" & num & " text=""" & txt & """")

Function RunMPE(Arguments) 
  set shell = CreateObject("WScript.Shell") 
  shell.run "myphoneexplorer " & Arguments ,3 
End Function

This should do the job

Hi FJ,
thanks for this quick answer

Unfortunately, I cannot make it work, probably uncorrectly wrapped within my macro.

I was naively hoping someone to merely correct the macro I provided in my
post with the correct syntax in the line beginning with : ''''Something like"

Because I am not able to wrap the code you provide in an appropriate macro

Should I put it in a macro or somewhere else?

Hi Franz Josef,
Hi everybody,

Please do not continue a possible search, I think I have found the solution.

For all intents and purposes please find below a possible script,

>>>>>>>>>>>>>>>>
Sub Send_SMS_Via_MPE()
'
' this macro should send SMS based
' on the table starting at A5
' given that A5 is the phone number and B5 the message to send
'
Dim CheminProgramme, Number, Message, Command As String
Dim x As Integer
Dim PID As Long

FullProgramName = "C:\Program Files (x86)\MyPhoneExplorer\MyPhoneExplorer.exe"

For x = 5 To Range("a32000").End(xlUp).Row 'We guess this is enough

Number = Cells(x, 1) ' Get the phone number from column A
Message = Cells(x, 2) ' Get the message string from column B
Command = Chr(34) & FullProgramName & Chr(34) & " " & "action=sendmessage savetosent=1 " + "number=" + Number + " text=" + Chr(34) + Message + Chr(34)

' Execute the command
On Error Resume Next
PID = Shell(Command, vbNormalFocus)
If Err.Number <> 0 Then
MsgBox "Error while executing the program : " & Err.Description, vbExclamation, "Erreur"
End If
On Error GoTo 0

' Now wait a little before moving to next line
Application.Wait (Now + TimeValue("00:00:05"))

Next
End Sub

<<<<<<<<<<<<<<<<<<<<<<<<

Thanks again

Regards

I have tried this but can't get it to work.
Does this need some changes in the init file as well or is it just the macro?

@JensF
In principle, the code is OK, but contains small errors.
The prerequisite in both codes is that the numbers are in column A and the SMS texts are in column B as of line 5
Try it with the following code:

Code: Alles auswählen

Sub Send_SMS_Via_MPE()
  Dim Number, Message, Command As String
  Dim x As Long
  Dim PID As Long, yLast As Long
  Dim PrgPath As String

  PrgPath = "C:\Program Files (x86)\MyPhoneExplorer\MyPhoneExplorer.exe"
  yLast = ActiveSheet.Cells(65536, 1).End(xlUp).Row
  For x = 5 To yLast
    Number = Cells(x, 1)
    Message = Cells(x, 2)
    Command = Chr(34) & PrgPath & Chr(34) & " " & "action=sendmessage savetosent=1 " + "number=" + Number + " text=" + Chr(34) + Message + Chr(34)

    On Error Resume Next
    PID = shell(Command, vbNormalFocus)
    
    If Err.Number <> 0 Then
     MsgBox "Error while executing the program : " & Err.Description, vbExclamation, "Error"
    End If
    On Error GoTo 0

    Application.Wait (Now + TimeValue("00:00:05"))
  Next

If it still doesn't work, try to describe the problem in more detail!

Just for completeness, this Excel VBA Script exports the table into a xml-batch-file and advices MyPhoneExplorer to send the messages.

Code: Alles auswählen

Sub Send_SMS_Via_MPE()
    ' Declare variables
    Dim FullProgramName As String
    Dim BatchPath As String
    Dim Number As String
    Dim Message As String
    Dim Command As String
    Dim PID As Long
    Dim LastRow As Long
    Dim x As Long

    ' Set the path to MyPhoneExplorer executable
    FullProgramName = "C:\Program Files (x86)\MyPhoneExplorer\MyPhoneExplorer.exe"
    BatchPath = "Z:\test.xml"

    ' Determine the last row with data in column A
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row

    Open BatchPath For Output As #1
    
    Print #1, "<?xml version=""1.0"" encoding=""ISO-8859-1"" standalone=""yes""?>"
    Print #1, "<batch>"
    
    For x = 2 To LastRow
        Number = Trim(Cells(x, 2).Value)
        Message = Trim(Cells(x, 4).Value)
        
        If Len(Number) > 0 And Len(Message) > 0 Then
            Print #1, "<message>"
            Print #1, "  <recipient>" & EncodeValue(Number) & "</recipient>"
            Print #1, "  <text>" & EncodeValue(Message) & "</text>"
            Print #1, "</message>"
        Else
            MsgBox "Row " & x & " has missing data. Skipping.", vbExclamation, "Missing Data"
        End If
    Next x
    
    Print #1, "</batch>"
    
    Close #1

    Command = Chr(34) & FullProgramName & Chr(34) & " action=sendmessage batchfile=" & Chr(34) & BatchPath & Chr(34)
    
    ' Execute the command
    On Error Resume Next
    PID = Shell(Command, vbNormalFocus)
    If Err.Number <> 0 Then
        MsgBox "Error while starting batch: " & Err.Description, vbExclamation, "Error"
        Err.Clear
    End If
    On Error GoTo 0
End Sub

FJ Software

Sending SMSs on batch through MPE and Excel

Sending SMSs on batch through MPE and Excel