Automated invoice email function

NickeZ28 · November 3, 2016, 8:50am

Hi

I will try to describe what I want to accomplish :

Basically I have a computer set up with an email account running Apple Mail.app, and a excel .xls file (can also be converted into a numbers file if that is easier with OS X/Applescript) containing 4 columns and a vast number of rows. The columns are “Client name”, “client id 1”, “client code”, “email-address”.

To this e-mail account there are invoices sent (pdf’s) that I need forward/send out to my clients using my XLS sheet containing the email-addresses.

Today I have:

An applescript that saves the attached files/pdfs to a specific folder. An Automator folder-action tied to another specific folder on my mac, that when I drop a single pdf/invoice into that folder, Automator opens a new email with a specific signature and attaches the invoice/pdf, allowing me to type in the email address for the specific client. I search my XLS sheet manually for the client id of the pdf invoice and look for the email address in the next collumn (same row) and copy/paste that emailaddress manually. This is time consuming.

I would like (if possible) automate this whole process :

Save attached pdf to a folder (I HAVE SOLVED THIS USING THE FOLLOWING SCRIPT):


using terms from application "Mail"
	on perform mail action with messages theMessages for rule theRule
		set attachmentsFolder to "Volumes:Macintosh HD:folderxxx:" as rich text
		tell application "Mail"
			set selectedMessages to theMessages
			try
				repeat with theMessage in selectedMessages
					repeat with theAttachment in theMessage's mail attachments
						set originalName to name of theAttachment
						set savePath to attachmentsFolder & originalName
						save theAttachment in file (savePath)
					end repeat
				end repeat
			end try
		end tell
	end perform mail action with messages
end using terms from

This is going to be the tricky part I guess:

Using the same folder as a hot folder/connected action, and:

open the pdf in Preview
search text within the PDF in Preview app for the number right next to “Kundnr” (swedish for “client id”) in the pdf.
copy that number (client id) to clipboard

UPDATE: I have solved this one like this:

tell application "Adobe Acrobat Pro"
	activate
	tell active doc
		repeat with i from 1 to count of pages
			tell page i
				set {L, T, R, B} to media box
				set crop box to {L + 146, T - 200, R - 389, B + 655}
			end tell
		end repeat
	end tell
end tell
activate application "Adobe Acrobat Pro"
tell application "System Events"
	tell process "Acrobat"
		keystroke "a" using {command down}
		keystroke "c" using {command down}
	end tell
end tell
tell application "Adobe Acrobat Pro"
	activate
	close all docs saving "No"
end tell

Search the XLS sheet for the client id from clipboard
Select the column right next to (to the right in the XLS sheet) which will contain e-mailaddress (in the same row)
Copy email address to clipboard

UPDATE:
Continuing the AppleScript above with this code, Im able to get this half working as well:

set client_id to "somestuff"
get (the clipboard) = client_id

tell application "Microsoft Excel"
	set file_path to "Volumes:Main HD:path:to:file:export.xlsx"
	open file_path
	try
		set searchRange to range ("A2:B11000")
		set foundRange to find searchRange what "5700" look at whole with match case
		set fRow to first row index of foundRange
		set client_email to value of range ("C" & fRow as text)
	end try
end tell

However Im not able to search Excel for my variable that was set previosly, in this line:

set foundRange to find searchRange what "5700" look at whole with match case

I would like to write client_id instead of “5700”. But that doesnt work in my script,

Question Does anyone know what Im doing wrong there?

Is this the right way of defining the variable client_id and paste the contents from the clipboard to client_id ?

set client_id to "somestuff"
get (the clipboard) = client_id

Open a new email in Mail.app using a specific email account and a specific email signature (I HAVE AN AUTOMATOR ACTION FOR THIS PART, that I cant seem to find right now though)
Paste the email address to the client (copied in the section 3 above), stored in the variable “client_email”
Send the email

However. Some of my clients doesn’t accept their invoices to be emailed to them. For these clients I have chosen to add the word “post” instead of an e-mail address in the XLS sheet. It would be nice if these invoices are not emailed when the search in section 3 equals “post”. The best would rather be if they were sent/printed to the default installed printer instead. Or tagged with a color coding in OS X / Finder (for me to manually print later on).

I don’t know the forum rules 100%, but I would be happy to PayPal someone that could help me with this, if it can be accomplished.

Thanks for taking the time to read

/Niklas

NickeZ28 · November 8, 2016, 11:25am

I have managed to make some progress with this, here’s my script so far.

The only thing I cant get to work now is the last bottom part of it, where I would like to move the pdf from one folder to another… so the script can start over again with new pfd’s that are stored in the original folder…

(Have replaced all the folders in the script with fictional ones)

-- Save attached invoice from accountant
-- ***************************
-- ***************************
using terms from application "Mail"
	on perform mail action with messages theMessages for rule theRule
		set attachmentsFolder to "Volumes:Macintosh HD:folder1:folder2:" as rich text
		tell application "Mail"
			set selectedMessages to theMessages
			try
				repeat with theMessage in selectedMessages
					repeat with theAttachment in theMessage's mail attachments
						set originalName to name of theAttachment
						set savePath to attachmentsFolder & originalName
						save theAttachment in file (savePath)
					end repeat
				end repeat
			end try
		end tell
	end perform mail action with messages
end using terms from

-- Select the pdf invoice in the folder
-- ***************************
-- ***************************
tell application "Finder"
	set folder_1 to "Volumes:Macintosh HD:folder1:folder3:" as alias
	set folder_2 to "Volumes:Macintosh HD:folder1:folder3:" as alias
	set pdfFileName to name of item 1 in folder_epostas
end tell

-- Getting the client_id from the PDF invoice
-- ***************************
-- ***************************
tell application "Adobe Acrobat Pro"
	activate
	open "Volumes:Macintosh HD:folder1:folder3:" & pdfFileName
	tell active doc
		repeat with i from 1 to count of pages
			tell page i
				set {L, T, R, B} to media box
				set crop box to {L + 146, T - 194, R - 389, B + 655}
			end tell
		end repeat
	end tell
end tell
activate application "Adobe Acrobat Pro"
tell application "System Events"
	tell process "Acrobat"
		keystroke "a" using {command down}
		keystroke "c" using {command down}
	end tell
end tell
tell application "Adobe Acrobat Pro"
	activate
	close all docs saving "No"
end tell

-- Search the Excel spreadsheet for the client_id and email
-- **************************
-- **************************

tell application "Microsoft Excel"
	set file_path to "Volumes:Macintosh HD:folder1:folder3:export.xlsx"
	open file_path
	try
		set searchRange to range ("A2:B12000")
		set foundRange to find searchRange what (get (the clipboard)) look at whole with match case
		set fRow to first row index of foundRange
		set client_email to value of range ("C" & fRow as text)
	end try
end tell

if client_email = "post" then
	--tell application "HP LaserJet Pro MFP M521dw"
	--	activate
	--	print "Volumes:Macintosh HD:folder1:folder3:" & pdfFileName without print dialog
	--	quit
	--end tell
else
	tell application "Mail"
		
		set theSubject to "Invoice from.." -- the subject
		set theContent to "Content of mail here" -- the content
		set theAddress to client_email -- the receiver 
		set theSignatureName to "Invoice" -- the signature name
		set theAttachmentFile to "Volumes:Macintosh HD:folder1:folder2:" & pdfFileName -- the attachment file
		set theSender to "invoice@server.com"
		
		set msg to make new outgoing message with properties {subject:theSubject, content:theContent, visible:true, sender:theSender}
		
		tell msg to make new to recipient at end of every to recipient with properties {address:theAddress}
		tell msg to make new attachment with properties {file name:theAttachmentFile as alias}
		delay 1
		--set message signature of msg to signature theSignatureName
		
		send msg -- send the email message
	end tell
end if

-- Move the file after its beeing sent
-- **************************
-- **************************
--tell application "Finder"
--	move "Volumes:Macintosh HD:folder1:folder2:" & pdfFileName to "Volumes:Macintosh HD:folder1:folder3:" & pdfFileName
--end tell