Hello. I am trying to automate a marketing related email process where an apple script will send mails from a predefined email in Mail.App picking up email addresses from Numbers. Ideally it would be nice if it could mark email cells as ‘sent’ with a mild colour too. Somehow my basic script is not working. I am not familiar with Apple Script, just been using internet research to write this:
-- Set the starting time
set startTime to current date
set startTime to startTime + (1 * minutes)
-- Get the email data from the Numbers sheet
tell application "Numbers"
set emailData to value of range "A2:D" of sheet 1 of document "MailList"
end tell
-- Choose the email account to send from
set senderEmail to "email@myexample.com" -- Replace with the email address of the desired account
-- Iterate through the email data and send emails
set emailCounter to 0
repeat with emailRow in emailData
-- Check if the daily email limit has been reached
if emailCounter ≥ 45 then exit repeat
-- Get the email details
set recipient to item 1 of emailRow
set recipientName to item 2 of emailRow
set subject to item 3 of emailRow
set message to item 4 of emailRow
-- Delay until the next sending time
repeat until (current date) ≥ startTime
delay 1
end repeat
-- Compose the email message
set emailContent to "Dear " & recipientName & "," & return & return & message
-- Send the email
tell application "Mail"
set newEmail to make new outgoing message with properties {subject:subject, content:emailContent}
tell newEmail
make new to recipient with properties {address:recipient}
send
end tell
end tell
-- Increment the email counter
set emailCounter to emailCounter + 1
-- Set the next sending time
set startTime to startTime + (1 * minutes) as date -- 1 minute interval
end repeat
At first glance the code to make an email looks okay. I’m not familiar with AppleScript in Numbers, though. The range “A2:D” is not good. And the “end tell” should be after the “end repeat”.
set startTime to current date
set startTime to startTime + (1 * minutes)
– Get the email data from the Numbers sheet
tell application “Numbers”
set emailData to value of range “A2:D” of sheet 1 of document “MailList”
end tell
– Choose the email account to send from
set senderEmail to “param@threefilm.com” – Replace with the email address of the desired account
– Iterate through the email data and send emails
set emailCounter to 0
repeat with emailRow in emailData
– Check if the daily email limit has been reached
if emailCounter ≥ 45 then exit repeat
-- Get the email details
set recipient to item 1 of emailRow
set recipientName to item 2 of emailRow
set subject to item 3 of emailRow
set message to item 4 of emailRow
-- Delay until the next sending time
repeat until (current date) ≥ startTime
delay 1
end repeat
-- Compose the email message
set emailContent to "Dear " & recipientName & "," & return & return & message
-- Send the email
tell application "Mail"
set newEmail to make new outgoing message with properties {subject:subject, content:emailContent}
tell newEmail
make new to recipient with properties {address:recipient}
send
end tell
end tell
-- Increment the email counter
set emailCounter to emailCounter + 1
-- Set the next sending time
set startTime to startTime + (1 * minutes) as date -- 1 minute interval
This looks better, I guess. I am getting two problems.
1- Unless the numbers document is open, the script can’t find it. Which is something I can work around.
2- I get this error: error “Mail got an error: Can’t get subject.” number -1728 from subject
Probably because ‘subject’ is a property of the message and can’t be used as a variable name as well. Try changing the variable name wherever it occurs in the script.
I would suggest reading through Mail’s Scripting Dictionary and checking that none of your variable names match any of its elements, classes or properties.
I had some other fun so it took me a while to finish the script:
-- Get the email data from the Numbers sheet
tell application "Numbers" to tell document "MailList.numbers" to tell sheet 1 to tell table 1
set emailData to value of every cell of range "A2:D3"
end tell
--put the single list into 4 lists
set Counter to 1
set RecipientList to {}
set RecipientNameList to {}
set SubjectList to {}
set MessageList to {}
repeat with theItem in emailData
if Counter = 1 then
set end of RecipientList to theItem
else if Counter = 2 then
set end of RecipientNameList to theItem
else if Counter = 3 then
set end of SubjectList to theItem
else if Counter = 4 then
set end of MessageList to theItem
end if
set Counter to Counter + 1
if Counter = 5 then set Counter to 1
end repeat
repeat with currentEmail from 1 to length of RecipientList
set theRecipient to item currentEmail of RecipientList
set theRecipientName to item currentEmail of RecipientNameList
set theSubject to item currentEmail of SubjectList
set theMessage to item currentEmail of MessageList
--Compose the email message
set emailContent to "Dear " & theRecipientName & "," & return & return & theMessage
--Send the email
tell application "Mail"
set newEmail to make new outgoing message with properties {subject:theSubject, content:emailContent}
tell newEmail
make new to recipient at end of to recipients with properties {address:theRecipient}
--send
end tell
end tell
end repeat
