Automating Wave Accounting, Mail and Calendar

Time for a bit of a technology related, geeky post about how I’ve recently automated part of my workflow thanks to Mail Rules, Folder Actions, Automator and AppleScript. As you might know from a previous post, I’m in the process of transferring to a new accounting solution called Wave.

As part of the transition I thought I’d try to streamline my workflow a little by automating some of the processes. Afterall, if I can save some time when sending invoices, processing payments and recording receipts then I can spend more time actually working.

My previous workflow was:

The Workflow

Invoicing

  1. Create an invoice in my accounting software
  2. Save the invoice as a PDF file
  3. Email the PDF file to my client
  4. Add a Calendar event to say that I’d sent the PDF
  5. Add alarms to the Calendar Event to remind me that I’d sent it 30 days later

Payment

When I received payment I would

  1. Record the payment in my accounting software
  2. Create a Receipt
  3. Save the Receipt as a PDF file
  4. Email the PDF file to my client
  5. Find the Calendar event
  6. Remove the alarms from the calendar

Late Payment

If the client hadn’t paid I would get a Calendar alarm and email 30 days after sending the invoice. At this stage I would

  1. Create an reminder invoice in my accounting software
  2. Save the reminder invoice as a PDF file
  3. Email the PDF file to my client
  4. Add a Calendar event to say that I’d sent the PDF
  5. Add alarms to the Calendar Event to remind me that I’d sent it 15 days later

and the process would start again. It was fairly simple, it worked but when doing it over and over again it dod become a little tiresome.

Enter Wave

Wave itself streamlined this process a little as I could create the invoice and send it directly from within Wave. I could then set an automatic reminder to be sent on the due date. That helped a little but I wasn’t entirely happy leaving it all to a third part app on the Internet. I wanted some control and I wanted to keep records of invoices and payments locally as well.

I wanted to:

  1. Store the PDF files locally as well as within Wave
  2. Add reminders via calendar alarms so that I could check the status of an invoice before an automatic reminder was sent.

With a little bit of integration between Wave, some Mail Rules and some AppleScript I was able to do all of this.

Processing Emails

The secret to this was processing the emails I get from Wave. When sending an invoice from Wave it’s possible to attach a pdf of the invoice to the email and also send a copy of that to yourself as well as sending it to the client. This email always comes from the same address and has a similar subject line:

“Invoice #XX from Company Name”

where XX is the number of the invoice and Company Name is the name of your company.

Mail Rules

Knowing this information makes it easy to set up a Rule within Apple Mail. You do this by going to Preferences from the menu and selecting the Rules tab. Here you can add a new rule.

I set up a rule that is triggering when certain conditions are met. Now, whenever an email containing an invoice from Wave is received this mail Rule will be triggered. The rule itself performs several actions

  1. It Moves the email to a ‘Sent Invoices’ mailbox (so that I don’t even have to see it).
  2. It marks the message as read
  3. It runs an AppleScript that creates a Calendar event for me.

That was all pretty simple and straight forward. The real automation takes place within the AppleScript.

AppleScripting Calendar Events from Mail Messages

This was the fun part. I wanted to build an AppleScript that would extract some information from the email that had triggered it. Add this information to a Calendar Event and create Alarms on the Calendar Event. I was fairly new to AppleScript so it took some trial and error, but I think I have it sorted now.

Here’s the AppleScript that is triggered by the Mail Rule

-- Triggered by Mail rule.
using terms from application "Mail"
	on perform mail action with messages msgs for rule theRule
		tell application "Mail"
			repeat with msg in msgs
				try
					set msgsubject to subject of msg
					set msgcontent to content of msg
					set msgid to message id of msg
					set {amount, dueon} to my parseMsg(msgcontent)
					my createEvent(msgsubject, msgid, amount, dueon)
				end try
			end repeat
		end tell
	end perform mail action with messages
end using terms from

It’s fairly simple.

  • It simply sets the subject, content and id of the message into three sensibly named variables.
  • It then runs a function called parseMsg to extract some details from the content of the message and sets the ‘amount due’ and ‘due date’ into two more variables.
  • Finally it runs another function called createEvent to create a Calendar Event

Parsing the email with AppleScript

The parseMsg function was modified from an example script elsewhere.

It simply looks for given strings within the email and extracts the text from between them. It then trims any whitespace from the ends of the string and sets the strings into the variables ‘amount’ and ‘dueon’. Here’s the AppleScript used by this function.

-- Parse the email content to extract invoice details.
on parseMsg(msgcontent)
	set amount to extractBetween(msgcontent, "£", "pence")
	set dueon to extractBetween(msgcontent, "Due on:", "For questions")
	return {amount, dueon}
end parseMsg

-- Extract the substring from between two strings
to extractBetween(theString, startText, endText)
	set tid to AppleScript's text item delimiters
	set AppleScript's text item delimiters to startText
	set startComps to text items of theString
	set AppleScript's text item delimiters to endText
	set endComps to text items of second item of startComps
	set AppleScript's text item delimiters to tid
	return trim(first item of endComps)
end extractBetween

-- Trim all whitespace from start and end of a string
on trim(theString)
	set theChars to {" ", tab, character id 10, return, character id 0, character id 8232}
	repeat until first character of theString is not in theChars
		set theString to text 2 thru -1 of theString
	end repeat
	repeat until last character of theString is not in theChars
		set theString to text 1 thru -2 of theString
	end repeat
	return theString
end trim

With that done, my script now has strings for:

  • the message subject
  • the message id
  • the amount specified in the invoice
  • the due date of the invoice

Creating a Calendar Event with AppleScript

I could now build a function called createEvent that would create a calendar event based on this data.

Here’s the AppleScript for this

-- Create a calendar event for the specified invoice.
on createEvent(msgsubject, msgid, amount, dueon)
	set startdate to (current date) + 28 * days
	set sentdate to current date
	-- set enddate to startdate
	tell application "Calendar" to tell calendar "MyCalendar"
		set theEvent to make new event with properties {start date:startdate, summary:"Check " & msgsubject, allday event:true}
		set description of theEvent to amount & return & "Due on: " & dueon & return & "Sent on: " & sentdate
		set url of theEvent to "message:" & "%3c" & msgid & "%3e"
		delay 5
		tell theEvent
			-- Add a email alarm
			make new mail alarm at end of mail alarms with properties {trigger interval:10}
			delay 5
			make new sound alarm at end of sound alarms with properties {trigger interval:370, sound name:"Sosumi"}
		end tell
	end tell
end createEvent

Again, it’s fairly simple even if it did take some trial and error to get working properly. This is what is does.

  • It sets a ‘startdate’ variable which is 28 days after the current date. This is the date that I want the Reminder to be sent to me and therefore the date that I want the Calendar event created on.
  • It also set a ‘sentdate’ variable to the current date.
  • Next it tells the Calendar app to create a new all day event. This event has various properties set:
    • The date is set using the startdate variable
    • The title of the event (summary) is set to a string that includes the subject of the email using the msgsubject variable
    • The event is set to be an all day event
  • Next, the description of the event is added. This goes into the notes section of a Calendar event and in this case includes the Amount that the invoice was for, the date it is due and the date it was sent. Unfortunately the email from Wave doesn’t contain the client name which is a shame otherwise I would have added this to the description as well.
  • Next a URL is added to the Calendar Event. This uses the ‘msgid’ variable and provides a link back to the original email that triggered the AppleScript. This allows me to easily find the actual invoice that was sent to a client directly from the Calendar Event.
  • Finally two alarms are added to the Calendar event, and email alarm and a sound alarm so that I get notified that and invoice hasn’t been paid just before it becomes overdue.

It’s all fairly simple, but it does mean that I don’t have to do anything in the invoicing workflow other than send the invoice to a client from within Wave. Once I do that an email is also sent ot me that triggers the mail rule, files the email, marks it as read and then runs the AppleScript to create a Calendar event and set up some Calendar alarms.

With that all in place I’ll get an email 28 days after sending the invoice reminding me to check on it’s status. This means that if a client has paid and I hadn’t recorded the payment I can do so before an automatic reminder is sent out.

That was the invoicing part of my workflow set up and quite a few steps saved. I was enjoying this automation so thought I’d take it a little further. Next on my list was the Payments part of the workflow.

Payments

This was actually a fairly similar process, but in reverse. When I recorded that an invoice had been paid within Wave I simply had to click on the ‘Send Receipt’ button to send a receipt to my client and a copy of that receipt to myself. I set up a separate Mail Rule that would be triggered when this email was received and it would once again perform actions to file the email, mark it as read and run a different AppleScript. This AppleScript would find the Calendar Event created by the Invoice Script above. It would then remove the alarms from the Calendar event and add the date that the invoice was paid to the description.

The AppleScript itself follows the same format as the one for the Invoice. Here it is in its entirety

-- Triggered by Mail rule.
using terms from application "Mail"
	on perform mail action with messages msgs for rule theRule
		tell application "Mail"
			repeat with msg in msgs
				try
					set msgcontent to content of msg
					set {invoicenumber} to my parseMsg(msgcontent)
					my removeAlarm(invoicenumber)
				end try
			end repeat
		end tell
	end perform mail action with messages
end using terms from

-- Parse the email content to extract invoice details.
on parseMsg(msgcontent)
	set invoicenumber to extractBetween(msgcontent, "Invoice #", "for")
	return {invoicenumber}
end parseMsg

-- Extract the substring from between two strings
to extractBetween(theString, startText, endText)
	set tid to AppleScript's text item delimiters
	set AppleScript's text item delimiters to startText
	set startComps to text items of theString
	set AppleScript's text item delimiters to endText
	set endComps to text items of second item of startComps
	set AppleScript's text item delimiters to tid
	return trim(first item of endComps)
end extractBetween

-- Trim all whitespace from start and end of a string
on trim(theString)
	set theChars to {" ", tab, character id 10, return, character id 0, character id 8232}
	repeat until first character of theString is not in theChars
		set theString to text 2 thru -1 of theString
	end repeat
	repeat until last character of theString is not in theChars
		set theString to text 1 thru -2 of theString
	end repeat
	return theString
end trim


-- Remove Alarm from specified calendar event.
on removeAlarm(invoicenumber)
	set receiptdate to current date
	tell application "Calendar"
		tell calendar "myCalendar"
			set thisevent to first event where its summary = "Invoice #" & invoicenumber
			set description of thisevent to description of thisevent & return & "Paid: " & receiptdate
			tell thisevent
				delete mail alarms of thisevent
				delay 10
				delete sound alarms of thisevent
			end tell
		end tell
	end tell
end removeAlarm
  • It uses the same parseMsg function to extract the invoice number from the receipt email.
  • It then uses this invoice number to find the Calendar Event pertaining to that invoice.
  • Next it updates the description of the Calendar Event to the include the date it was paid
  • Finally it removes the alarms from the event.

I had to add some delay into it as for some reason the script didn’t always remove both alarms. Once I had it working, my workflow for recording a payment was simply to record the payment and send a receipt.

So far so good. Invoicing had gone:

Recording payments had gone:

What’s more, I’d had some ‘fun’ setting it up and learnt some new skills too. Late payments should in theory take care of themselves as Wave should send out reminders itself if an invoice hasn’t been paid so that has cut that workflow out completely. Although, as I’ve yet to see what happens when an invoice hasn’t been paid I might have to re-visit it at some point. I guess ideally I’ll get a copy of the reminder email as well and will be able to use it to set up a new Calendar Event.

Filing of Receipts with Folder Actions

You may have noticed that I mentioned Folder Actions and Automator in the first line of this blog post but haven’t actually used them so far to implement any of this automation. Well, Wave will also allow you to record expenses by uploading receipts. It then processes these receipts and adds the expense to your accounts. As well as manually uploading the receipt to your Wave account, you can send it via email.

I’ve therefore set up a Folder on my computer with a Folder Action that runs an Automator Workflow. Whenever a new item is added to the folder the automator Workflow is invoked. The Automator Workflow simply creates an email, attaches the new item that was added to the folder to the email and sends it to my Wave account. Once there Wave processes it and adds it to my expenses ready for me to approve. Just an additional little bit of automation to make my life easier.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Avatar forComment Author

Alan Cole

Alan is a Freelance Website Designer, Sports & Exercise Science Lab Technician and full time Dad & husband with far too many hobbies: Triathlete, Swimming, Cycling, Running, MTBing, Surfing, Windsurfing, SUPing, Gardening, Photography.... The list goes on.

You may also like...