Mail merge from Excel

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)

Hi,

at a glance the handler replace_chars() is missing and the line


.
set theData to value of range ("A1:C" & rowCount) of ¬
				(read (choose file with prompt "Pick the excel list"))
.

looks strange. range in Excel is a property of an Excel document,
not of (read) plain text (of an Excel document).

Furthermore the Finder tell block is not needed at all.

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

you could use the open workbook command with parameter update links update remote and external links instead of the standard open command

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.