How to get list of email addresses from a saved excel file

Hi,

I am new(-ish) to applescript but I have been able to write a script that allows me to bulk email using addresses from the apple contacts book.

Now that works fine for testing purposes (I have been using 2 of my own email addresses to test) but the person I am writing the applescript for is for send 250 emails at a time (different groups).

So I am wondering if there is anyone out there who could help me with extracting email addresses from an excel file and have mail send it one message at a time.

Here are two parts of the script I am using to send the emails and get the emails from apple address book and choosing of which group it gets the emails from:


display dialog "Choose Mail Group" buttons {"TEST", "Test Bulk"}
if result = {button returned:"TEST"} then
	set theGroups to "TEST"
	set theEmailGroup to "TEST"
else if result = {button returned:"Test Bulk"} then
	set theGroups to "Test Bulk"
	set theEmailGroup to "Test Bulk"
end if
end

tell application "Contacts"
	activate
	set EmailAddresses to value of email 1 of people of group theEmailGroup
	tell application "Mail"
		activate
		repeat with i from 1 to count of EmailAddresses
			tell (make new outgoing message)
				set visible to true
				make new to recipient at end of to recipients with properties {address:item i of EmailAddresses}

and


								
								tell application "System Events"
									tell process "Mail"
										repeat with i from 1 to count of EmailAddresses
											keystroke "d" using {command down, shift down}
										end repeat
									end tell
								end tell

Hope someone in the forums will be able to help!

Also the middle bit of this script if for copy and pasting a HTML news letter (opens safari at a certain URL and copy-paste)

If it may be needed here is my full script at this moment in time:


display dialog "Choose Mail Group" buttons {"TEST", "Test Bulk"}
if result = {button returned:"TEST"} then
	set theGroups to "TEST"
	set theEmailGroup to "TEST"
else if result = {button returned:"Test Bulk"} then
	set theGroups to "Test Bulk"
	set theEmailGroup to "Test Bulk"
end if
end

tell application "Contacts"
	activate
	set EmailAddresses to value of email 1 of people of group theEmailGroup
	tell application "Mail"
		activate
		repeat with i from 1 to count of EmailAddresses
			tell (make new outgoing message)
				set visible to true
				make new to recipient at end of to recipients with properties {address:item i of EmailAddresses}
				delay 2
				tell application "Safari"
					activate
					tell window 1 to set current tab to make new tab with properties {URL:"http://www.google.co.uk"}
					delay 2
					tell application "System Events"
						keystroke "a" using command down
						delay 2
						keystroke "c" using command down
						tell application "Mail"
							activate
							delay 1
							tell application "System Events"
								keystroke tab
								delay 1
								keystroke tab
								delay 1
								keystroke "test"
								delay 1
								keystroke tab
								keystroke "v" using command down
								
								tell application "System Events"
									tell process "Mail"
										repeat with i from 1 to count of EmailAddresses
											keystroke "d" using {command down, shift down}
										end repeat
									end tell
								end tell
							end tell
						end tell
					end tell
				end tell
			end tell
		end repeat
	end tell
end tell

Any help would be great!

Thanks,
Bruce

Model: Mac Mini
AppleScript: 2.3.2
Browser: Safari 537.77.4
Operating System: Mac OS X (10.8)

That sounds to most remote spam filters as spam. Just a small heads up.

get mail addresses from a text file you can use:

paragraphs of (do shell script "egrep -o '\\b[a-zA-Z0-9.-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z0-9.-]+\\b' " & quoted form of POSIX path of (choose file)) 

save the file as an text file (csv, txt, xml) and run the command above

I have just tried saving it within a folder which has the .csv file and I got the syntax error:
Expected “”" but found unknown token.
All I did was change the file name and the extension:

paragraphs of (do shell script "egrep -o '\\b[a-zA-Z0-9.-]+@[a-zA-Z0-9.-]+\.[a-zA-Z0-9.-]+\\' bulk test.csv")

it highlighted the full stop in ]+.[

Did I do something wrong? or is this not supposed to be put into applescript editor?

Thanks,
Bruce

There looks to be a backslash missing. Also, the space in that file name will be problematic. Try it like this:

paragraphs of (do shell script ("egrep -o '\\b[a-zA-Z0-9.-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z0-9.-]+\\' " & quoted form of theFileName))

Thanks for that although i put in the new filename with no spaces and the extension .csv but i get a syntax error saying it expected a “,” where the . is in .csv and if i remove it it says can not find variable.

paragraphs of (do shell script ("egrep -o '\\b[a-zA-Z0-9.-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z0-9.-]+\\' " & quoted form of bulktest.csv))

EDIT:

Never mind I declared theFileName as “bulktest.csv” but now I get a new error “Apple Script error egrep: trailing backslash ()”

Try removing the two trailing backslashes.

Thanks for that it works great the only issue I have is that I ran that small script and saved it in with a folder with the .csv file yet it could not find it so I had to declare the full file path any reason as to why that is? it says there is no such file or directory as “bulktest.csv” if I just leave it on its own.

Would be great to have it working within a folder as I need to send the script file along with the different csv files and it would be easier for them so they don’t have to have it stored in a certain location.

Thanks,
Bruce

There is no “current directory” in AppleScript so you have to specify always the full path.
However you can get the full (HFS) path of the current script with

path to me as text

or the parent folder with


tell application "System Events" to set parentFolder to path of container of (path to me)

I attempted the parent fielder script and it worked on its own but when I try to run it for detecting a file path (I have it at the top of the script) I get this:


tell current application
	path to current application
		--> alias "Server:Users:server:Desktop:Bulk Test:send test.scpt"
end tell
tell application "System Events"
	get path of container of alias "Server:Users:server:Desktop:Bulk Test:send test.scpt"
		--> "Server:Users:server:Desktop:Bulk Test:"
end tell
tell application "AppleScript Editor"
	display dialog "Choose Mail Group" buttons {"TEST", "Test Bulk", "bulktest"}
		--> {button returned:"Test Bulk"}
end tell
tell current application
	do shell script "egrep -o '\\b[a-zA-Z0-9.-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z0-9.-]+' 'testbulk.csv'"
		--> error "egrep: testbulk.csv: No such file or directory" number 2
Result:
error "egrep: testbulk.csv: No such file or directory" number 2

Have I put it in the wrong place placing it at the top of the script?

Here is my file declaration script (choosing of which file)


tell application "System Events" to set parentFolder to path of container of (path to me)


display dialog "Choose Mail Group" buttons {"TEST", "Test Bulk", "bulktest"}
if result = {button returned:"TEST"} then
	set theGroups to "test"
	set theFileName to "test.csv"
	set theEmailGroup to paragraphs of (do shell script ("egrep -o '\\b[a-zA-Z0-9.-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z0-9.-]+' " & quoted form of theFileName))
else if result = {button returned:"Test Bulk"} then
	set theGroups to "testbulk"
	set theFileName to "testbulk.csv"
	set theEmailGroup to paragraphs of (do shell script ("egrep -o '\\b[a-zA-Z0-9.-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z0-9.-]+' " & quoted form of theFileName))
else if result = {button returned:"bulktest"} then
	set theGroups to "bulktest"
	set theFileName to "bulktest.csv"
	set theEmailGroup to paragraphs of (do shell script ("egrep -o '\\b[a-zA-Z0-9.-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z0-9.-]+' " & quoted form of theFileName))

Any help is greatly appreciated!!

Thanks,
Bruce

of course you have to put the path to the parent folder and the file name together


tell application "System Events" to set parentFolder to POSIX path of container of (path to me)
do shell script "egrep -o '\\b[a-zA-Z0-9.-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z0-9.-]+' " & quoted form of (parentFolder & "/'testbulk.csv")

Thanks for that, although when I try to use this under a variable it says expected expression but found “tell”.

How would I go about putting this into a variable?

Thanks,
Bruce

for example


tell application "System Events" to set parentFolder to POSIX path of container of (path to me)

display dialog "Choose Mail Group" buttons {"TEST", "Test Bulk", "bulktest"}
if result = {button returned:"TEST"} then
	set theGroups to "test"
	set theFileName to "test.csv"
	set theEmailGroup to paragraphs of (do shell script ("egrep -o '\\b[a-zA-Z0-9.-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z0-9.-]+' " & quoted form of (parentFolder & "/" & theFileName)))
end if

GREAT!!! Thank you so much for that!

Thankyou everyone for your help Im sorry I am new to applescript as this all maybe very simple to you I am very grateful.

Now there is one little niggle that I have noticed and that is the web page has to reopen every time it cycles through now this could get a bit heavy on safari when it will be used for its actually purpose as on average it will be used to send up to 250 emails a time and I don’t think safari would like to have 250 tabs open at any one time.

I know it is a bit off topic to the original question but it would be really helpful as I don’t really want safari to be opening 250 tabs and the process to potentially slow down a lot.

If anyone can help that would be great! but thank you all for your help so far to a little noobie like me.

Thanks,
Bruce

EDIT:

Here is the main chuck of the code where the body of the message is made. I could just add a “q” with command down? (can’t remember the quit shortcut) but I would rather not have safari have to load every time I would like it to detect if the active tab is of that URL then just copy and paste into the new current message.

set EmailAddresses to theEmailGroup
tell application "Mail"
	activate
	repeat with i from 1 to count of EmailAddresses
		tell (make new outgoing message)
			set visible to true
			make new to recipient at end of to recipients with properties {address:item i of EmailAddresses}
			delay 2
			tell application "Safari"
				activate
				tell window 1 to set current tab to make new tab with properties {URL:"***.html"}
				delay 2
				tell application "System Events"
					keystroke "a" using command down
					delay 2
					keystroke "c" using command down
					tell application "Mail"
						activate
						delay 1
						tell application "System Events"
							keystroke tab
							delay 1
							keystroke tab
							delay 1
							keystroke "test"
							delay 1
							keystroke tab
							keystroke "v" using command down
							
							tell application "System Events"
								tell process "Mail"
									repeat with i from 1 to count of EmailAddresses
										keystroke "d" using {command down, shift down}
									end repeat
								end tell
							end tell
						end tell
					end tell
				end tell
			end tell
		end tell
	end repeat
end tell

why do you open that amount of Safari tabs for the same URL and use the resend function in Mail?

Wouldn’t it be easier to create the HTML source once and send one Mail to all recipients.
To avoid that the recipients see the mail addresses of the others, send the mail to yourself and put all recipients into the BCC field

Just had a look at doing that, I don’t like how in both the to and from field it shows the senders email address surly this could defiantly be picked up by spam filters if the to and from fields are the same and everyone is in the BCC field?

I know sending a large bulk email is seen as spam as well but I would like to have separate emails send out instead of bulking it and showing the to and from info as the same person.

Thanks,
Bruce

I’ve updated my previous post, I wasn’t behind a mac to test (compile) when I wrote my post, and forgot escaping some characters for AppleScript and the trailing b which I have added again in my post. The ‘\b’ (unescaped: ‘\b’) are word boundaries which makes sure that the mail address isn’t surrounded by invalid characters.

sorry for the inconvenience.