Sending Personalized Email From Excel 2008

Peasantshoes · February 13, 2012, 4:53pm

Hello there,

I’m completely new to Applescript. I’m running on Mac 10.5.8 and excel 2008.

I have a spreadsheet with data fields for Name, Address 1, Address 2, etc, Email. I’d love a script that looks a little like this one I found here http://spreadsheetpage.com/index.php/tip/sending_personalized_email_from_excel/, but in applescript.

Essentially, I need to send emails with personalised content related to the data in the adjacent fields.

Any help much appreciated!

StefanK · February 13, 2012, 5:13pm

Hi,

you can extract the data of all cells of the used range with one line


tell application "Microsoft Excel" to set theData to value of used range of active sheet

the result is a list of lists. Each inner list contains the value of all cells of a row
For example assuming there are three columns name, email and some other information
this code creates a new mail in Mail.app with name, address and “some other information” as subject


property theSender : "my@email.com"

tell application "Microsoft Excel" to set theData to value of used range of active sheet
repeat with aRow in theData
	set {theName, theEmail, someOtherInformation} to aRow
	tell application "Mail"
		set newMessage to make new outgoing message with properties {sender:theSender, visible:true, subject:someOtherInformation}
		tell newMessage
			set content to "This is the body of the message"
			make new to recipient at end of to recipients with properties {name:theName, address:theEmail}
		end tell
	end tell
end repeat

Peasantshoes · February 15, 2012, 1:04pm

Thanks Stefan,

That’s definitely useful but doesn’t quite hit the nail on the head, probably because I was unclear about what I need.

I need to send about 200 personalised emails, such as:

To: [personalised@email.com]
From: myemail@email.com

Subject: The same for every message

Body of message:

Dear [personalised name]

Some blurb.

I’m writing to confirm that the following contact details are correct.

[personalised address 1]
[personalised address 2]
[personalised address 3]
[personalised address 4]
[personalised postcode]

Thank you,

Signature

I have a spreadsheet with the following data fields:

Column A - Name
Column B - Address 1
Column C - Address 2
Column D - Address 3
Column E - Address 4
Column F - Postcode
Column G - Email

Is it possible to automate this?

Thanks

Gabriel

StefanK · February 15, 2012, 1:32pm

Try this, I commented out the line to send the mail(s).
The post code information seems to be unused.
The script assumes that the Excel sheet contains only the 7 columns with the appropriate data


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

Peasantshoes · February 15, 2012, 2:39pm

Amazing, that works almost perfectly.

The one missing piece of functionality is that the automatic signature (which includes an image) that normally appears on any message I send out doesn’t seem to appear on the messages this code produces. Furthermore, the box next to “Signature” in the ‘compose new message’ window displays ‘none’ where normally it would display ‘mysignature’.

So, I guess I’m asking, how do I set “mySignature” to the one I normally use?

Thanks again,

Gabriel

StefanK · February 15, 2012, 2:54pm

the literal string "mySignature in the property theSignature line must match the name of one of the defined signatures.

If you’re using always the same signature it might work to delete the first Mail application tell block and
the if mySignature is not. line in the second Mail tell bolck

Peasantshoes · February 15, 2012, 3:06pm

Neither of those fixes seemed to work.

defining “mySignature” as “the name of my signature” results in the following error message

Mail got an error: AppleEvent handler failed.

As well as the error message, ‘message signature’ becomes highlighted in the second mail tell block.

Deleting the references to the signature in both tell blocks results in the original problem.

I have to sign out now, but will check back in tomorrow.

Thank you for your time and help.

Gabriel

MrMLK · February 13, 2013, 2:37pm

This is almost exactly what I need. Can I ask you a few questions:

Will all this still work with MountainLion and Excel 2011?
Is there some way to attach a file to each email? In my case, it will be the same file, although ideally, it would be nice to know how to use a file whose name was in a column of the spreadsheet.

Thanks for the help.

StefanK · February 13, 2013, 2:47pm

I think so

Yes, add this line after the set content to mailBody line

tell content to make new attachment with properties {file name:theAttachment as alias} at after the last paragraph

the variable theAttachment must contain a valid full HFS path (starting with a disk name and colon separated)

MrMLK · February 13, 2013, 4:27pm

Thanks very much.

Everything else seems to be working except I keep getting an error when I try to set the signature.

I set the value to

property theSignature : “KJ”

Then at the line:

		if mySignature is not missing value then set message signature to theSignature

The error is

Mail got an error: Can’t make “KJ” into type signature, missing value.

if I change the line to

		if mySignature is not missing value then set message signature to signature theSignature

I get the error:

Mail got an error. Can’t set signature “KJ” of outgoing message id 4 to signature “KJ” of outgoing message id 4.

Edit: Never mind, I figured this out. I should have been using the variable mySignature, not theSignature.

MrMLK · February 13, 2013, 4:33pm

One more question.

One of the columns in my spreadsheet is a number. In the spreadsheet it shows up as 20,000. In my email it shows up as 2.0E+4. Is there some way to format the number so that it shows up as the entire number with the comma?

Thanks again for all the help.

StefanK · February 13, 2013, 4:46pm

this should work


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

-- .

tell application "Mail"
	-- .
	tell newMessage
		-- .
		if mySignature is not missing value then set message signature to mySignature
	end tell
end tell

the cells have a property string value which avoids scientific numeric notation

MrMLK · February 13, 2013, 5:05pm

StefanK:

this should work


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

-- .

tell application "Mail"
	-- .
	tell newMessage
		-- .
		if mySignature is not missing value then set message signature to mySignature
	end tell
end tell

Its very weird. When I run the script, it generates the emails with the proper signature, and then a second or so later, the signature is deleted from the email.

MrMLK · February 13, 2013, 5:45pm

When I move the signature line to above the attachment lines, it all works perfectly.

Thanks very much for the help, you saved me a lot of time.

esneaker · February 22, 2015, 9:10pm

I created a code using VBA that allows you to send a range of cells as an attachment in Excel 2011. It also allows you to send the mail from Applemail and Outlook (help on one would be enough!). This code was modified from Ron De Bruin’s code. Is there a way to utilize this function using AppleScript for Excel 2008?

A sample of my code is below.

Dim Source As Range
Dim Destwb As Workbook
Dim wb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim FileFormatNum As Long

If Val(Application.Version) < 14 Then Exit Sub

Set Source = Nothing
On Error Resume Next
Set Source = Range("A96:F134").SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If Source Is Nothing Then
    MsgBox "The source is not a range or the sheet is protected, " & _
           "please correct and try again.", vbOKOnly
    Exit Sub
End If

With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With

Set wb = ActiveWorkbook
Set Destwb = Workbooks.Add(xlWBATWorksheet)

Source.Copy
With Destwb.Sheets(1)
    .Cells(1).PasteSpecial Paste:=8
    .Cells(1).PasteSpecial Paste:=xlPasteValues
    .Cells(1).PasteSpecial Paste:=xlPasteFormats
    .Cells(1).Select
    Application.CutCopyMode = False
End With

'Save format and extension
FileExtStr = ".xlsx": FileFormatNum = 52

'Or if you want it in xls format, use:
'FileExtStr = ".xls": FileFormatNum = 57


'Save the new workbook/Mail it/Delete it
'If you want to change the file name then change only TempFileName
TempFilePath = MacScript("return (path to documents folder) as string")
TempFileName = wb.Name & " " & Format(Date, "MMM-YYYY")

With Destwb
    .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
    MailFromMacwithOutlook bodycontent:="Hi there", _
                mailsubject:="Incidental Tracker for" & Format(Date, "MMM-YYYY"), _
                toaddress:="yum.yum@mail.com", _
                ccaddress:="", _
                bccaddress:="", _
                attachment:=.FullName, _
                displaymail:=True
    .Close SaveChanges:=False
End With

KillFileOnMac TempFilePath & TempFileName & FileExtStr

With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With

End Sub

Thank you ahead of time for your assistance and help!!