Блог

Приведенный ниже код копирует выделение, которое я выбираю из листа Excel, и вставляет его в новое тело письма и добавляет подпись, но мне нужно, чтобы оно также добавляло первую строку (диапазон («A1: O1»)) в тело письма над выделением, которое оно вставляет, и что оно будет сохраните ширину диапазона, высоту, формат…

 Sub SendSelectedCells_inOutlookEmail()
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 strSig As String

'Copy the selection
Set objSelection = Selection
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 amp; "Temp for Excel" amp; Format(Now, "YYYY-MM-DD hh-mm-ss") amp; ".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
Set objTextStream = objFileSystem.OpenTextFile(strTempHTMLFile)
objNewEmail.Display
strSig = objNewEmail.HTMLBody
objNewEmail.HTMLBody = objTextStream.ReadAll amp; strSig

'You can specify the new email recipients, subjects here using the following lines:
'objNewEmail.To = "johnsmith@datanumen.com"
'objNewEmail.Subject = "DataNumen Products"
'objNewEmail.Send --> directly send out this email

objTextStream.Close
objTempWorkbook.Close (False)
objFileSystem.DeleteFile (strTempHTMLFile)
 

End Sub

Явно скопируйте заголовки перед копированием выделения и вставкой ниже

 Dim dblRH as Double

Set objSelection = Selection
'Copy Headers
dblRH = Rows(1).RowHeight
Range("A1:O1").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
     .RowHeight = dblRH
End With

'Copy Selection
objSelection.Copy

With objTempWorksheet.Range("A2")
     .PasteSpecial xlPasteValues
     .PasteSpecial xlPasteFormats
End With