excel - 如何过滤主工作簿以将结果作为表格而不是文件发送？

Question

我的任务是过滤主工作簿以将结果发送给各自的收件人。将位于工作簿中的电子邮件的收件人和正文。

我必须向 50 多个不同的收件人发送 50 多封电子邮件，并以表格形式显示结果。

Option Explicit

Sub split()

    Dim wswb As String
    Dim wssh As String

    Dim vColumn As Variant

    Dim i As Integer
    Dim vcounter As Variant
    Dim vfilter As String

    wswb = ActiveWorkbook.Name
    wssh = ActiveSheet.Name

    vColumn = InputBox("Select Column to Filter", "Column Selection")

    Columns(vColumn).Copy
    Sheets.Add

    ActiveSheet.Name = "Working_Magic"
    Range("A1").PasteSpecial

    Columns("A").RemoveDuplicates Columns:=1, Header:=xlYes

    vcounter = Range("A" & Rows.Count).End(xlUp).Row

    For i = 2 To vcounter
        vfilter = Sheets("Working_Magic").Cells(i, 1)
        Sheets(wssh).Activate
        ActiveSheet.Columns.AutoFilter field:=Columns(vColumn).Column, Criteria1:=vfilter
        Cells.Copy
        Workbooks.Add
      
        Range("A1").PasteSpecial
        If vfilter <> "" Then
            ActiveWorkbook.SaveAs ThisWorkbook.Path & "\DSAttachments_to_email\" & vfilter
        Else
            ActiveWorkbook.SaveAs ThisWorkbook.Path & "\DSAttachments_to_email\_Empty"
        End If
        ActiveWorkbook.Close
        Workbooks(wswb).Activate
    
    Next i
    Sheets("Working_Magic").Delete
    
End Sub

我不需要将附件保存在上面代码所示的文件夹中，而是将报告发送给所有相应的收件人。

标签： excelvbaoutlook

Answer 1

我会采用两种方法之一，将范围转换为 HTML 表格并将其插入电子邮件或使用SendKeys '^c', true然后SendKeys '^v', true. 我的首选是第一个选项，这里是代码：

Sub LoopThroughTable()

    Set ws = ActiveSheet

    For i = 2 To Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Row
        email_to = Sheet1.Cells(i, 5).Value
        email_subject = Sheet1.Cells(i, 4).Value

        folder_path = Sheet1.Cells(i, 2).Value

        Set FSO = CreateObject("Scripting.FileSystemObject")
        'Set fld = FSO.GetFolder(folder_path)
        If FSO.FolderExists(folder_path) Then
            'Nothing, folder is good
        Else
            'Just save to desktop
            folder_path = CreateObject("WScript.Shell").SpecialFolders("Desktop")
            Sheet1.Cells(i, 2).Value = folder_path
        End If

        If Right(folder_path, 1) <> "\" Then
            Sheet1.Cells(i, 2).Value = folder_path & "\"
        End If

        file_path = Sheet1.Cells(i, 2).Value & Sheet1.Cells(i, 3).Value

        sheet_name = Sheet1.Cells(i, 1).Value
        sheet_name_range = Sheet1.Cells(i, 9).Value
        Dim table_range As Range
        Dim range_string As String
        range_string = Sheet1.Cells(i, 10)
        Set table_range = Sheets(sheet_name_range).Range(range_string) 'Range("A3:C8") 'etc.

        email_body = Sheet1.Cells(i, 8).Value & "<br><br>" & ConvertRangeToHTMLTable(table_range)

        CopySheetAndSave sheet_name, file_path

        SendOutlookMessage email_to, email_subject, file_path, email_body
        ThisWorkbook.Activate
    Next i

    ws.Activate

End Sub

Sub CopySheetAndSave(ByVal sheet_name As String, ByVal full_path As String)

    SheetName = sheet_name
    FullPath = full_path

    Sheets(SheetName).Select
    Sheets(SheetName).Copy
    'ChDir "C:\Users\username\Downloads"
    Workbooks(Workbooks.Count).SaveAs Filename:=FullPath, FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
    Workbooks(Workbooks.Count).Close
End Sub


Sub SendOutlookMessage(ByVal email_to As String, ByVal email_subject As String, ByVal file_path As String, ByVal email_body As String)

    emailTo = email_to
    emailSub = email_subject
    FullPath = file_path
    HTMLBODY = email_body

    DoEvents
    Application.Wait 1

    Dim olApp As Object
    Dim olMail As Object

    Set olApp = CreateObject("Outlook.Application")
    Set olMail = olApp.CreateItem(0)
    With olMail
        .to = emailTo
        .Subject = emailSub
        .Attachments.Add (FullPath)

        .HTMLBODY = HTMLBODY
        DoEvents

        .Display
        Application.Wait 1
        .Send

    End With

    Application.Wait 1

    Set olMail = Nothing
    Set olApp = Nothing

End Sub

'Following function converts Excel range to HTML table
'Taken from https://excelsirji.com/vba-code-to-convert-excel-range-into-html-table/
Public Function ConvertRangeToHTMLTable(rInput As Range) As String
    'Declare variables
    Dim rRow As Range
    Dim rCell As Range
    Dim strReturn As String
    'Define table format and font
    strReturn = "<Table border='1' cellspacing='0' cellpadding='7' style='border-collapse:collapse;border:none'>  "
    'Loop through each row in the range
    For Each rRow In rInput.Rows
        'Start new html row
        strReturn = strReturn & " <tr align='Center'; style='height:10.00pt'> "
        For Each rCell In rRow.Cells
            'If it is row 1 then it is header row that need to be bold
            If rCell.Row = 1 Then
                strReturn = strReturn & "<td valign='Center' style='border:solid windowtext 1.0pt; padding:0cm 5.4pt 0cm 5.4pt;height:1.05pt'><b>" & rCell.Text & "</b></td>"
            Else
                strReturn = strReturn & "<td valign='Center' style='border:solid windowtext 1.0pt; padding:0cm 5.4pt 0cm 5.4pt;height:1.05pt'>" & rCell.Text & "</td>"
            End If
        Next rCell
        'End a row
        strReturn = strReturn & "</tr>"
    Next rRow
    'Close the font tag
    strReturn = strReturn & "</font></table>"
    'Return html format
    ConvertRangeToHTMLTable = strReturn
End Function

请注意，ConvertRangeToHTMLTable 不是我从中获取的函数： https ://excelsirji.com/vba-code-to-convert-excel-range-into-html-table/

我有一个帮助器/加载器函数 LoopThroughTable 作为发送与 Sheet1 上的表格一样多的电子邮件的一种方式，如下所示：

请注意，我也仅将特定工作表作为附件发送，但如果您不希望这样做，您可以修改该代码以删除该部分。任何问题请在评论中提出，如果这解决了您的问题，请考虑将其标记为正确答案。

附在此处的 Excel 表：https ://drive.google.com/file/d/1yO0HvonMV6HHyLRjmHS2PHVquIDvjI5S/view?usp=sharing