0

I'm currently creating an office script to send email from excel online(hosted in onedriveforbusiness). The components of the email am extracting from excel cell values. Have done a research and it looks to me that office script has syntax similar to javascript or typescript. I'm also not sure if the language used is javascript or typescript, kindly clarify for me on that.

In summary

  1. I need a code sample on how to send email from office script in excel online(office 365)
  2. I need to know what language is being used in office script, is it javascript or typescript.

Below is the sample code office script have written and its failing when i run it.

function main(workbook: ExcelScript.Workbook) {
    // Your code here

  // select a sheet
  let selected_sheet = workbook.getActiveWorksheet()

  // get subject
  let subject = selected_sheet.getRange("C2")
  let subject_ = subject.getValue()

  // get body
  let body = selected_sheet.getRange("E2")
  let body_ = body.getValue()

  // to email
  let receivembx = selected_sheet.getRange("B2")
  let receivembx_ = receivembx.getValue()

  // from email
  let sendmbx = "emailhere"
  let pwd_ = "passwordhere"

  Email.send({
    Host: "smtp.office365.com",
    Username: sendmbx,
    Password: pwd_,
    To: receivembx_,
    From: sendmbx,
    Subject: subject_,
    Body: body_,

  }).then(
    console.log("mail sent successfully")
  );

  console.log(sendmbx)
}

Upon running that code, i get enter image description here enter image description here

Below is the snip for the excel online and office script. enter image description here

Bernietechy
  • 186
  • 1
  • 16
  • Maybe try [Power Automate](https://powerusers.microsoft.com/t5/General-Power-Automate/Send-an-email-to-the-email-address-found-in-excel-using-power/td-p/731230)? – findwindow Jun 03 '22 at 15:29
  • Is there a way to implement a email sending snippet within the script? I believe if the language being used is `javascript or typescript` i can send an email – Bernietechy Jun 03 '22 at 15:36
  • Looks to be typescript https://learn.microsoft.com/en-us/office/dev/scripts/tutorials/excel-tutorial – findwindow Jun 03 '22 at 15:43
  • VBA is not supported in excel online – Bernietechy Jun 03 '22 at 15:50
  • I just need a sample snippet on how to send the email from office script, from there i will able to customize my code based on excel values. Have not found any way to do this from internet. I'm new to office scripts that is why i had to seek for guidance. – Bernietechy Jun 03 '22 at 15:52
  • There is no Email object in Office Scripts. So you can't pass in data to an Email object or use methods that send an email. I would recommend looking into using PowerAutomate for this. You can see an example here: https://learn.microsoft.com/en-us/power-automate/email-customization – Brian Gonzalez Jun 05 '22 at 01:37

1 Answers1

0

Question 1

The code editor you're using has more limited capabilities so you aren't permitted to directly send emails. With VBA this is somewhat intuitive as VBA has the power to do a lot with your PC (perhaps too much). You would be better off to try to use powerapps.

Since you asked for an example, consider this one from Microsoft.

Note that you can capture fields from an table in excel so that's how you would get a dynamic email address per message, etc.

Question 2

You are using TypeScript, but very similar. The difference is described as:

"Office Scripts are written in TypeScript, which is a superset of JavaScript. The Action Recorder generates code in TypeScript and the Office Scripts documentation uses TypeScript. Since TypeScript is a superset of JavaScript, any scripting code that you write in JavaScript will work just fine."

pgSystemTester
  • 8,979
  • 2
  • 23
  • 49
  • Kindly note this is excel online – Bernietechy Jun 03 '22 at 17:31
  • @Bernietechy So specify that it is Excel Online in the question - an important bit of info that you failed to include. – Solar Mike Jun 03 '22 at 18:03
  • @Bernietechy, yes I know it's Excel online... I mentioned VBA only to indicate you should not confuse the fact that the task you're doing online is easy to do with the excel application vs using the web version. And as you requested a snipit or example of using excel and a mail program was included. – pgSystemTester Jun 03 '22 at 19:10