Copying a Row From Excel Into Outlook

TheBlackBinLiner · January 4, 2023, 6:48pm

Hi all and a happy 2023!

Using Applescript I’m trying to extract data from Microsoft Excel.

A user puts in a unique Job number in a dialog, the script locates the same Job number in column M of an open Excel doc.

Once the unique number has been located in column M it will copy certain cells from the row it belongs to, within the range of column C to column R. Then paste this into a new Microsoft Outlook email along with other text required in the email body.

I only need the data from the columns C, I, M, P and R, but if the entire row in the range C to R is easier I’m happy to manually clear the unwanted cells once it’s in the email body.

I do not want this pasted as plain delimited text, just straight in as an HTML in the “cells” as it is in Excel.

I’ve got as far as the below but with this the user has to filter for the Job number directly in Excel in column M first, then go back to the script dialog to put in the range of cells and the row number it relates, eg C10:R10.

It does return all the data but the result seems to be comma delimited within two braces {{ and I can’t get it into a new email or figure out how I get other text in the email body.

Essentially what I want the email to say in layman’s terms is:

Please find info below regarding this job:
Excel cells pasted here
Thanks and regards

Any help or pointers greatly received

display dialog "Enter Job Number" default answer ""

set theText to text returned of the result

tell application "Microsoft Excel"

    set myRange to range theText of sheet "Master Sheet" of workbook "Artwork Tracker V1"

    return value of myRange
    tell application "Microsoft Outlook"

        tell (make new outgoing message with properties {subject:"Enter Subject Details Here", content:myRange})

            make new recipient with properties {email address:{name:"Joe Bloggs", address:"Joe.Bloggs@email.co.uk"}}

            open

        end tell

        activate

    end tell

end tell

Mockman · January 5, 2023, 2:11am

Not sure exactly what you want this to look like as you didn’t supply any data. As I understand it, HTML isn’t the base format of what’s in a cell — rather, it’s a supported input/output variant, as is RTF. So I’ll just assume some basic formatting, e.g. colour, alignment, bold, and the cell structure (which you mention).

Looking at your code, it looks like you’re trying to get Outlook to eat an excel range. It doesn’t know what that is though. You need to get the contents of the range.

Generally, a brace of braces suggests that you have a list of lists, which is something else that Outlook likely won’t understand. But that’s just a guess. If you want to work with something that looks like this, you have to dig in like this below. Item 1 of xy would be the first list.

    set xy to {{"list 1, item 1", "list 1, item 2"}, {"list 2, item 1", "list 2, item 1"}}
    set yz to item 1 of item 1 of xy
    --> "list 1, item 1"

I can’t really work with outlook so I’ll use TextEdit here instead. Convert an empty text document to rich text before you run the script. It should deposit your cells and text in it (at the bottom if there is already text in it). It should be relatively straightforward to make it work with outlook.

*** IMPORTANT *** Before running the script, identify a blank range to use as a scratchpad. I used cell A15 but you may have data there and the script will overwrite it. Edit the line that begins “set aTempcr to…” so that it uses a cell in a safe area.

The script uses intersect to refine your range to include only the five cells you need. It then copies that range’s contents to a blank area of the spreadsheet — note that all adjacent cells (above, below, right, left, diagonal) must be empty as well or they will be copied as well.

Once copied to the new area, it is again copied as rich text and a new paragraph in TextEdit is created with its content, along with the two text strings you need.

tell application "Microsoft Excel"
	tell active sheet of workbook 1
		activate
		-- set up ranges
		set rowRge to range "C10:R10"
		set colRge to range "C:C,I:I,M:M,P:P,R:R"
		with timeout of 1 second
			set ise to intersect range1 rowRge range2 colRge
			--> range "[Workbook1.xlsx]Sheet1!$C$10,$I$10,$M$10,$P$10,$R$10"
		end timeout
		-- select ise -- optional; only here to aid visualization
		
		-- pick blank area of spreadsheet	
		-- copy/paste data from above cells to here, which removes blank cells;
		-- *** Change this range so that it points to a blank area of your spreadsheet ***
		set aTemp to range "A15" --< change this to a safe address
		copy range ise destination aTemp
		
		activate object aTemp
		set aTempcr to current region of aTemp
		--> "$A$15:$E$15"
		copy range aTempcr
		set clipRTF to the clipboard as «class RTF »
		
	end tell
end tell

-- Additional text
set textBlobAbove to linefeed & "Please find info below regarding this job:
Excel cells pasted here: " & linefeed & linefeed
set textBlobBelow to linefeed & linefeed & "Thanks and regards" & linefeed & linefeed

-- deposit in rich text TextEdit document
tell document 1 of application "TextEdit"
	activate
	make new paragraph at end with data textBlobAbove
	make new paragraph at end with data clipRTF
	make new paragraph at end with data textBlobBelow
	
end tell

TheBlackBinLiner · February 9, 2023, 2:38pm

Hi all and thanks for the responses,

I’ve scratched my head trying to get Mockman’s solution to work for my needs but to no avail.

To clarify:

We have printed material that comes in with unique “Asset Codes” printed on them. This corresponds to the same code in an Excel doc.

I want the user to be able to type into an Applescript dialog the Asset Code, it locate the correct entry in the open Excel doc, then copy the contents to the clipboard from the Cells C, I, M, P and R from the same row that the Asset Code resides to be used for a paste function later on.

I need it to be dynamic, as the asset code is unique every time.

I’ve managed to get as far as the below but this just selects the entire columns of C, I, M, P and R as you would expect with “C:C,I:I,M:M,P:P,R:R” and I can’t figure out the commands/syntax to do what I require.

Any help greatly received.

tell application "Microsoft Excel"
display dialog "Enter Asset Code" default answer ""
set theText to text returned of the result
select range "C:C,I:I,M:M,P:P,R:R"
tell application "System Events" to keystroke "c" using command down
end tell

TheBlackBinLiner · February 9, 2023, 2:48pm

I’ve got a little bit further and forgot to say Asset Code is always located in cells in column C.
The below returns the value of the Cell and it’s location (C15 in this case) containing the Asset Code and then gets the Row number that cell resides in (15) - not sure if this helps extract the data from the Cells listed previously?

tell application "Microsoft Excel"
set searchRange to range ("C1")
display dialog "Enter Asset Code" default answer ""
set theText to text returned of the result
set foundRange to find searchRange what result with match case
set fRow to first row index of foundRange
set myData to value of range ("C" & fRow as text)
set copyRow to the first row index of the foundRange
select range "C:C,I:I,M:M,P:P,R:R"
tell application "System Events" to keystroke "c" using command down
end tell

Mockman · February 9, 2023, 9:17pm

That additional information is helpful.

NB I’m using range A25:E25 to consolidate the contents of the five cells. Whatever is in those cells will be overwritten. Doing this allows you to easily capture only the required cells and not all those in between.

I moved the dialog code outside of the excel block as they’re not related.

set inputCode to display dialog "Enter Asset Code" default answer ""
set cAC to text returned of inputCode

tell application "Microsoft Excel"
	tell active sheet
		with timeout of 8 seconds
			set depRge to "$A$25:$E$25" -- where to deposit the five cells' data
			clear range range depRge -- clear range beforehand
			
			set cCol to range "C2:C12" -- range to search in
			set cAsset to find cCol what cAC -- the string to search for, i.e. asset code
			
			set rowRge to get resize cAsset column size 16 -- resize range to include columns C:R
			set colRge to range "C:C,I:I,M:M,P:P,R:R"
			
			set ise to intersect range1 rowRge range2 colRge
			
			set aTemp to range "A25" --< change this to a safe address
			copy range ise destination aTemp -- copy the five cells to "$A$25:$E$25"
			
			activate object aTemp
			set aTempcr to current region of aTemp
			--> "$A$25:$E$25"
			copy range aTempcr
			set clipRTF to the clipboard
			
		end timeout
	end tell
end tell

What this does is take the entered code and search for it in column c. If it finds the asset code then it returns the range of the found cell. Next, it resizes that range to include the rest of the row out to column R (e.g. if the found cell is C10, then the new range will be $C$10:$R$10). It then intersects that range with columns C,I,M,P,R to produce a range that includes the five corresponding cells, e.g. $C$10,$I$10,$M$10,$P$10,$R$10.

Once the appropriate range has been determined, the values from those cells are copied to range A25:E25 to make the cells contiguous. The values are then copied to the clipboard and can be put wherever.

TheBlackBinLiner · February 10, 2023, 9:58am

Thanks for the speedy reply!

Unfortunately this seems to have a stumbling block and returns an error.

We have quite a lot of entries so I’ve changed the cell ref to a line at 1000 to avoid clearing any important stuff. Below is your script modified just in case I’ve done something stupid.

set inputCode to display dialog "Enter Asset Code" default answer ""
set cAC to text returned of inputCode

tell application "Microsoft Excel"
tell active sheet
	with timeout of 8 seconds
		set depRge to "$A$1000:$E$1000" -- where to deposit the five cells' data
		clear range range depRge -- clear range beforehand
		
		set cCol to range "C2:C12" -- range to search in
		set cAsset to find cCol what cAC -- the string to search for, i.e. asset code
		
		set rowRge to get resize cAsset column size 16 -- resize range to include columns C:R
		set colRge to range "C:C,I:I,M:M,P:P,R:R"
		
		set ise to intersect range1 rowRge range2 colRge
		
		set aTemp to range "A1000" --< change this to a safe address
		copy range ise destination aTemp -- copy the five cells to "$A$25:$E$25"
		
		activate object aTemp
		set aTempcr to current region of aTemp
		--> "$A$1000:$E$1000"
		copy range aTempcr
		set clipRTF to the clipboard
		
	end timeout
end tell
end tell

And this is the error message I’m getting…

  error "Microsoft Excel got an error: missing value doesn’t understand the “copy range” message." number -1708 from missing value*

And the log info…

  tell application "Script Editor"
  display dialog "Enter Asset Code" default answer ""
  --> {button returned:"OK", text returned:"SKA0999"}
  end tell
  tell application "Microsoft Excel"
  clear range range "$A$1000:$E$1000" of active sheet
  get range "C2:C12" of active sheet
  --> range "C2:C12" of active sheet
  find range "C2:C12" of active sheet what "SKA0999"
  --> error number -50
  Result:
  error "Microsoft Excel got an error: Parameter error." number -50

Any ideas what’s causing the error?

TheBlackBinLiner · February 10, 2023, 10:27am

Ignore the above as I’ve just realised my stupid mistake.
I didn’t expand the search range so anything after C12 wasn’t being picked up, so resulted in weird things.

Must remember to do this stuff after my first cup of coffee!

Thanks again for your assistance and script tips. Having the itemised text of “what the script does” in plain English really helped!

Many thanks!!

TheBlackBinLiner · February 10, 2023, 1:29pm

As an addendum to this I’ve added the show all data command right after tell application as occasionally this spreadsheet can be open filtered, and this script will only work with all data shown.

Hope this helps others

Cheers again Mockman!

Mockman · February 10, 2023, 3:38pm

Glad it helped.

FWIW, here are a couple of additional ideas along the lines of the show all data command that may be of value. Below is the script that incorporates them.

Check to see if the document is open and active before proceeding.
Uses excel’s Input Box command which instructs excel to supply the input dialogue. It might be better for users to keep within excel visually.
In case the code cannot be found (e.g. not entered correctly) then the script will throw up an alert explaining the issue. It also displays the range so that you would see immediately whether there was an issue with that. Note that it only responds to this particular error so if there is something else that’s wrong, it won’t obscure that and you should get a system-generated error.

-- set inputCode to display dialog "Enter Asset Code" default answer ""
-- set cAC to text returned of inputCode

set wbfn to (path to desktop as text) & "datagarb3.xlsx"
set wbn to "datagarb3.xlsx"

tell application "Microsoft Excel"
	if exists workbook wbn then
		activate object workbook wbn -- if doc is open, make active
	else
		open workbook workbook file name wbfn -- if doc is not, then open
	end if
	
	set cAC to (input box prompt "Asset code" type string) -- input asset code
	
	tell active sheet of workbook wbn
		with timeout of 8 seconds
			set depRge to "$A$25:$E$25"
			clear range range depRge
			
			set cCol to range "C2:C12"
			try -- in case the entered code cannot be found in column C
				set cAsset to find cCol what cAC
				
				set rowRge to get resize cAsset column size 16
				set colRge to range "C:C,I:I,M:M,P:P,R:R"
				
				set ise to intersect range1 rowRge range2 colRge
				
				set aTemp to range "A25" --< change this to a safe address
				copy range ise destination aTemp
				
				activate object aTemp
				set aTempcr to current region of aTemp
				--> "$A$25:$E$25"
				copy range aTempcr
				-- set clipRTF to the clipboard as «class RTF »
				set clipRTF to the clipboard
				
			on error number -1708
				display alert "That asset code could not be found in range " & (get address cCol without row absolute and column absolute)
			end try
			
		end timeout
	end tell
end tell

On the subject of where to put the five output cells, I don’t know if you can rearrange the document but for myself, I often start the data area in row 10. This leaves me with some space at the top to put in various status formulae or intermediary data such as this.

Regards