I am trying to write a script that will prompt the user to select account, select signature, select text file, select excel file. Then will read excel file and created personalized emails on Mail to send with the text file being the body (this is personalized with a name on a specific row in the excel file) and gets sent to the email on that specific row.
Here is my code, but I keep getting a runtime error:
tell application "Mail" to set allAccounts to name of every account
choose from list allAccounts with title "Choose the Mail account to use..."
set theAccount to result as string
tell application "Mail" to set allSignatures to name of every signature
choose from list allSignatures with title "Choose the signature to use."
set theSignatureName to result as string
set subjectDialog to display dialog ¬
"Enter the subject of the email to send" default answer "Newsletter Update"
set theSubject to text returned of subjectDialog
set sendOrPreview to the button returned of ¬
(display dialog ¬
"Send the messages right away or preview and send manually?" with title ¬
"Send or Preview?" with icon caution ¬
buttons {"Preview", "Send"} ¬
default button 1)
set theText to (choose file with prompt "Pick a text file containing the text")
set messageText to read theText
tell application "Finder"
tell application "Microsoft Excel"
tell active sheet
tell used range
set rowCount to count of rows
end tell
set theData to value of range ("A1:C" & rowCount) of ¬
(read (choose file with prompt "Pick the excel list"))
end tell
end tell
end tell
tell application "Mail"
activate
set activeAccount to account theAccount
repeat with aRow in theData
set {theEmail, theName, theSurname} to aRow
set the messageBody to messageText
set the messageBody to replace_chars(messageBody, "<<theName>>", theName)
set the messageBody to replace_chars(messageBody, "<<theSurname>>", theSurname)
if (the length of theSurname) > 1 then
set theContent to "Dear" & theName & theSurname & return & return & messageBody
else
set theContent to "Dear" & theName & return & return & messageBody
end if
set newMessage to make new outgoing message ¬
with properties {account:activeAccount, subject:theSubject, content:theContent}
set message signature of newMessage to signature theSignatureName of application "Mail"
tell newMessage
set sender to ¬
((full name of activeAccount & " < " & email addresses of activeAccount as string) & " > ")
make new to recipient at end of to recipients ¬
with properties {address:theEmail}
set visible to true
end tell
tell application "System Events"
tell application process "Mail"
set frontmost to true
end tell
keystroke "T" using {command down, shift down}
end tell
if sendOrPreview is equal to "Send" then
send newMessage
end if
end repeat
end tell
Browser: Safari 537.11
Operating System: Mac OS X (10.7)
Ok, thanks!
Fixed, but now I have another problem. When it open the Excel workbook, since I have links in it, it has a pop up asking if I want to ignore, update, or edit those links. I want the applescript to be something like.
If POPUP then
press UPDATE
end if
Here is the updated version:
(* Copyright 2012 Gonzalo Drinot
This snippet is licensed under the Apache License version 2.0
see http://www.apache.org/licenses/LICENSE-2.0.html *)
tell application "Mail" to set allAccounts to name of every account
choose from list allAccounts with title "Choose the Mail account to use..."
set theAccount to result as string
tell application "Mail" to set allSignatures to name of every signature
choose from list allSignatures with title "Choose the signature to use."
set theSignatureName to result as string
set subjectDialog to display dialog ¬
"Enter the subject of the email to send" default answer "Newsletter Update"
set theSubject to text returned of subjectDialog
set sendOrPreview to the button returned of ¬
(display dialog ¬
"Send the messages right away or preview and send manually?" with title ¬
"Send or Preview?" with icon caution ¬
buttons {"Preview", "Send"} ¬
default button 1)
set theText to (choose file with prompt "Pick a text file containing the text")
set messageText to read theText
set theExcel to (choose file with prompt "Pick the excel list")
tell application "Microsoft Excel"
open theExcel
set rowCount to first row index of (get end (last cell of column 1) direction toward the top)
set theData to value of range ("A3:E" & rowCount)
tell application "Mail"
activate
set activeAccount to account theAccount
repeat with aRow in theData
set {theEmail, theTitle, theName, theSpouse, theSurname} to aRow
set the messageBody to messageText
if (the length of theSurname) > 1 then
set theContent to "Dear" & theName & theSurname & return & return & messageBody
else
set theContent to "Dear" & theName & return & return & messageBody
end if
set newMessage to make new outgoing message ¬
with properties {account:activeAccount, subject:theSubject, content:theContent}
set message signature of newMessage to signature theSignatureName of application "Mail"
tell newMessage
set sender to ¬
((full name of activeAccount & " < " & email addresses of activeAccount as string) & " > ")
make new to recipient at end of to recipients ¬
with properties {address:theEmail}
set visible to true
end tell
tell application "System Events"
tell application process "Mail"
set frontmost to true
end tell
keystroke "T" using {command down, shift down}
end tell
if sendOrPreview is equal to "Send" then
send newMessage
end if
end repeat
end tell
end tell
Thanks Stefan, I’ve been pulling my hair out trying to get a GUI solution to dismiss the links dialog with no luck, and the open workbook command with the parameter “update links do not update links” did the trick.