Need help reformatting excel data in clipboard to email message body

Scripting Forums AppleScript | Mac OS X
,
#1

First of all, let me say that although I’ve been using Macs for over 15 years (my first Mac was a SE-30) I am embarrassed to say that I am a total n00b to Applescript and Automator. Thank you to everyone who posts regularly on these forums. I’ve been reading for a couple of days and there is a lot of great information.

I’d like to explain what I want my script to do and if someone could point me in the right direction or even tell me that I am barking up the wrong tree with Applescript then I would be grateful. It’ seems totally possible after reading all of the info on this site but i’m not any closer to a solution after a couple of days or reading and messing around.

Every day I have a csv file in Excel 2008. It contains a list of orders that I need to ship, one order per row. The columns are always in this order: OrderID,Quantity,PartNumber,FirstName,LastName,Company,AddressLine1,AddressLine2,City,State,Zip,Country,EmailAddress,PhoneNumber.

I’d like to select one or more rows of the spreadsheet and then right-click and have a script be available that will:
1.) copy the selection to the clipboard (or create a variable in the script as being the selection)

2.) reformat the clipboard contents so that instead of it all being on one line with the text from each cell separated (presumably) by tabs, it is formatted as:
“OrderID”
“Quantity” “PartNumber”
“FirstName” "LastName
“Company”
“AddressLine1”
“AddressLine2”
“City” “State” “Zip”
“Country”
“EmailAddress”
“PhoneNumber”

(Ex.):
1068773930
1 2K4-MUSIC-STAND
John Smith
Widgets R Us
123 street
suite 6B
yourtown, CA, 92009
United States
888-555-1212
johnsmith@isp.com

3.) create a new email message in Entourage using the main email account with a recipient “shippingdepartment@mycompany.com” and a subject of “add this order to todays shipping”

4.) paste the contents of the clipboard into the body of this message

The creation of a new email message with predefined recipient and subject and the pasting of the contents of the clipboard into the body sounds like something I can do. It’s already been done in fact and i think i can download the script and tweak it to suit my needs. But the reformatting or rearrangement of the text is something That i can’t find any examples of in the wild. What seems especially tricky to me is that the Quantity and PartNumber cells, the FirstName and LastName cells and the City,State and Zip cells each need to go one one line, separated by a single space, respectively. I wouldn’t know where to start with regards to that. Honestly though, it would save me at least 30 minutes per week if I could at least get the text into the email as one line per cell.

My first way of thinking was to do a find and replace on the clipboard contents. replacing tabs with carriage returns, somewhere in in the middle of the script. But that doesn’t address the issue of formatting the address correctly. At least the cells are already in the the correct order. And i would often be selecting more than one row of the spreadsheet at a time. I’m not sure if that would make a difference.

I think it would be better if there was a way to say, paste cell one on the first line of the email body, then carriage return, then paste cell 2, then space than paste cell 3 the carriage return, etc. But i don’t see anything yet to suggest that this is possible.

My other way of thinking was to have a script for excel that crated a new worksheet and pasted my selection in the correct format as mentioned above but in cells still and then copy that whole thing to the clipboard and paste that into the email message body.

Can anyone point me in the right direction?
Thank you so much
Steve

#2

Hi,

the part to extract and format the text could be like this


tell application "Microsoft Excel" to set sel to value of selection

set formattedText to {}
if sel is not {} and (count item 1 of sel) > 15 then
	set {TID, text item delimiters} to {text item delimiters, return}
	repeat with oneItem in sel
		set t to {}
		tell items of contents of oneItem
			set end of t to item 1 as integer as text
			set end of t to (item 2 as integer as text) & space & item 3
			set end of t to item 4 & space & item 5
			set end of t to item 6
			set end of t to item 7
			set end of t to item 8
			set end of t to item 9
			set end of t to item 10 & ", " & item 11 & ", " & (item 12 as integer as text)
			set end of t to item 13
			set end of t to item 14
			set end of t to item 15
		end tell
		
		set end of formattedText to t as text
	end repeat
	set text item delimiters to return & return
	set formattedText to formattedText as text
	set text item delimiters to TID
	formattedText
end if
#3

thank you so much
i’m sorry for such a late reply. i had some other issues. i will try this now.
my follow up question is
what do i do with this? if i just want to select the cells containing the information that i want to reformat in excel and then have it go into the clipboard after it is reformatted using this script and then i can paste it into a mail message or something like that. what else would i need to add to the script?
Steve

#4

follow up to this. i found a great app that helps me with this. it’s not as good as a little script that i can use for my automation but it is still pretty good.
it is an app called Apimac Clean Text
there is a feature where you can create “macros” that will deal with the text. it adds an extra step to my workflow but it still saves time overall