Copying a Row From Excel Into Outlook

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

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