I don’t know who we have to thank at Intuit for the relatively new appearance the invoice emails, but like most of the people I work with I am in the professional services business, doing my best to be professional. Sending out invoices using QuickBooks’ new invoice emails makes that difficult. Invoices sent to clients are a recurring touchpoint and an opportunity to create an impression – good or bad. Intuit’s invoice emails are making everyone using them look like clowns.
I don’t need an invoice email that tells my clients that “Your Invoice is Ready!” Why else would I send an email with their invoice? I also don’t need to tell my clients to please pay their invoice on time. Nor have I ever considered centering the text of an email with a salutation and closing. I can see Intuit’s name on my email, but where is my company name? Why is the day of the week the invoice is due included? That’s superfluous. And last, but not least, why would I want overcooked pea-soup to be the background of my email. Who on earth is responsible for this? Was AI used to assist? I think so.
If your firm is still using QuickBooks Desktop or Enterprise, you may be frustrated by the look of the new invoice emails that were implemented and locked-down to some degree regardless of what you might want them to look like. I know I was. Towards the end of last year, the format of the QuickBooks invoice emails changed suddenly and without warning. Look, these emails were never awe-inspiring, but they had a semi-professional … not too awful look that we could work with.
I’ll admit that I sent out a couple of the newer invoice emails with some edits to prevent me from appearing like a complete clown, but it was still embarrassing. In my experience, the invoices reverted back to the old format the next month and I breathed a sigh of relief to regain the stale, but professional-looking invoices I have sent via email for years.
Unfortunately, that relief was short-lived. In February, the invoice emails changed back to the god-forsaken format again (minus the part about paying the invoice on time), and now, if didn’t do something to address the issue, my invoicing was going to look even worse. Not only would I appear to have no design or business sense, but now our invoice-related buffoonery was going to include vacillating between multiple invoice email formats every month. I called Intuit to air my frustration and hoped that they could help resolve the issue.
It turns out I wasn’t alone. There were a lot of people that were unhappy about their inability to control the format of the invoice emails, and Intuit confirmed it. We discussed the issue at length, and the representative told me there was nothing they could do to fix it … so, I fixed it. And if we were dealing with any software company but Intuit that would probably be the end of the story.
The fix that worked until Intuit changed the email invoicing format again in April. 😐
I created Outlook VBA code to remove the day of the week, eliminate red-orange color on the date, remove the “Your invoice is ready!” and replace it with my company name, add logic to remove the cents (.00), left align the text of the message in the body of the email, change the color of the font and the background, and automatically have it adjust the due date to first day of the following month; then I customized the Quick Access Toolbar to create an icon you can click on to run the automation. The code below lets you update the invoices to make them appear more professional.
Sub FixQBEmail()
LeftJustifyInvoicePara
ProofAndCleanEmail
End Sub
Sub ProofAndCleanEmail()
Dim oInspector As Inspector
Dim oItem As MailItem
Dim oDoc As Object
Dim sBody As String
' Get the currently open/active email being composed
Set oInspector = Application.ActiveInspector
If oInspector Is Nothing Then
MsgBox "No email is currently open for editing.", vbExclamation
Exit Sub
End If
If oInspector.CurrentItem.Class <> olMail Then
MsgBox "The active item is not an email.", vbExclamation
Exit Sub
End If
Set oItem = oInspector.CurrentItem
' --- Work on the BODY ---
' For HTML emails, we modify the plain text representation carefully.
' Using Word editor object for rich/HTML body editing is more robust.
If oInspector.EditorType = olEditorWord Then
' Email is using Word as editor (default in modern Outlook)
Set oDoc = oInspector.WordEditor
Call CleanWordDocument(oDoc)
Else
' Fallback: work on plain text body
sBody = oItem.Body
sBody = CleanDollarAmounts(sBody)
oItem.Body = sBody
End If
'MsgBox "Email proofing complete!", vbInformation
End Sub
' -------------------------------------------------------
' Cleans a Word document (the email body in Word editor)
' -------------------------------------------------------
Sub CleanWordDocument(oDoc As Object)
Dim oTable As Object
Dim oCell As Object
Dim oRange As Object
Dim oFind As Object
Dim i As Integer, j As Integer, k As Integer
' First try Find/Replace on the whole document content
Call FindReplaceDollars(oDoc.Content)
Call ReplaceText(oDoc.Content, "Your invoice is ready!", "Quartare")
Call FixDateLineColor(oDoc.Content)
Call RemoveDayOfWeek(oDoc.Content)
Call UpdateToFirstOfNextMonth(oDoc.Content)
'Call ColorDateGreen(oDoc.Content)
Call FixHTMLBackgrounds
' Then explicitly loop through all tables and cells
For i = 1 To oDoc.Tables.Count
Set oTable = oDoc.Tables(i)
For j = 1 To oTable.Rows.Count
For k = 1 To oTable.Columns.Count
On Error Resume Next ' some cells may be merged
Set oCell = oTable.Cell(j, k)
If Err.Number = 0 Then
Call FindReplaceDollars(oCell.Range)
End If
On Error GoTo 0
Next k
Next j
Next i
End Sub
Sub FindReplaceDollars(oRange As Object)
Dim oRegex As Object
Dim oMatches As Object
Dim oMatch As Object
Dim sFind As String
Dim sReplace As String
' Use RegEx to identify what needs replacing
Set oRegex = CreateObject("VBScript.RegExp")
With oRegex
.Global = True
.Pattern = "\$([0-9,]+)\.00"
End With
Set oMatches = oRegex.Execute(oRange.Text)
' For each match, do a safe literal Word find/replace
For Each oMatch In oMatches
sFind = oMatch.Value ' e.g. $250.00
sReplace = "$" & oMatch.SubMatches(0) ' e.g. $250
With oRange.Find
.ClearFormatting
.Replacement.ClearFormatting
.MatchWildcards = False
.Forward = True
.Wrap = 1
.Text = sFind
.Replacement.Text = sReplace
.Execute Replace:=2
End With
Next oMatch
End Sub
Sub ReplaceText(oRange As Object, sFind As String, sReplace As String)
With oRange.Find
.ClearFormatting
.Replacement.ClearFormatting
.MatchWildcards = False
.Forward = True
.Wrap = 1
.Text = sFind
.Replacement.Text = sReplace
.Execute Replace:=2
End With
End Sub
Sub FixDateLineColor(oRange As Object)
Dim oPara As Object
Dim oWord As Object
For Each oPara In oRange.Paragraphs
If InStr(oPara.Range.Text, "| Due ") > 0 Then
' Loop through each word/run in the paragraph and set to white
For Each oWord In oPara.Range.Words
oWord.Font.Color = RGB(50, 50, 50) ' KS white?
Next oWord
End If
Next oPara
End Sub
Sub FixHTMLBackgrounds()
Dim oInspector As Inspector
Dim oItem As MailItem
Dim sHTML As String
Set oInspector = Application.ActiveInspector
Set oItem = oInspector.CurrentItem
sHTML = oItem.htmlBody
' Fix background colors - replace with whatever colors you want
'sHTML = Replace(sHTML, "bgcolor=""#ECEEF1""", "bgcolor=""#FFFFFF""") ' outer body
sHTML = Replace(sHTML, "background:#F4F4EF", "background:#7D99B6") ' top section
sHTML = Replace(sHTML, "background:#F4F5F8", "background:#F4F5F8") ' footer section
' Fix the red date color while we're here
'sHTML = Replace(sHTML, "color:#D52B1E", "color:#393A3D") ' match surrounding text
oItem.htmlBody = sHTML
'MsgBox "Background colors updated.", vbInformation
End Sub
Sub RemoveDayOfWeek(oRange As Object)
Dim sDays As String
Dim oDays() As String
Dim i As Integer
sDays = "on Mon, |on Tue, |on Wed, |on Thu, |on Fri, |on Sat, |on Sun, "
oDays = Split(sDays, "|")
For i = 0 To UBound(oDays)
Call ReplaceText(oRange, oDays(i), "")
Next i
End Sub
Sub UpdateToFirstOfNextMonth(oRange As Object)
Dim oRegex As Object
Dim oMatches As Object
Dim oMatch As Object
Dim sOldDate As String
Dim sNewDate As String
Dim dDate As Date
Dim dNewDate As Date
' Use RegEx to find MM/DD/YYYY pattern
Set oRegex = CreateObject("VBScript.RegExp")
With oRegex
.Global = True
.Pattern = "\d{2}/\d{2}/\d{4}"
End With
Set oMatches = oRegex.Execute(oRange.Text)
If oMatches.Count = 0 Then
MsgBox "No date found.", vbExclamation
Exit Sub
End If
For Each oMatch In oMatches
sOldDate = oMatch.Value
' Parse the found date
dDate = CDate(sOldDate)
' Calculate first day of next month
dNewDate = DateSerial(Year(dDate), Month(dDate) + 1, 1)
' Format back to MM/DD/YYYY
sNewDate = Format(dNewDate, "MM/DD/YYYY")
' Use safe Word find/replace
Call ReplaceText(oRange, sOldDate, sNewDate)
Next oMatch
End Sub
Sub ColorDateGreen(oRange As Object)
Dim oPara As Object
Dim oRegex As Object
Dim oMatches As Object
Dim oMatch As Object
Dim oFindRange As Object
Set oRegex = CreateObject("VBScript.RegExp")
With oRegex
.Global = True
.Pattern = "\d{2}/\d{2}/\d{4}"
End With
For Each oPara In oRange.Paragraphs
Set oMatches = oRegex.Execute(oPara.Range.Text)
For Each oMatch In oMatches
' Create a range for just the date
Set oFindRange = oPara.Range.Duplicate
oFindRange.Start = oPara.Range.Start + oMatch.FirstIndex
oFindRange.End = oFindRange.Start + oMatch.Length
' #D86C00 converts to RGB(216, 108, 0)
oFindRange.Font.Color = RGB(0, 137, 46)
Next oMatch
Next oPara
End Sub
Sub LeftJustifyInvoicePara()
Dim oInspector As Inspector
Dim oDoc As Object
Dim oCell As Object
Dim oPara As Object
Set oInspector = Application.ActiveInspector
Set oDoc = oInspector.WordEditor
Set oCell = oDoc.Tables(1).Cell(1, 1)
For Each oPara In oCell.Range.Paragraphs
' Find the paragraph that contains the invoice message
If InStr(oPara.Range.Text, "Please remit payment") > 0 Then
oPara.Alignment = 0
End If
Next oPara
End Sub
We also could have eliminated the need to click on button to reformat invoice emails and call the FixQBEmail process from within the existing QuickBooks Outlook integration by intercepting outgoing Outlook email via Application.ItemSend / MailItem.Send event before the message leaves Outlook.
Another alternative to this, if you need a greater degree of control, would be the following workflow:
- Batch-create invoices in QuickBooks.
- Send them to a mailbox you control.
- Use automation to extract the hosted invoice URL from each email.
- Generate your own cleaner outbound email using that URL.
- Send them out from your own domain/mailbox.
I still believed the invoice emails we sent out before Intuit changed their default appearance were more professional looking, but this code at least succeeded in taking something that looked utterly ridiculous and made it look less so. I was willing to call it a win at the time. However, Intuit had other plans.
The latest QuickBooks change, which nearly restores the original email invoice format from five months ago, does little to reassure me that the invoice emails generated in the future will look similar. Instead, I am convinced that users like me have absolutely no persistent control over how these invoice emails will look when created directly through QuickBooks and that is not acceptable. The updates to invoice emails are occurring without any permission or approval from QuickBooks users and do not appear to be connected in any way to software updates that users perform.
It may be tempting to believe that Intuit has finally resolved this snafu, but after the frustration of dealing with the issue repeatedly over a period of months I doubt many users will be able to trust Intuit further in this regard. Professional services firms need adequate controls on client-facing communications and the only way they will get those controls where Intuit’s invoice emails are concerned is by moving away from QuickBooks altogether or creating their own internal processes to compensate for Intuit’s shortcomings.
About the Author: Kevin Shea is the Founder and Principal Consultant of Quartare; Quartare provides a wide variety of agile technology solutions to investors and the financial services community at large.
For details, please visit Quartare.com, contact Kevin Shea via phone at 617-720-3400 x202 or e-mail at kshea@quartare.com.