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.
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.
I double check the physical invoice to make sure number and payee correspond to the list info.
I use a TextExpander snippet with the subject line partially filled so I only have to type the last digits of the invoice number.
I have to drag the corresponding PDF file onto the message
I use ⌘D to send.
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?
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.
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:
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%.
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,
Thanks so much for this 
