Next post Previous post

Updating my invoicing email automation

February 21, 2017 at 11:12 PM by Dr. Drang

Last month, I described a script I used to create

The email part of the script assumed I was using MailMate, which was true at the time, but isn’t anymore. I needed to rewrite the script for Apple Mail.

Fortunately, that script was originally written for Apple Mail; I’d converted it to MailMate three years ago. Even more fortunately, I’d kept the original script around—all it needed was a little touching up and the addition of the Reminders section (Reminders didn’t exist when I wrote the first version of this script).

I’m not going to explain the inner workings of the script. The various posts linked to in the paragraph above do that. But here’s what it does in a nutshell:

  1. It extracts the text from the invoice (a PDF file) using Derek Noonburg’s Xpdf.
  2. From the text, it gets the project name, project number, due date, and amount of the invoice.
  3. It looks up, in a text file “database” of all my projects, the name of the client.
  4. It looks up the email address of the client in the Contacts app.
  5. It generates an email to the client with all the pertinent information and the invoice PDF attached.
  6. It creates a new reminder for me to follow up on the invoice in seven weeks.1

Here’s what the generated email looks like:

Invoice email

The script doesn’t send the email because sometimes I like to add a sentence or two before sending.

OK, here’s the invoice script:

python:
  1:  #!/usr/bin/python
  2:  
  3:  import os
  4:  import os.path
  5:  import sys
  6:  from applescript import asrun
  7:  from subprocess import check_output
  8:  
  9:  # Templates for the subject and body of the message.
 10:  sTemplate = '{0}: Drang invoice {1}'
 11:  bTemplate = '''Attached is Drang Engineering invoice {0} for {1} covering\
 12:   recent services on the above-referenced project. Payment is due {2}.
 13:  
 14:  Thank you for using Drang. Please call if you have any questions or need\
 15:   further information.
 16:  
 17:  --
 18:  Dr. Drang
 19:  leancrew.com
 20:  
 21:  '''
 22:  
 23:  # AppleScript template for getting project contact info.
 24:  cScript = '''
 25:  tell application "Contacts"
 26:    set contact to item 1 of (every person whose name contains "{}")
 27:    return value of item 1 of (emails of contact)
 28:  end tell
 29:  '''
 30:  
 31:  # AppleScript template for composing email.
 32:  mScript = '''
 33:    tell application "Mail"
 34:      activate
 35:      set newMsg to make new outgoing message with properties {{subject:"{0}", content:"{1}", visible:true}}
 36:      tell the content of newMsg
 37:        make new attachment with properties {{file name:"{4}"}} at after last paragraph
 38:      end tell
 39:      tell newMsg
 40:        make new to recipient at end of to recipients with properties {{name:"{2}", address:"{3}"}}
 41:      end tell
 42:    end tell
 43:  '''
 44:  
 45:  # AppleScript template for setting a reminder.
 46:  rScript = '''
 47:  set rem to "Invoice {} on {}"
 48:  set rmdate to (current date) + 49*days
 49:  tell application "Reminders"
 50:    tell list "Invoices"
 51:      set minder to (make new reminder with properties {{name:rem, remind me date:rmdate}})
 52:    end tell
 53:    activate
 54:    show minder
 55:  end tell
 56:  '''
 57:  
 58:  # Establish the home directory for later paths.
 59:  home = os.environ['HOME']
 60:  
 61:  # Open the project list file and read it into a string.
 62:  pl = open("{}/Dropbox/pl".format(home)).readlines()
 63:  
 64:  # Get the selected invoice PDF names from the command line.
 65:  pdfs = sys.argv[1:]
 66:  
 67:  # Make a new mail message for each invoice.
 68:  for f in pdfs:
 69:    f = os.path.abspath(f)
 70:  
 71:    # Use pdftotext from the xpdf project (http://foolabs.com/xpdf) to extract
 72:    # the text from the PDF as a list of lines.
 73:    invText = check_output(['/usr/local/bin/pdftotext', '-layout', f, '-'])
 74:  
 75:    # Pluck out the project name, project number, invoice number, invoice amount,
 76:    # and due date.
 77:    for line in invText.split('\n'):
 78:      if 'Project:' in line:
 79:        parts = line.split(':')
 80:        name = parts[1].split('  ')[0].strip()
 81:        invoice = parts[2].lstrip()
 82:      if 'project number:' in line:
 83:        number = line.split(':')[1].split()[0].lstrip()
 84:      if 'Invoice Total:' in line:
 85:        parts = line.split(':')
 86:        amount = parts[1].split()[0].strip()
 87:        due = parts[2].lstrip()
 88:  
 89:    # Get the email address of the client.
 90:    try:
 91:      client = [x.split('|')[2] for x in pl if number in x.split('|')[1]][0]
 92:      email = asrun(cScript.format(client))
 93:    except:
 94:      client = ''
 95:      email = ''
 96:  
 97:    # Construct the subject and body.
 98:    subject = sTemplate.format(name, invoice)
 99:    body = bTemplate.format(invoice, amount, due)
100:  
101:    # Create a mail message with the subject, body, and attachment.
102:    asrun(mScript.format(subject, body, client, email, f))
103:    
104:    # Add a reminder to the Invoices list.
105:    asrun(rScript.format(invoice, name))

The only thing you might find weird is how I execute AppleScript through Python. That’s done through a very simple library I wrote when Hamish Sanderson’s appscript library bit the dust. My applescript library is described in this post.

In addition to issuing a command like

invoice inv12345.pdf

in the Terminal, I can also run the script by right-clicking on the invoice PDF and choosing this Service, created in Automator:

Automator workflow

If a client doesn’t pay within seven weeks, I get a reminder and need to send a followup email. That gets generated with this very similar script, called dun:

python:
 1:  #!/usr/bin/python
 2:  
 3:  import os
 4:  import os.path
 5:  import sys
 6:  from applescript import asrun
 7:  from subprocess import check_output
 8:  from datetime import datetime, timedelta, date
 9:  
10:  # Templates for the subject and body of the message.
11:  sTemplate = '''{0}: Drang invoice {1}'''
12:  bTemplate = '''The attached invoice, Drang {0} for {1},\
13:   is still outstanding and is now {2} days old. Whatever you can\
14:   do to get it paid would be appreciated.
15:  
16:  Thank you for your attention. Please call if you have any questions or need\
17:   further information.
18:  
19:  --
20:  Dr. Drang
21:  leancrew.com
22:  
23:  '''
24:  
25:  # AppleScript template for getting project contact info.
26:  cScript = '''
27:  tell application "Contacts"
28:    set contact to item 1 of (every person whose name contains "{}")
29:    return value of item 1 of (emails of contact)
30:  end tell
31:  '''
32:  
33:  # AppleScript template for composing email.
34:  mScript = '''
35:    tell application "Mail"
36:      activate
37:      set newMsg to make new outgoing message with properties {{subject:"{0}", content:"{1}", visible:true}}
38:      tell the content of newMsg
39:        make new attachment with properties {{file name:"{4}"}} at after last paragraph
40:      end tell
41:      tell newMsg
42:        make new to recipient at end of to recipients with properties {{name:"{2}", address:"{3}"}}
43:      end tell
44:    end tell
45:  '''
46:  
47:  # Establish the home directory for later paths.
48:  home = os.environ['HOME']
49:  
50:  # Open the project list file and read it into a string.
51:  pl = open("{}/Dropbox/pl".format(home)).readlines()
52:  
53:  # Get the selected invoice PDF names from the command line.
54:  pdfs = sys.argv[1:]
55:  
56:  # Make a new mail message for each invoice.
57:  for f in pdfs:
58:    f = os.path.abspath(f)
59:  
60:    # Use pdftotext from the xpdf project (http://foolabs.com/xpdf) to extract
61:    # the text from the PDF as a list of lines.
62:    invText = check_output(['/usr/local/bin/pdftotext', '-layout', f, '-'])
63:  
64:    # Pluck out the project name, project number, invoice number, invoice amount,
65:    # and due date from the upper portion of the first page.
66:    for line in invText.split('\n')[:20]:
67:      if 'Project:' in line:
68:        parts = line.split(':')
69:        name = parts[1].split('  ')[0].strip()
70:        invoice = parts[2].lstrip()
71:      if 'project number:' in line:
72:        number = line.split(':')[1].split()[0].lstrip()
73:      if 'Invoice Total:' in line:
74:        parts = line.split(':')
75:        amount = parts[1].split()[0].strip()
76:        due = parts[2].lstrip()
77:  
78:    # Get the email address of the client.
79:    try:
80:      client = [x.split('|')[2] for x in pl if number in x.split('|')[1]][0]
81:      email = asrun(cScript.format(client)).strip()
82:    except:
83:      client = ''
84:      email = ''
85:    addr = "{0} <{1}>".format(client, email)
86:  
87:    # Determine the age of the invoice.
88:    dueDate = datetime.strptime(due, '%B %d, %Y')
89:    dunDate = datetime.today()
90:    age = (dunDate - dueDate).days + 30     # invoices are net 30
91:  
92:    # Construct the subject and body.
93:    subject = sTemplate.format(name, invoice)
94:    body = bTemplate.format(invoice, amount, age)
95:    
96:    # Create a mail message with the subject, body, and attachment.
97:    asrun(mScript.format(subject, body, client, email, f))

There’s a bit of date calculation in the script in Lines 87–90, which allows me to point out (gently) the degree of delinquency.

Dunning email

There’s a Service that runs this script, too. It looks just like the one above but for the name of the script.

In last night’s post, I said Apple deserved credit for maintaining Mail’s plugin system, even though it seems antithetical to Apple’s current app philosophy. The same goes for Mail’s AppleScript support.

  1. When invoices get paid, I delete the associated reminder. This process hasn’t been automated… yet. ↩

Next post Previous post