AppleScript Error on Upgrade to Monterey – Mass SMS Text Application

Question

Hoping someone can help with this – I'm running macOS Monterey on an Apple M1 Pro laptop. My AppleScript to send out batch text messages, stored on an Excel file, delivered through the Messages app is now not working – it worked fine on my old laptop operating under Catalina.

The problem appears to be in delivering the phone number ("targetBuddyPhone") into the proper location in the Messages app. Instead, the text message ("targetMessage") is being dropped into the recipient location in the app. Does anyone have any ideas on possible solutions?

Thanks in advance.

on run {input, parameters}
    
    set phoneCol to "B"
    set messageCol to "C"
    set startRow to 1
    set counter to "D"
    
    set xlsFilePath to (path to desktop as text) & "texttest.xlsx"
    tell application "Microsoft Excel" to open file xlsFilePath
    tell application "Microsoft Excel"
        set endRow to value of cell (counter & startRow) as number
    end tell
    repeat with thisRow from startRow to endRow
        tell application "Microsoft Excel"
            set targetBuddyPhone to string value of cell (phoneCol & thisRow) as string
            set targetMessage to value of cell (messageCol & thisRow) as string
        end tell
        
        activate application "Messages"
        tell application "System Events" to tell process "Messages"
            key code 45 using command down -- press Command + N to start a new window
            keystroke targetBuddyPhone -- input the phone number
            key code 36 -- press Enter to focus on the message area 
            delay 3
            keystroke targetMessage -- type some message
            key code 36 -- press Enter to send
        end tell
        
        delay 6
    end repeat
    
    
    return input
end run

Answer 1

I figured out the solution. To my GUI-based approach, I inserted delays into the script, which solved the problems.

on run {input, parameters}

set phoneCol to "B"
set messageCol to "C"
set startRow to 1
set counter to "D"

set xlsFilePath to (path to desktop as text) & "texttest.xlsx"
tell application "Microsoft Excel" to open file xlsFilePath
tell application "Microsoft Excel"
    set endRow to value of cell (counter & startRow) as number
end tell
repeat with thisRow from startRow to endRow
    tell application "Microsoft Excel"
        set targetBuddyPhone to string value of cell (phoneCol & thisRow) as string
        set targetMessage to value of cell (messageCol & thisRow) as string
    end tell
    
    activate application "Messages"
    tell application "System Events" to tell process "Messages"
        key code 45 using command down -- press Command + N to start a new window
        delay 3
        keystroke targetBuddyPhone -- input the phone number
        delay 3
        key code 36
        delay 3
        key code 36 -- press Enter to focus on the message area 
        delay 3
        keystroke targetMessage -- type some message
        delay 3
        key code 36 -- press Enter to send
    end tell
    
    delay 6
end repeat


return input
end run

To Robert's solution, I changed one line (set SMSService to ...) and this script now works properly.

on run {input, parameters}
set phoneCol to "B"
set messageCol to "C"
set startRow to 1
set counter to "D"

set xlsFilePath to (path to desktop as text) & "texttest.xlsx"
tell application "Microsoft Excel"
    open file xlsFilePath
    set endRow to value of cell (counter & startRow) as number
end tell
tell application "Messages"
    activate
    set SMSService to 1st account whose service type = SMS -- YOU NEED THIS SERVICE
end tell

repeat with thisRow from startRow to endRow
    tell application "Microsoft Excel"
        set targetBuddyPhone to string value of cell (phoneCol & thisRow) as string
        set targetMessage to value of cell (messageCol & thisRow) as string
    end tell
    tell application "Messages"
        set theBuddy to participant targetBuddyPhone of SMSService
        send targetMessage to theBuddy
    end tell
end repeat

return input
end run