how do I automatically send an email (w/ attachment) to recipients when an xlsx file with their address is added to a folder?

Question

I'm using macOS 14.6.1 and Outlook 16.89.3. I can't quite find an answer to this question on the forum.

I'm trying to create a folder actions service/script to monitor a particular folder for any new excel files (only excel files would be added to this folder anyway) and, when a file is added, send that file, via Outlook, to the user whose email address is found in cell B2 of the spreadsheet.

I've tried using chatgpt for this. It gives me a script that works when using Apple Mail, but not Outlook.

Here is one example of what chatgpt says I should use...which keeps failing. When I drop a file in the folder, I see the little gear icon turning in the menu bar, but there are no dialog boxes that appear, no error messages, and no created/sent emails in Outlook.

on adding folder items to this_folder after receiving added_items
    repeat with added_item in added_items
        set filePath to POSIX path of added_item
        -- Check if the file is an Excel file
        if filePath ends with ".xlsx" then
            display dialog "File added: " & (name of added_item) -- Debugging message
            try
                -- Open the Excel file to read the email address
                tell application "Microsoft Excel"
                    open filePath
                    delay 1 -- Wait for Excel to open
                    set recipientEmail to value of cell "B2" of active sheet
                    close active workbook saving no -- Close the workbook without saving
                end tell
                
                -- Ensure recipientEmail is valid
                if recipientEmail is not missing value then
                    -- Prepare the email
                    tell application "Microsoft Outlook"
                        set newMessage to make new outgoing message with properties {subject:"Your Requested Excel File", content:"Hello,\n\nPlease find the requested Excel file attached.\n\nBest regards,\nYour Name"}
                        make new recipient at newMessage with properties {email address:{address:recipientEmail}}
                        make new attachment at newMessage with properties {file:added_item}
                        send newMessage
                    end tell
                else
                    display dialog "No email address found in cell B2 of " & (name of added_item)
                end if
            on error errMsg
                display dialog "Error processing file: " & errMsg
            end try
        end if
    end repeat
end adding folder items to

Thanks for your time.

Answer 1

Folder actions will fail silently, and the problem statement

   display dialog "File added: " & (name of added_item)

will error because AppleScript doesn't know about file properties, and it is outside the script's error handling (the try statement). Finder (or System Events) are the applications normally used for handling files, so you need to ask one of them to get the name of the file. In your posted script for example, you can add a statement to get the file name:

   set filePath to POSIX path of added_item -- existing statement
   tell application "Finder" to set file_name to (name of added_Item) -- new statement

...then replace all occurrences of (name of added_item) with file_name.

I don't use Excel or Outlook to test further, but a handy way to manually test your folder actions is to break out the functionality into its own handler so that it can also be run from the editor or as a droplet. For example:

on run -- run from Script Editor or application double-clicked
   doStuff for (choose file with multiple selections allowed)
end run

on open theFiles -- items dropped onto the droplet
   doStuff for theFiles
end open

on adding folder items to this_folder after receiving these_items -- folder action
   doStuff for these_items
end adding folder items to

to doStuff for someFiles -- the main funtion
    repeat with added_item in someFiles
        set filePath to POSIX path of added_item
        -- Check if the file is an Excel file
        if filePath ends with ".xlsx" then
            tell application "Finder" to set file_name to (name of added_Item)
            display dialog "File added: " & file_name -- Debugging message
            try
                -- Open the Excel file to read the email address
                tell application "Microsoft Excel"
                    open filePath
                    delay 1 -- Wait for Excel to open
                    set recipientEmail to value of cell "B2" of active sheet
                    close active workbook saving no -- Close the workbook without saving
                end tell
                
                -- Ensure recipientEmail is valid
                if recipientEmail is not missing value then
                    -- Prepare the email
                    tell application "Microsoft Outlook"
                        set newMessage to make new outgoing message with properties {subject:"Your Requested Excel File", content:"Hello,\n\nPlease find the requested Excel file attached.\n\nBest regards,\nYour Name"}
                        make new recipient at newMessage with properties {email address:{address:recipientEmail}}
                        make new attachment at newMessage with properties {file:added_item}
                        send newMessage
                    end tell
                else
                    display dialog "No email address found in cell B2 of " & file_name
                end if
            on error errMsg
                display dialog "Error processing file: " & errMsg
            end try
        end if
    end repeat
end doStuff