Record Software Licences in Numbers from data extracted from Mail message

Software Registration - Multiple messages.applescript

##############################################
# Title: Record Software Licences in Numbers
##############################################

# Iain Dunn
# Logic2Design
# www.logic2design.com
# logic2design@icloud.com

# Last update: 28 January 2022
# Version: 2 - created MailTag option

# Contributors and sources
# 

##############################################
# Configuration
##############################################

set MailTags to 0 -- set to 1 to use MailTags
property assignedKeywords : {"Purchases"} -- MaiTtag that you would like to allocate

set purchaser to "Your name" -- Purchaser's Name
set mail_Account to "iCloud"
set mail_Archive to "Test"

##############################################
# Code
##############################################
# Select Mail Message(s)

tell application "Mail"
	
	--Get the messages currently selected in Mail
	set the_Messages to selection as list
	
	if the_Messages is {} then -- check empty list
		display alert "Please select some message first"
		return
	else
		
		--Iterate over the selected messages
		repeat with the_Message in the_Messages
			set mdate to date sent of the_Message
			set mrec to address of first recipient of the_Message
			set mnam to name of first recipient of the_Message
			set murl to "message://%3C" & message id of the_Message & "%3e"
			if mnam = missing value then
				set mnam to purchaser
			end if
			set the clipboard to (murl as string)
			
			# Open Numbers
			tell application "Finder"
				--activate
				open (path to home folder as text) & "Library:Mobile Documents:com~apple~Numbers:Documents:Software Licences.numbers"
			end tell
			delay 3
			tell application "Numbers"
				tell front document
					tell active sheet
						set the selectedTable to the first table
					end tell
					
					tell selectedTable
						add row below last row
						tell last row
							--set value of cell 1 to short date string of (current date)
							set value of cell 1 to short date string of (mdate)
							set format of cell 1 to date and time
							set value of cell 5 to mnam
							set format of cell 5 to automatic
							set value of cell 6 to mrec
							set format of cell 6 to automatic
							set format of cell 7 to automatic
							activate
							my makeMailLink() -- Required to make Mail URL in Numbers clickable
						end tell
						
					end tell
				end tell
				
			end tell
			
			# Archive the Message
			tell application "Mail"
				set flagged status of the_Message to false
				--set mailbox of the_Message to mailbox "Archive" of account mailbox
				move the_Message to mailbox mail_Archive of account mail_Account
			end tell
			
			if MailTags = 1 then
				#Tag the Message - optional for Mailtag users
				using terms from application "SmallCubed MailSuite"
					set keywords of the_Message to assignedKeywords
				end using terms from
			end if			
			
		end repeat
	end if
end tell

# Process new Registrations

-- Open Archive Folder
tell application "Mail"
	
	activate
	
	set theMailbox to mailbox mail_Archive of account mail_Account
	set selected mailboxes of front message viewer to theMailbox
	--set sort column of front message viewer to date received column -- Not working due to Mail app bug
	--set sorted ascending of front message viewer to false
	
end tell
--Split Screen with the Mail and Numbers app, makes it easier to record additional information from the mail message
tell application "Shortcuts Events"
	run the shortcut named "Software Registration"
end tell
##############################################
# Functions
##############################################
to makeMailLink()
	tell application "System Events"
		keystroke "v" using command down
	end tell
end makeMailLink