Personalized email merge to contacts within date-range

pseudodennis · June 29, 2016, 12:49am

Hi Everyone.

First-time poster, long-time lurker, but I’m fairly new to Applescript and think it can meet a need. Normally I can search and modify others’ script suggestions to make them work for me, but in this case it’s somewhat of a unique issue (apparently) and can’t track down the script-bits to make it work.

The Goal: I need a script that will send a personalized email to multiple contacts located on a spreadsheet within a certain date range.

Context: When guests register at our non-profit, I need to follow up with a series of emails highlighting our upcoming events, etc, over a span of about a month. After which, they drop from the email list. The first two emails are always the same, but the next four will be constantly updated as our events change throughout the season.

Parameters:

I use Apple mail.app linked to gmail.

A spreadsheet, “guests.numbers” (Excel, Numbers, google sheet, or CSV/TSV ” whatever is simplest to script. The script should preferably not have to open the associated application to find the data on the sheet.). The guest data is in the following format:

[format]| A | B | C | D | E |
| date | firstname | lastname | guestemail | phonenumber |
| | | | | |
| 2016-05-01 | Tony | Stark | ironman@shield.com | 555.5555 |
| 2016-05-08 | Steve | Rogers | america@shield.com | 555.5556 |
| 2016-05-22 | Bruce | Banner | iamincredible@shield.com | 555.5557 |[/format]

A second spreadsheet, “emails.numbers”. This sheet contains the subject lines and body of the email messages themselves. (I prefer to keep the email texts in a separate document so I don’t have to edit the script every time I want to edit the email text. The email data is in the following format:

The script will be triggered via a series of staggered iCal alarms. Each ˜stagger’ will reference a different row of content, resulting in a different email sent each week. (Example: The iCal alarm on May 29 will send the “first” email to guests dated 2-4 weeks ago [Tony and Steve]. The iCal alarm on June 5 will send the “second” email to guests dated 2-4 weeks ago [Steve and Bruce]. Etc.)

I have this script as my starting point:


property theSender : "my@email.com"
property theSubject : "The same for every message"
property theSignature : "mySignature"

tell application "Mail"
   try
       set mySignature to signature theSignature
   on error
       set mySignature to missing value
   end try
end tell

tell application "Microsoft Excel" to set theData to value of used range of active sheet
repeat with aRow in theData
   set {theName, address1, address2, address3, address4, postcode, theEmail} to aRow
   set mailBody to "Dear " & theName & return & return
   set mailBody to mailBody & "Some blurb" & return & return
   set mailBody to mailBody & "I'm writing to confirm that the following contact details are correct." & return & return
   set mailBody to mailBody & address1 & return
   set mailBody to mailBody & address2 & return
   set mailBody to mailBody & address3 & return
   set mailBody to mailBody & address4 & return & return & "Thank you" & return & return
   
   tell application "Mail"
       set newMessage to make new outgoing message with properties {sender:theSender, visible:true, subject:theSubject}
       tell newMessage
           set content to mailBody
           make new to recipient at end of to recipients with properties {address:theEmail}
           if mySignature is not missing value then set message signature to theSignature
       end tell
       -- send newMessage
   end tell
end repeat

Which I found here: [http://macscripter.net/viewtopic.php?id=38096]

It almost does what I need, it just needs.
(1) To allow the contact list to be limited to a particular date range
(2) To allow the source of the email text to come from outside the script itself.
(3) To bypass opening Excel/Numbers every time the script runs.

Sorry for the long question! I’ve tried to include only the necessary information. Also, if there are other posts that have solved these issues that I’ve missed, please point them out to me and I’ll be happy to try to work them in.

AppleScript: 2.4
Browser: Safari 601.6.17
Operating System: Mac OS X (10.10)

Marc_Anthony · June 29, 2016, 8:20pm

Hi. Welcome to the forum.

You can read messages from a tab-delimited CSV file. Here is a possible starting point; it can be run weekly, as written. You could run it daily (or as needed) by adding a “mailing done” option to your database and verifying that boolean along with the date checks.


set timeNow to (current date)
set {bizweek1, bizweek2, bizweek3} to {(timeNow - 5 * days), (timeNow - 10 * days), (timeNow - 15 * days)}
repeat with aLine in (read alias (((path to desktop) as text) & "database.txt"))'s paragraphs 2 thru -1 --assumes CSV file with header line lives on desktop
	set text item delimiters to "-"
	set reorderTime to (get aLine's {word 2, word 3, word 1}) as text --assumes your system uses month-day-year format
	set text item delimiters to tab
	set {nom1, nom2, emailAddy, tele} to aLine's {text item 2, text item 3, text item 4, text item 5}
	set text item delimiters to ""
	tell date reorderTime
		if it is greater than bizweek1 and it is less than timeNow then --mailing may already be complete, if this runs more than weekly
			"0-5 days old"
			--do 1st mailing things
			set theMessage to (read alias (((path to desktop) as text) & "messages.txt"))'s paragraph 2 --message 1 (strip out redundant info)
			tell application "Mail"
				--whatever
			end tell
		else
			if it is greater than bizweek2 and it is less than bizweek1 then
				"5-10 days old"
				--do 2nd mailing things
			else
				if it is greater than bizweek3 and it is less than bizweek2 then
					"10-15 days old"
					--do 3rd mailing things
				else
					"Age off database."
				end if
			end if
		end if
	end tell
end repeat

pseudodennis · June 29, 2016, 9:29pm

Thanks, Marc.

I’ll fiddle around with this a bit tonight and see if I can combine the scripts together. It seems fit all three of my criteria, above, which I’m excited about.

I’ll post back how things go.

pseudodennis · July 1, 2016, 5:19am

UPDATE: Please disregard this inquiry… the file “database.txt” had blank lines at the bottom, which resulted in the error. :rolleyes:

I’ve tweaked it a bit and had it working. (Just the code Marc posted - haven’t tried to actually send email yet.) For some reason I could only get the paths to database.txt and messages.txt working when I defined them as variables (or is it parameters… not sure on the terminology).

The main hangup right now is that for some reason the line…

set reorderTime to (get aLine's {word 1, word 2, word 3}) as text

…generates an error:

[format]error “Can’t get word 1 of " ".” number -1728 from word 1 of " "[/format]

It wasn’t doing this last night, and I can’t imagine that I’ve changed anything pertinent to that line. I’ve reverted to the version I had working and I can’t get that to work, now, either.

Here are the contents of database.txt:
[format]
date firstname lastname guestemail phone number
6-5-2016 Tony Stark ironman@shield.com 555.5555
6-12-2016 Steve Rogers america@shield.com 555.5555
6-19-2016 Bruce Banner iamincredible@shield.com 555.5555
[/format]

And here is my latest version of the script: (Ignore my added notes-to-self and also the else-if series… I was testing various options with notifications to see how it worked)

-- variables
set timeNow to (current date)
set database to (read alias (((path to desktop) as text) & "database.txt")) -- path to database file
set messages to (read alias (((path to desktop) as text) & "messages.txt")) -- path to email file
set {week1, week2, week3, week4} to {(timeNow - 7 * days), (timeNow - 14 * days), (timeNow - 21 * days), (timeNow - 28 * days)}

-- reading the database
repeat with aLine in database's paragraphs 2 thru -1 --assumes CSV file with header line lives on desktop
	set text item delimiters to "-" --format date with dashes, not hyphens, or else change the delimiter
	set reorderTime to (get aLine's {word 1, word 2, word 3}) as text --assumes your system uses month-day-year format EDIT: It apparently does not
	set text item delimiters to tab
	set {visitdate, firstname, lastname, email, phone} to aLine's {text item 1, text item 2, text item 3, text item 4, text item 5}
	set text item delimiters to ""
	
	-- compile emails based on age
	tell date reorderTime
		if it is greater than week1 and it is less than timeNow then --mailing may already be complete, if this runs more than weekly
			"0-7 days old"
			--do 1st mailing things
			set theMessage to messages's paragraph 2 --message 1 (strip out redundant info)
			tell application "System Events"
				display notification with title firstname subtitle theMessage
			end tell
			tell application "Mail"
				--whatever
			end tell
		else
			if it is greater than week2 and it is less than week1 then
				set theMessage to messages's paragraph 2 --message 1 (strip out redundant info)
				tell application "System Events"
					display notification with title firstname subtitle theMessage
				end tell
				--do 2nd mailing things
			else
				if it is greater than week4 and it is less than week1 then
					"15-20 days old"
					tell application "System Events"
						display notification firstname
					end tell
					--do 3rd mailing things	
				else
					"age off database"
					firstname
				end if
			end if
		end if
	end tell
end repeat

Marc_Anthony · July 1, 2016, 3:21pm

Hello, again. You changed the date format in the database text to 6-5-2016 from 2016-05-01, but words 1-3 yields the same month, day, year result as words 2, 3, 1 in the original. Use whatever arrangement is returned from (current date). I used word, but, since what constitutes a word can also be affected by your system settings, this may be more reliable for isolating the date components:

set reorderTime to aLine's text 1 thru ((offset of tab in aLine) - 1)'s text items as text

At the 15-20 day mark, week1 should be week3.