You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Send SMS to Contacts in Excel through Automator and AppleScript

I'm trying to figure out how to send SMS texts to an Excel contact list. Others on this forum have discussed this topic in the recent past, however, the code presented does not work for me. Wondering if anyone has a proven solution.


For example, the solution Camelot offered on 12/11/2020:


on get_member(id)

tell application "Microsoft Excel"

-- assumes column 1 for the name and column 2 for the phone number

set _name to value of cell ("A" & id)

set _number to value of cell ("B" & id)

return {name:_name, number:_number}

end tell

end get_member



on send_message_to(member)

tell application "Messages"

try

send "Hello, " & name of member & ", This is text" to participant (number of member) of account "SMS"

end try

end tell

end send_message_to



set i to 2

repeat

-- go get the data for the member

set _member to get_member(i)

if name of _member = "" then

-- break on the first empty/missing name

exit repeat

end if

send_message_to(_member)

-- increment the counter

set i to i + 1

end repeat



Thanks in advance.

Posted on Jul 7, 2021 1:54 PM

Reply
9 replies

Jul 7, 2021 4:46 PM in response to Camelot

Many thanks for the reply - My Excel setup is as you describe. Column A is names, no header. Column B is phone numbers, no header. The file precedes the script in Automator.


Here's a screenshot – note "value" is highlighted. Also, in the second section, should the referenced variable be "_member" rather than "member"?


Thanks for your help!


Jul 7, 2021 4:25 PM in response to Prince Hal

I vaguely recall writing this script.


As I recall, as is often the case, the script makes some assumptions. Most specifically it assumes two columns in the spreadsheet, with names in column A and numbers in column B.


If your spreadsheet is different from this you'd need to amend the script to reference the relevant columns.


Secondly, it assumes you have an account setup in Messages.app with the name 'SMS'. This isn't as obvious. If I recall, the original discussion that script was attached to showed how to find the name of configured accounts.


if neither of these are the issue, please explain the failure state. AppleScript is usually pretty good about reporting errors (even if you don't understand the error message itself). Posting that could help identify the problem.


Jul 8, 2021 5:50 AM in response to Prince Hal

I think I've managed to piece together something that works.


Reflecting on (1) Camelot's work + (2) this from StackOverflow, I've come up with:


on run {input, parameters}
	
	set phoneCol to "B"
	set messageCol to "C"
	set startRow to 1
	set endRow to 3
	
	set xlsFilePath to (path to desktop as text) & "texttest.xlsx"
	tell application "Microsoft Excel" to open file xlsFilePath
	
	
	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 
			keystroke targetMessage -- type some message
			key code 36 -- press Enter to send
		end tell
		
		delay 2
	end repeat
	
	
	return input
end run


This appears to work for New messages as well. Perhaps someone knows how to set endRow automatically, so the code needn't be adjusted as the contact roster changes?



Jul 8, 2021 9:43 AM in response to Prince Hal

I think I've got it solved.


First, the excel file, designed as follows, with no column headers:

Column A: First Names (not used in this example)

Column B: Phone number, digits only, no symbols

Column C: Text message


Cell D1 contains the following: =COUNT(B:B)


Second, the completed code:


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 
			keystroke targetMessage -- type some message
			key code 36 -- press Enter to send
		end tell
		
		delay 2
	end repeat
	
	
	return input
end run


The Excel file rests on the Desktop. And that's it. Should work.



Jul 8, 2021 9:49 AM in response to Prince Hal

> Excel file is closed


So how do you expect the script to find your data?


I mean, the script may be somewhat smart, but it's not psychic.

I believe the original post stated that it looks at the currently open spreadsheet. If it's not open you will get this error. You need to add additional logic to make sure the spreadsheet exists before trying to process it.

Jul 8, 2021 9:53 AM in response to Prince Hal

That's not really my code, and that's OK if it works for you, but I would always avoid using UI scripting for this kind of thing. It's more cumbersome and prone to error, but if it works for you, go for it.


As for detecting the end, I would just check if the number field is empty and exit the loop. That way you don't need a calculated cell, and it makes the script more robust, and easier to work with other sources, too (any table of numbers and messages would work, not just an Excel sheet.

Send SMS to Contacts in Excel through Automator and AppleScript

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.