Automate sending invoices

textexpander
hazel

#1

Is it possible to automate sending emails with an invoice attached?

The list of addressees, new each month, in Excel format, ordered by invoice number, contains the following data I need to utilise:

  • Addressee
  • Invoice number
  • Email address

The actual PDF invoices are provided to me all in a folder with no other files, named only with their invoice number.

The email needs to be formatted as follows:

  • From [1 email account of several on my Mac]
  • To [addressee’s email]
  • Subject “Invoice 0[invoice number] - i.e. the invoice number in the Excel sheet needs to have a leading “0” added in the subject line
  • Body: None, just the pdf of the invoice
  • Attachment: The corresponding monthly invoice
  • Signature: Special Invoicing signature

I have already made some shortcuts, but it’s still tedious and long.

  1. I made a BusyContacts group specifically for invoicing, but the Excel list isn’t exactly the same each month and is not created in alphabetical order. I use the shortcut ⌘M and it opens Mail (previously set up with the invoicing account as default) with the addressee filled in.

  2. I double check the physical invoice to make sure number and payee correspond to the list info.

  3. I use a TextExpander snippet with the subject line partially filled so I only have to type the last digits of the invoice number.

  4. I have to drag the corresponding PDF file onto the message

  5. I use ⌘D to send.

  6. I search for the next addressee and repeat the process.

Automation tools I have:

  • Keyboard Maestro, but don’t know how to use it.
  • Hazel
  • TextExpander
  • LaunchBar (?)

Can anyone tell me if I can set up a(n) (semi-)automated process?


#2

If you can open the spreadsheet in Numbers, this AppleScript should save you some time at least:

-- config
set theInvoicesFolder to "/path/to/the/invoices/"
-- end config

set myData to {}

-- get info from Numbers
tell application "Numbers"
	tell the first table of the active sheet of the front document
		repeat with i from 2 to the count of rows
			set the end of the myData to the value of cells 1 thru 3 of row i
		end repeat
	end tell
	stop
end tell

-- send the email
tell application "Mail"
	repeat with row in myData
		set recipient_name to the first item of row
		set invoice_number to the second item of row
		set recipient_email to the the third item of row
		
		-- fetch the file from the Finder
		tell application "Finder"
			set theFilePath to theInvoicesFolder & invoice_number & ".pdf"
			set theFile to theFilePath as POSIX file
		end tell
		
		set theSubject to "Invoice 0" & invoice_number
		set theBody to ""
		set theAddress to recipient_name & "<" & recipient_email & ">"
		set theAttachment to theFile
		
		set theNewMessage to make new outgoing message with properties {subject:theSubject, content:theBody, visible:true}
		tell theNewMessage
			set visibile to true
			make new to recipient at end of to recipients with properties {address:theAddress}
			try
				make new attachment with properties {file name:theAttachment}
				set message_attachment to 0
			on error errmess -- oops
				log errmess -- log the error
				set message_attachment to 1
			end try
			-- send
		end tell
	end repeat
end tell

I had some issues setting the signature and from-address, but maybe somebody who actually knows AppleScript can solve that later. Or maybe you can figure it out yourself, here’s a start at least. :slight_smile:

You can launch this script from either Keyboard Maestro or LaunchBar, whichever you prefer. Or just run it from Script Editor even!

Demo of the spreadsheet/invoices-folder I’ve used:

34

And that resulted in me having 5 windows open that looked like this:

If you remove the “–” before send the emails are sent automatically. But you’ll probably want to figure out the from/signature-thing first. And, you know, test the script to make sure everything works 100%.

Let me know if this helps!


#3

Oh! :open_mouth: Thanks so much for this @martijnengler.

The “from” and “account used” are easily solved by setting both to be the default, temporarily. Since it’s once a month, that’s not much of a pain :wink:

I’ll have a try at getting it working on some fake data next week.

On a more general note, does anyone have any suggestions as to where I could kickstart my learning to use Keyboard Maestro (tutorials, videos)?


#4

The User Manual / Keyboard Maestro Wiki is actually a great starting point. There’s an overview, feature review, tour and quick start.

After that just type Keyboard Maestro into YouTube and you’ll get lots of ideas and walk throughs.

As well as here, there’s also Keyboard Maestro’s own forum where the app developer (Peter) is typically very active; for times when you might not find an obvious answer online,


#5

You’re very welcome! :):grinning:

If I’m not mistaken @MacSparky is working on a Field Guide. Not sure when that will be available though.


#6

That should be January or soon after if I’m not mistaken!