Little problem in the script

gdrinot · December 18, 2012, 8:50pm

So, I have written this script to send individualized emails from the mail app. I have given the option of selecting different kinds of salutations (formal salutation, semi-formal salutation, informal salutation) and for some reason this is giving trouble.
Basically, the formal makes it where the signature does not appear in any of the emails that are created; the semi-formal makes it where the signature appears only in the last email created; and the informal makes it where the signature appears in all the emails created.
I have read over the code over and over again, but I still cannot find the reason for that to be happening.
Here is the code:


(* 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 note ¬
		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


(* To choose your own file
set theExcel to (choose file with prompt "Pick the excel list")
*)

set theExcel to (path to documents folder as text) & "Wesley Intern:To-Do.xlsx"

set typeSalutation to the button returned of ¬
	(display dialog ¬
		"Choose a type of salutation for your email:" with title ¬
		"Type of Salutation" with icon note ¬
		buttons {"Formal", "Semi-Formal", "Informal"} ¬
		default button 3)

set choiceAttachment to the button returned of ¬
	(display dialog ¬
		"Would you like to add an attachment to your email?" with title ¬
		"Attachments?" with icon note ¬
		buttons {"Yes", "No"} ¬
		default button 1)

if choiceAttachment is equal to "Yes" then
	set theAttachment to (choose file with prompt "Please select your attachment:")
end if

tell application "Microsoft Excel"
	
	open theExcel
	repeat with rowCount from 1 to (count rows of active sheet)
		if value of cell 1 of row rowCount = "" then
			exit repeat
		end if
	end repeat
	
	set rowCount to rowCount - 1
	
	if rowCount is equal to 2 then
		quit theExcel without saving
		tell application "Finder"
			display dialog "There are no emails in your To-Do sheet" with title "Empty list"
		end tell
		error number -128
	end if
	
	set theData to value of range ("A3:E" & rowCount)
	
	tell application "Mail"
		activate
		set activeAccount to account theAccount
		set loopCounter to 3
		
		repeat with aRow in theData
			
			repeat 1 times
				tell application "Microsoft Excel"
					if value of cell ("F" & loopCounter) is "âœ”" then
						exit repeat
					end if
				end tell
				
				set {theEmail, theTitle, theName, theSpouse, theSurname} to aRow
				
				if theTitle is 0.0 then
					set theTitle to "1"
				end if
				if theSpouse is 0.0 then
					set theSpouse to "1"
				end if
				
				set the messageBody to messageText
				
				if typeSalutation is equal to "Formal" then
					if (the length of theTitle) > 1 then
						if (the length of theSpouse) > 1 then
							set theSalutation to theTitle & " & " & "Mrs. " & theName & " " & theSurname & ","
						else
							set theSalutation to theTitle & " " & theName & " " & theSurname & ","
						end if
					else
						if (the length of theSpouse) > 1 then
							set theSalutation to "Mr. & Mrs. " & theName & " " & theSurname & ","
						else
							set theSalutation to theName & " " & theSurname & ","
						end if
						
						set formalAlert to the button returned of ¬
							(display dialog ¬
								"One or more cells have not been assigned with a Title. If continue, this will send as Semi-Formal for these people." with title ¬
								"Title not found" with icon caution ¬
								buttons {"Continue", "I'll fix it"} ¬
								default button 1)
						if formalAlert is equal to "I'll fix it" then
							tell application "Microsoft Excel"
								open theExcel
							end tell
							error number -128
						end if
					end if
				end if
				
				if typeSalutation is equal to "Semi-Formal" then
					if (the length of theSpouse) > 1 then
						set theSalutation to theSpouse & " & " & theName & " " & theSurname & ","
					else
						set theSalutation to theName & " " & theSurname & ","
					end if
				end if
				
				if typeSalutation is equal to "Informal" then
					if (the length of theSpouse) > 1 then
						set theSalutation to theName & " & " & theSpouse & ","
					else
						set theSalutation to theName & ","
					end if
				end if
				
				set theContent to theSalutation & return & return & messageBody
				
				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 paragraph with data (return & return) at after the last paragraph
					
					make new to recipient at end of to recipients ¬
						with properties {name:theName & " " & theSurname, address:theEmail}
					if choiceAttachment is equal to "Yes" then
						make new attachment with properties {file name:theAttachment} at after the last paragraph
					end if
					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
					tell application "Microsoft Excel"
						set value of cell ("F" & loopCounter) to "âœ”"
					end tell
				end if
			end repeat
			set loopCounter to loopCounter + 1
		end repeat
		
		delay 7.5
		if sendOrPreview is equal to "Preview" then
			set sendAlert to the button returned of ¬
				(display dialog ¬
					"If you sent any of those emails manually, remember check off box on excel file. Would you like to do it now?" with title ¬
					"Note!" with icon note ¬
					buttons {"Do it now", "Later"} ¬
					default button 1)
			if sendAlert is equal to "Do it now" then
				tell application "Microsoft Excel"
					open active workbook
				end tell
				tell application "System Events"
					tell application process "Microsoft Excel"
						set frontmost to true
					end tell
				end tell
			else if sendAlert is equal to "Later" then
				tell application "Microsoft Excel"
					save active workbook
					quit theExcel
				end tell
			end if
		end if
	end tell
	if sendOrPreview is equal to "Send" then
		tell application "Microsoft Excel"
			save active workbook
		end tell
		quit theExcel
	end if
end tell

gdrinot · December 20, 2012, 8:39pm

Could this problem be because of the amount of things it must process? Should I add delays? I tried adding some delays between if statements, but it still has the same problem.
I need help!

mark_hunte · December 20, 2012, 10:15pm

Hi gdrinot,
Can I suggest you only post the part of the code that deals with the salutations. This may help in a response.

I know when I look at your post and see all that code, I do not fancy reading through it all just to figure out an obscure problem you are having with it. Also what might help you get a response from someone is comment as much of the code as possible to give someone and idea of what your code parts are meant to do.

I hope this helps you get a response.

Good luck.

Nigel_Garvey · December 20, 2012, 10:23pm

Hi gdrinot.

I don’t have Microsoft Excel and don’t use Mail, so I can’t try your script. I can however see some things which need sorting out.

Firstly, the whole of the last two-thirds of the script is a ‘tell application “Microsoft Excel”’ statement which contains a ‘tell application “Finder”’ statement and, for its greater part, a ‘tell application “Mail”’ statement containing further ‘tell’ statements to System Events and to Microsoft Excel again! You must tidy that up for neatness, execution speed, and the avoidance of terminolgy clashes and reader confusion.

Secondly, the several iterations of the line ‘quit theExcel’ are attempting to quit a piece of text, namely the path to your file “To-Do.xlsx”. ‘quit’ is for applications and is likely to affect the application in whose ‘tell’ block it occurs.

Thirdly, I imagine that the references to cells in Microsoft Excel need to include some reference to the document in which they occur.

That’s as much as I have time for at the moment. Good luck.