AppleScripting QuickBooks 2009 Mac

Let's suppose we have an invoice open and would like to mail a copy to the customer along with some other information. We'll assume that we have a customer invoice open:

set theReply to display dialog "Open an existing Quickbooks invoice." with icon note buttons icon note buttons ["Abort Script", "Run it!"] default button 2

if (button returned of theReply is "Abort Script") then return

-- Make sure we have an invoice open!

if the name of window 1 of application "QuickBooks 2009" does not contain "Invoice" then

set theReply to display dialog "Dude, I don't see an invoice open." with icon note buttons ["Oops"]

return

end if

-- Here's another handy way to monitor progress and debug. Stick a line like this wherever you want!

say "Reading invoice" using "Victoria"

-- Make sure we are on the Customer:Job field of an invoice.

tell application "QuickBooks 2009"

activate

delay 0.4

-- get the window coordinates

set theCoordinates to the bounds of window 1

tell me

-- move to the Customer:Job field and triple click it to select the customer's name.

move mouse {(item 1 of theCoordinates) + 160, (item 2 of theCoordinates) + 45} -- requires the XTools OSAX; see page 1 of this tutorial

delay 0.2

click mouse

click mouse

click mouse

end tell

-- Copy the customer's name to the clipboard.

tell application "System Events"

keystroke "c" using {command down}

set theCustomer to the clipboard

-- "Print" (Save) a copy of the invoice as a PDF file.

keystroke "p" using {command down}

delay 1

-- Instead of delay 1 you could have used the QuickbooksReady routine dicussed earlier.

click menu button "PDF" of window "Print" of process "QuickBooks 2009"

click menu item "Save As PDF…" of menu 1 of menu button "PDF" of window "Print" of process "QuickBooks 2009"

delay 0.2

keystroke "d" using {command down} -- Sets destination to the desktop

keystroke "Invoice.pdf"

delay 0.2

click button "Save" of window "Save" of process "QuickBooks 2009"

delay 0.7

keystroke "w" using {command down} -- Close the invoice window

-- Now we open the Customer Center window, enter the customer name, and double click on it so we can access our customer's information.

keystroke "J" using {command down, shift}

keystroke tab

keystroke theCustomer

set theCoordinates to the position of row 1 of outline 1 of scroll area 1 of group 1 of splitter group 1 of window 1 of process "QuickBooks 2009" -- Hahahahaha, good one! There are other ways to do this; this is the most direct but difficult to figure out on your own.

move mouse theCoordinates -- requires the XTools OSAX; see page 1 of this tutorial

click mouse

click mouse

delay 0.2

--The Customer information pane should now be open. We'll grab the first name, last name, and email address.

set firstName to (value of every text field of tab group 1 of window theCustomer of application process "QuickBooks 2009" whose value of attribute "AXDescription" is "First Name") as text

set lastName to (value of every text field of tab group 1 of window theCustomer of application process "QuickBooks 2009" whose value of attribute "AXDescription" is "Last Name") as text

set eMail to (value of every text field of tab group 1 of window theCustomer of application process "QuickBooks 2009" whose value of attribute "AXDescription" is "EMail") as text

keystroke "w" using {command down} -- Done. Close the customer info window

end tell

end tell

We have now exported the invoice as well as grabbed the customer's first name, last name, and email address. Now we will construct the body of our personalized email message and then we will tell Apple Mail to prepare the message for us.

set myBody to "Dear" & firstName & "," & return & return

set myBody to myBody & "The invoice for your recent order is attached as a PDF file. We'd like to thank you for your business, " & firstName & ", and look forward to serving you again in the future." & return & return

set myBody to myBody & "Sincerely," & return & return

set myBody to myBody & "Your Name Here" & return

-- Replace "MyHardDriveName" and "MyUserName" in this next line, of course

set invoiceFile to "MyHardDriveName:Users:MyUserName:Desktop:Invoice.pdf"

tell application "Mail"

activate

-- Create a new message

set newMessage to make new outgoing message at end of outgoing messages with properties {visible:true, content:myBody}

set the message signature of newMessage to signature "Your Signature Name in Mail"

delay 0.4

tell newMessage

try

make new to recipient at end of to recipients with properties {address:eMail}

end try

try

set the sender to "My Name <myname@mycompany.com>" -- This must be formatted just as it appears in the From: field of new messages you create in Mail.

end try

try

set the subject to "MyCompanyName invoice copy for " & firstName & space & lastName

end try

try

make new cc recipient at end of cc recipients with properties {address:"myname@mycompany.com"} -- you can use the "short form" of your email address here

end try

try

tell content to make new attachment with properties {file name:(invoiceFile as alias)} at after last paragraph

end try

end tell

delay 1

end tell

And that's it. Your custom email message with the invoice attachment should be open and ready for you to review before sending.

You'll notice that we used a string of try statements with Mail. That's simply so that if any one of those fails, the rest of the message will still be constructed, and it makes debugging a lot easier. You could use just one try construction within or around the "tell Mail...end tell" block if you prefer.

You can print statements and reports from QuickBooks 2009 Mac in the same way that we did for Invoices as shown above. Here are the specific lines of code you need to call up a specific customer's statement:

tell application "QuickBooks 2009"

activate

tell application "System Events"

click menu item "Statements…" of menu 1 of menu item "Print Forms" of menu 1 of menu bar item "File" of menu bar 1 of process "QuickBooks 2009" -- the … is an ellipsis, not 3 periods!)

delay 0.4

keystroke tab

keystroke tab

keystroke tab

keystroke theCustomer

delay 1

click button "OK" of window "Select Statements to Print" of process "QuickBooks 2009"

delay 0.5

end tell

end tell

From that point on, the procedure for saving the statement as a PDF is the same as we saw previously for invoices. In fact, the printing process would make a good subroutine. Notice also that we could change the date range if we wanted to. Although it is beyond the scope of this tutorial, you could set the starting date to the first of the year or 90 days in the past if you wished to.

Here's the code to call up a memorized report.

set reportName to "MyReport"

tell application "QuickBooks 2009"

activate

tell application "System Events"

click menu item "Memorized Reports" of menu 1 of menu bar item "Reports" of menu bar 1 of process "QuickBooks 2009"

delay 0.2

keystroke tab

delay 0.2

keystroke reportName

delay 0.2

set theCoordinates to the position of row 1 of outline 1 of scroll area 1 of window "Memorized Report List" of process "QuickBooks 2009"

move mouse theCoordinates -- requires the XTools OSAX; see page 1 of this tutorial

click mouse

click mouse

delay 0.2

end tell

end tell

From there, you can simulate pressing command-P to print as PDF just as we saw in the Invoice example above. Some examples of how to script non-memorized reports are given on Examples Page 1. In general, if you are scripting a report, then you should make that report a template by memorizing it.