Printing Excel Worksheet to PDF then sending or scheduling an email

I’m brand new to Applescript and I have found my first task to automate. Thanks for the tutorials on this site. I have searched this site and the web as well as fumbled around with automator and applescript and I make any progress. This is what is what I usually do and I want to script it:

  1. cmd - p to print an excel sheet
  2. select pdf
  3. remove “.xls” from title so it will be name.pdf instead of name.xls.pdf, and select the folder (it’s the same every time)
  4. hit the save button
  5. go to mail, (or quicksilver), attach that file, and email it. The persons email address is on the spread sheet.

Could you please give me a hint as to which functions, etc I should be using?

Jon Robinson

Hi Jon,
please give this script a try and see if it does what you need! The only part I can’t do at the moment is specify the email address as I need you to provide a little more info as to where the address is specified in the Excel file, maybe send me an example file?

set thefile to choose file
tell application "Finder" to set thefiles_name to the name of thefile

tell application "Printer Setup Utility"
	set Current_Printer to name of current printer --> set a variable for the name of your current/default printer
	set current printer to printer "Adobe PDF 7.0"
end tell

tell application "Microsoft Excel"
	open thefile
	print out active sheet
	close active workbook saving no
end tell

tell application "Printer Setup Utility"
	set current printer to printer Current_Printer --> restore your current/default printer
end tell

delay 10 --> wait for the pdf to be created

	set pdf_alias to (path to desktop as string) & thefiles_name & ".pdf" as alias
	if thefiles_name ends with ".xls" then
		set new_file_name to characters 1 thru -4 of thefiles_name & "pdf" as string
		tell application "Finder" to set the name of pdf_alias to new_file_name
	end if
	set new_pdf_alias to (path to desktop as string) & new_file_name as alias
on error
	set pdf_string to (path to desktop as string) & thefiles_name & ".pdf" as string
	display dialog "Couldn't find file: " & pdf_string
end try

tell application "Mail"
	set nextnewmessage to make new outgoing message with properties {subject:new_file_name, content:"Your message details here"}
	tell nextnewmessage
		set visible to true
		make new to recipient at end of to recipients with properties {name:"JonsAdmin", address:""}
		tell content
			make new attachment with properties {file name:new_pdf_alias} at after the last paragraph
		end tell
	end tell
end tell