☰

VBA 调用特定选择单元格的值放入电子邮件的主题中

tag-icon • tag-icon microsoft-excel vba
VBA 调用特定选择单元格的值放入电子邮件的主题中

我从 excel 工作簿中选择数据并发送到 outlook。我需要更新下面的 Vba 代码来调用与我选择放入电子邮件主题中对应的列 (F、G、H) 中特定单元格的值，但我不知道该怎么做！

例如：工单需要审核@区域“....”，字段“....”，平台“....”。

图纸数据

Sub Send_Selections_To_OutlookEmail()

    Dim objSelection As Excel.Range
    Dim objTempWorkbook As Excel.Workbook
    Dim objTempWorksheet As Excel.Worksheet
    Dim strTempHTMLFile As String
    Dim objTempHTMLFile As Object
    Dim objFileSystem As Object
    Dim objTextStream As Object
    Dim objOutlookApp As Outlook.Application
    Dim objNewEmail As Outlook.MailItem
    Dim Strbody As String

    
    'Set the selection
    Set objSelection = Nothing
    Set objSelection = Selection.SpecialCells(xlCellTypeVisible)
    Selection.Copy
 
    'Paste the copied selected ranges into a temp worksheet
    Set objTempWorkbook = Excel.Application.Workbooks.Add(1)
    Set objTempWorksheet = objTempWorkbook.Sheets(1)
 
    'Keep the values, column widths and formats in pasting
    With objTempWorksheet.Cells(1)
         .PasteSpecial xlPasteValues
         .PasteSpecial xlPasteColumnWidths
         .PasteSpecial xlPasteFormats
    End With
 
    'Save the temp worksheet as a HTML file
    Set objFileSystem = CreateObject("Scripting.FileSystemObject")
    strTempHTMLFile = objFileSystem.GetSpecialFolder(2).Path & "\Temp for Excel" & Format(Now, "YYYY-MM-DD hh-mm-ss") & ".htm"
    Set objTempHTMLFile = objTempWorkbook.PublishObjects.Add(xlSourceRange, strTempHTMLFile, objTempWorksheet.name, objTempWorksheet.UsedRange.Address)
    objTempHTMLFile.Publish (True)
 
    'Create a new email
    Set objOutlookApp = CreateObject("Outlook.Application")
    Set objNewEmail = objOutlookApp.CreateItem(olMailItem)
     
    'Read the HTML file data and insert into the email body
    objNewEmail.Display
    Set objTextStream = objFileSystem.OpenTextFile(strTempHTMLFile)
    Strbody = "<H5>Eng.</H5>" & "Kindly review the below item to close.<br>"
    
    objNewEmail.HTMLBody = Strbody & "<table align=""left"">" & objTextStream.ReadAll & "<br>" & "<br>" & objNewEmail.HTMLBody
    
    'You can specify the new email recipients, subjects here using the following lines:
    'objNewEmail.To = "[email protected]"
    objNewEmail.Subject = " PM need review to close @"
    'objNewEmail.Send --> directly send out this email
 
    objTextStream.Close
    objTempWorkbook.Close (False)
    objFileSystem.DeleteFile (strTempHTMLFile)
            
End Sub

答案1

来自 stackoverflow 的“Niton”，很有帮助地创建了这个 VBA，并且它有效

相关内容