AppleScript Pop-Up Menu – Create Pop-Up Menu from Contacts Group Names

This is a bit long-winded, but here goes…

Using AppleScript (vanilla, please), I want to automate the Numbers spreadsheet I made for my wife’s music lessons Accounts (which she uses four times a year – she’s the sole user).

This is my first AppleScript Project. I’ve found some individual bits, which work partially, and I’d like help fitting them together.

Script 1A and Script 1B should be combined into ONE Script, but I don’t know how to do that.

Script 1 only needs to run once, after she (manually) opens the spreadsheet, but Script 2 will be run for every student’s account, to generate an email (so approximately 25 individual times)

Script 2 needs to get information from Script 1 – see below.

The overall sequence should go like this:

1) User ensures Contacts are up to date on laptop (Catalina), and manually opens the Accounts spreadsheet.
2) Run Scripts 1A &1B (which will be ONE script)
3) Fill in details
4) Run Script 2
5) Check, and (manually) send email.
6) Repeat from 3) for each student
——————————————————————————————-

Script 1A

--returns Contacts group’s names and email addresses

set myList to ""
tell application "Contacts" to repeat with p in people in group "testGroup"
    if emails of p is {} then
        set e to ""
    else
        set e to value of email 1 of p
    end if
    set myList to myList & name of p & ":" & e & linefeed -- gives full names
end repeat

(result of this is e.g…) (I don’t know if this result is in a suitable format…)

"Peter Pan:peterpan@gmail.com
Joe Bloggs:joebloggs@gmail.com
Molly Mouse:mollymouse@gmail.com
"
——————————————————————————————-

Script 1B MUST USE items from above result in Script 1A, NOT the menuItems list below!

-- Make Pop-Up Menu in Numbers spreadsheet which is already open.
set menuItems to {"Joe Bloggs", "Molly Mouse", "Peter Pan"}

tell application "Numbers"
    activate
    tell the first table of the active sheet of document 1
        tell cell "A3"
            -- Set the cell value to the first menu item required. This also selects the cell.
            set value to item 1 of menuItems
            -- THEN set the cell format. This causes the menu to adopt the value just set as a single item instead of having the three default items.
            set the format to pop up menu
        end tell
    end tell
end tell


tell application "System Events"
    tell application process "Numbers"
        set frontmost to true
        tell window 1
            -- The inspector doesn't have to be visible, but its "Cell" pane does have to be selected. (?)
            click radio button "Cell" of radio group 1
            -- Adds the remaining items by clicking the "+" button and inputting the texts the required number of times.
            repeat with i from 2 to (count menuItems)
                click button 1 of group 1 of scroll area -1
                keystroke (item i of menuItems)
            end repeat
        end tell
    end tell
end tell

——————————————————————————————-

The user selects a name from the Pop-Up Menu, which now exists, and fills in lesson details, charges, etc.

Then runs Script 2 probably by pressing a key combination on the keyboard – the spreadsheet usually runs fullscreen on the laptop, so a key combination would simplify things ( not essential, but convenient. I can assign the key combination).

——————————————————————————————

Script 2

--Make an email, with a screen captured image of the spreadsheet page, ready to send to the address belonging to the selected name in the Pop-Up Menu 

set thePath to POSIX path of (path to documents folder) & "IMAGE" & ".jpg"
do shell script "screencapture -R20,80,720,880 -tjpg" & space & quoted form of the thePath

tell application "Mail"
    set myMessage to make new outgoing message
    set subject of myMessage to "Music Lessons 1st Term"
    set content of myMessage to " \r \r \r "
    set theAttachedImage to "Macintosh HD:Users:petehunter:Documents:IMAGE.jpg"
    --
    tell myMessage
        make new to recipient at end of to recipients with properties {name:"peterpan@gmail.com “} —Fake name for test purposes! The Script must provide this/these names
        make new attachment with properties {file name:theAttachedImage as alias}
    end tell
    --  
    activate -- brings Mail to front, for checking, and a manual send.
end tell

——————————————————————————————-

I hope this is clear enough. Thanks in advance for your help.