基于Power Automate/Office Scripts实现Excel条件邮件合并发送的技术问询

Office Scripts本身没有直接发送邮件的权限，必须搭配Power Automate完成整个邮件合并流程，以下是具体步骤：

一、编写Office Scripts脚本（提取并整理收件人数据） #

这个脚本负责从三个工作表中筛选符合条件的收件人，匹配对应代码，并组装好邮件的主题和正文：

function main(workbook: ExcelScript.Workbook): RecipientData[] {
  // 获取对应工作表
  const peopleSheet = workbook.getWorksheet("People");
  const categoryMapSheet = workbook.getWorksheet("CategoryMap"); // 替换为你的类别对照表工作表名
  const templateSheet = workbook.getWorksheet("Sheet3");

  // 构建类别与代码的映射表
  const categoryMapValues = categoryMapSheet.getUsedRange().getValues();
  const categoryCodeMap: {[key: string]: string} = {};
  for (let i = 1; i < categoryMapValues.length; i++) {
    const category = categoryMapValues[i][0].toString();
    const code = categoryMapValues[i][1].toString();
    categoryCodeMap[category] = code;
  }

  // 读取邮件模板内容
  const templateSubject = templateSheet.getRange("A2").getValue().toString();
  const templateBody = templateSheet.getRange("B2").getValue().toString();

  // 筛选并整理符合条件的收件人数据
  const peopleValues = peopleSheet.getUsedRange().getValues();
  const recipients: RecipientData[] = [];
  
  for (let i = 1; i < peopleValues.length; i++) {
    const name = peopleValues[i][0].toString();
    const email = peopleValues[i][1].toString();
    const category = peopleValues[i][2].toString();

    // 仅保留类别为2或3的收件人
    if (category === "2" || category === "3") {
      const code = categoryCodeMap[category];
      const finalSubject = `${templateSubject} ${code}`;
      const finalBody = `Dear ${name},\n\n${templateBody}`;
      
      recipients.push({name, email, finalSubject, finalBody});
    }
  }

  return recipients;
}

// 定义返回数据结构
interface RecipientData {
  name: string;
  email: string;
  finalSubject: string;
  finalBody: string;
}

脚本使用说明 #

1. 打开你的Excel工作簿，点击顶部菜单栏的「自动化」->「新建脚本」。
2. 将上述代码粘贴到脚本编辑器中，修改categoryMapSheet的工作表名为你实际的类别对照表名称。
3. 确认People表的列顺序：姓名在第1列（索引0）、邮箱在第2列（索引1）、类别在第3列（索引2），如果你的表结构不同，调整对应的索引值。
4. 保存脚本。

二、创建Power Automate流发送邮件 #

1. 登录Power Automate，新建即时云流，触发方式选择「手动触发流」。
2. 添加「Excel Online (Business)」的「运行脚本」动作：
   • 选择工作簿所在的SharePoint/OneDrive站点、目标工作簿，以及刚才保存的脚本。
3. 添加「应用到每一个」动作，选择上一步返回的「输出」数组（即脚本返回的收件人列表）。
4. 在「应用到每一个」容器内添加「发送电子邮件(V2)」（Outlook）动作：
   • 「收件人」选择当前项的email字段；
   • 「主题」选择当前项的finalSubject字段；
   • 「正文」选择当前项的finalBody字段。
5. 保存流后，即可手动触发测试，完成邮件发送。

注意事项 #

• 确保你的Office 365账号拥有Excel Online、Outlook以及Power Automate的完整权限。
• 如果邮件正文需要格式（如换行、加粗），可以在脚本里用HTML标签调整，比如把\n替换为<br>，并在Power Automate的邮件动作中选择「HTML」格式。

内容的提问来源于stack exchange，提问作者Kris