Send an email using Excel Online Script

BrainStain
NewLounger
Posts: 6
Joined: 08 Aug 2023, 17:55

Send an email using Excel Online Script

Post by BrainStain »

Eileen's Lounge really helped me before with a script, thanks, hoping I can get an answer for this please.

I'm looking for a bit of code for a script within Excel Online, that will send me an email when a certain action is taken,
similar to the way Comments work with @mention, but I don't want to use comments in this case.

I have someone who adds, deletes, and archive's projects on a progress tracking tool I built in Excel, and jobs are listed
in this tool for time tracking for about 12 engineers.

When a project (consisting of about 15 rows) is Archived, its also removed from an Engineers sheet.
A project can also be added to an engineer sheet when new jobs come in. Each engineers sheet can have over 100 projects.

I need to know when a job is added, deleted, and archived (which is done by someone else) so I can update another sheet
which is a Birds Eye list of Active jobs and their status.

I already have scripts ran by buttons to do the adding, deleting, and archiving, but don't know when they are done except by
manually looking at about 12 sheets to see when something changed.

I want to add this email snippet to each of the current buttons that add, delete, and archive so it will notify me when something changes.

If I can just learn the code once, I can modify it for the other purposes.

It can have a common subject line, or none at all (like how comments send @mention emails without a subject line, but I would like
it to have a one line text to tell me what changed, or it can have that one line in the subject without any body text, either way.
I will customize the message based on which button I add it to.

Ive tried this and have a button for this script so that its the only thing running before I add it to an existing button script,
but just cant get it to send me the email.

Any ideas on having a way for a script to send an email please?

Thanks for all the help and ideas.

BrainStain
Last edited by BrainStain on 29 Nov 2023, 15:33, edited 2 times in total.

User avatar
HansV
Administrator
Posts: 78071
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Send an email using Excel Online Script

Post by HansV »

As far as I know, you'll need to create a Power Automate Flow to create and send an email message.
See Send Email from Excel with Office Scripts & Power Automate for an example.
Best wishes,
Hans

BrainStain
NewLounger
Posts: 6
Joined: 08 Aug 2023, 17:55

Re: Send an email using Excel Online Script

Post by BrainStain »

Thanks Hans,
Looks like a challenge, but I'm up for it to learn something.
Thanks for the tip, will let you know the results.
BrainStain

snb
4StarLounger
Posts: 542
Joined: 14 Nov 2012, 16:06

Re: Send an email using Excel Online Script

Post by snb »

I already have scripts ran by buttons to do the adding, deleting, and archiving,
What do they look like ?

BrainStain
NewLounger
Posts: 6
Joined: 08 Aug 2023, 17:55

Re: Send an email using Excel Online Script

Post by BrainStain »

For the sake of space, I'll just paste the shortest script, which is for the Deleting a project after its been moved to Archive.
The other 2 scripts are over 400 lines of code, this is much shorter, and only shows where I want to insert the 'emailing script' near
the bottom of this to notify me a project has been deleted, so I can go to my Birds Eye sheet and remove it from the Status list
that keeps track of all active jobs.

================================================================

// This Deletes a job after its been Archived


function main(workbook: ExcelScript.Workbook) {

let selectedSheet = workbook.getActiveWorksheet();

// Gets the data from the source cells
let cell = selectedSheet.getRange("A1");
let cell2 = selectedSheet.getRange("A2");

// Read the value from the source cell
let cellValue = cell.getValue();
let cell2Value = cell2.getValue();

// Store the source cell value as a variable
let myVariable = cellValue;
let myVariable2 = cell2Value;

let srcAddress = myVariable.toString() + ":" + myVariable2.toString();

selectedSheet.getRange(srcAddress).delete(ExcelScript.DeleteShiftDirection.up);

// Clear ExcelScript.ClearApplyTo.contents from range A1:A2 on selectedSheet
selectedSheet.getRange("A1:A2").clear(ExcelScript.ClearApplyTo.contents);

// so the emailing script would be inserted down here

}