AppleScript to copy and separate Mail body content into Excel

Scripting Forums AppleScript | Mac OS X
#1

Beginner here: I’m trying to edit an AppleScript that looks for a specific subject in mail, then pulls information from the body of that email and separates it out into different cells of a current workbook. Any help would be truly appreciated!

Three issues I can’t work out - maybe AppleScript is not the right move here.

How to tell it to import it to an existing (not new) workbook.
How to tell it to look for a specific subject line
How to get it to stop failing at: set fieldVal to text (1 + (offset of “:” in eachPara)) through end of eachPara
For the paragraph issue. I’ve tried putting the field and the value on different lines, same line, not separated by a space… etc.

Here’s what the body of the email looks like:

Code:

tell application "Microsoft Excel"
set LinkRemoval to make new workbook
set theSheet to active sheet of LinkRemoval
set formula of range "K1" of theSheet to "Name of Requester"
set formula of range "J1" of theSheet to "Requester's Role"
set formula of range "I1" of theSheet to "Requester's Email"
set formula of range "H1" of theSheet to "Manager"
set formula of range "E1" of theSheet to "Start Date"
set formula of range "D1" of theSheet to "End Date"
end tell

tell application "Mail"
set theRow to 2
set theAccount to "Account"
get account theAccount
set theMessages to messages of inbox
repeat with aMessage in theMessages
    set theText to content of aMessage
    set theName to my getField(theText, "Name of Requester:")
    set theRole to my getField(theText, "Requester's Role:")
    set theEmail to my getField(theText, "Requester's Email:")
    set theName to my getField(theText, "Manager:")
    set theDate to my getField(theText, "Start Date:")
    set theDate to my getField(theText, "End Date:")
    set theRow to theRow + 1
end repeat
end tell

on getField(theText, fieldName)

set p to paragraphs of theText
repeat with eachPara in p
    if (eachPara as text) begins with fieldName then
        set fieldVal to text (1 + (offset of ":" in eachPara)) through end of     eachPara
        return fieldVal
    end if

end repeat

return ""

end getField
#2

Try this. Its a brute force way of doing this.

You must have a worksheet open in Excel and the messages of interest selected in Mail prior to running the routine.


-- Open the desired workbook to the correct worksheet
--     or have a new workbook open before calling.
-- Select the messages in "Mail" that are to be processed

tell application "Mail"
	set theMessages to selection
	if theMessages is {} then return
	activate
	repeat with aMessage in theMessages
		set theText to content of aMessage
		set theName to my getField(theText, "Name of Requester:")
		set theRole to my getField(theText, "Requester's Role:")
		set theEmail to my getField(theText, "Requester's Email:")
		set theName to my getField(theText, "Manager:")
		set startDate to my getField(theText, "Start Date:")
		set endDate to my getField(theText, "End Date:")
		
		tell application "Microsoft Excel"
			activate
			set LinkRemoval to active workbook
			set theSheet to active sheet of LinkRemoval
			tell theSheet
				set rowN to count of rows of used range
				set colN to count of columns of used range
			end tell
			if (rowN = 1) and (colN = 1) then
				set formula of range "K1" of theSheet to "Name of Requester"
				set formula of range "J1" of theSheet to "Requester's Role"
				set formula of range "I1" of theSheet to "Requester's Email"
				set formula of range "H1" of theSheet to "Manager"
				set formula of range "E1" of theSheet to "Start Date"
				set formula of range "D1" of theSheet to "End Date"
			end if
			set theRow to rowN
			repeat with aMessage in theMessages
				set theRow to theRow + 1
				set value of range ("K" & theRow) to theName
				set value of range ("J" & theRow) to theRole
				set value of range ("I" & theRow) to theEmail
				set value of range ("H" & theRow) to theName
				set value of range ("E" & theRow) to startDate
				set value of range ("D" & theRow) to endDate
			end repeat
		end tell
		
	end repeat
end tell


on getField(theText, fieldName)
	set fieldVal to "" as text
	set p to paragraphs of theText
	repeat with eachPara in p
		if (eachPara as text) begins with fieldName then
			set fieldVal to characters ((offset of ":" in eachPara) + 2) thru -1 of eachPara as text
		end if
	end repeat
	return fieldVal
end getField