如何基于含Google Drive链接的Excel批量发送带附件的邮件？

方法一：Excel VBA脚本实现 #

步骤1：提取纯Drive文件ID #

Excel附件列的HTML链接需先提取纯URL，再从中拆分出文件ID：

• 提取纯URL（假设附件列在E1）：=MID(E1,FIND("href=""",E1)+6,FIND(""" rel",E1)-FIND("href=""",E1)-6)
• 提取文件ID（假设纯URL在F1）：=MID(F1,FIND("/d/",F1)+3,FIND("/",F1,FIND("/d/",F1)+3)-FIND("/d/",F1)-3)

步骤2：VBA脚本下载PDF并发送邮件 #

先启用Excel开发工具，添加Microsoft HTML Object Library和Microsoft Outlook 16.0 Object Library引用，再插入以下脚本：

Sub SendEmailsWithDriveAttachments()
    Dim olApp As Outlook.Application
    Dim olMail As Outlook.MailItem
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim fileID As String
    Dim downloadURL As String
    Dim tempPath As String
    Dim fileName As String
    
    Set olApp = New Outlook.Application
    Set ws = ThisWorkbook.Sheets("Sheet1") ' 替换为你的工作表名
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row ' C列为收件人列，按需调整
    
    tempPath = Environ("TEMP") & "\" ' 系统临时文件夹
    
    For i = 2 To lastRow ' 跳过表头行
        ' 获取提取后的文件ID
        fileID = ws.Cells(i, "F").Value ' F列为文件ID列，按需调整
        downloadURL = "https://drive.google.com/uc?export=download&id=" & fileID
        
        ' 下载PDF到临时文件夹
        fileName = "客户附件_" & i & ".pdf"
        Call DownloadFile(downloadURL, tempPath & fileName)
        
        ' 创建并发送邮件
        Set olMail = olApp.CreateItem(olMailItem)
        With olMail
            .To = ws.Cells(i, "C").Value
            .Subject = "客户咨询回复 - " & ws.Cells(i, "A").Value ' A列为姓名列
            .Body = "您好，以下是您的咨询内容回复：" & vbCrLf & ws.Cells(i, "B").Value ' B列为内容列
            .Attachments.Add tempPath & fileName
            .Send ' 改为.Display可预览邮件
        End With
        
        ' 删除临时文件
        Kill tempPath & fileName
    Next i
    
    Set olMail = Nothing
    Set olApp = Nothing
    MsgBox "批量邮件发送完成"
End Sub

' 辅助下载文件的函数
Sub DownloadFile(url As String, savePath As String)
    Dim xHTTP As Object
    Dim oStream As Object
    
    Set xHTTP = CreateObject("MSXML2.XMLHTTP")
    Set oStream = CreateObject("ADODB.Stream")
    
    xHTTP.Open "GET", url, False
    xHTTP.Send
    
    oStream.Type = 1
    oStream.Open
    oStream.Write xHTTP.responseBody
    oStream.SaveToFile savePath, 2 ' 2=覆盖已有文件
    oStream.Close
    
    Set xHTTP = Nothing
    Set oStream = Nothing
End Sub

注意：需确保Google Drive文件权限设置为「知道链接的人可下载」，否则脚本无法获取文件。

方法二：Power Automate（微软流）实现 #

步骤1：关联Excel文件 #

将编辑好的Excel文件保存到OneDrive/SharePoint，创建新流，触发方式选择「手动触发流」或「当Excel表格行添加时」。

步骤2：提取并下载Drive文件 #

• 用「文本操作」提取附件列的纯URL和文件ID，逻辑同Excel公式。
• 添加「HTTP」动作，发送GET请求到https://drive.google.com/uc?export=download&id=[文件ID]，获取文件二进制内容。

步骤3：批量发送邮件 #

添加「发送电子邮件(V2)」动作，将HTTP返回的文件内容作为附件上传，填写收件人、主题、正文后执行流即可。

方法三：Google生态内直接处理 #

若可调整流程，直接在Google Sheet中补充收件人信息，用Google Apps Script实现：

function sendEmailsWithAttachments() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("表单提交");
  const data = sheet.getDataRange().getValues();
  
  for (let i = 1; i < data.length; i++) {
    const name = data[i][0];
    const content = data[i][1];
    const recipient = data[i][2];
    const link = data[i][3];
    const fileId = link.match(/file\/d\/([^\/]+)/)[1];
    
    const file = DriveApp.getFileById(fileId);
    
    GmailApp.sendEmail(
      recipient,
      `客户咨询回复 - ${name}`,
      `您好，以下是您的咨询内容回复：\n${content}`,
      { attachments: [file.getAs(MimeType.PDF)] }
    );
  }
}

此方法无需导出到Excel，权限适配更顺畅。

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