将选项卡复制到新的未打开的文件中，并发送包含正文和主题中的文本的电子邮件 - VBA - 宏

Question

有一段代码将文档中的复制选项卡附加到新文档中，并将其粘贴到新文档中。然后仅将新文档发送给预期的收件人。

有没有一种方法可以更改我打算发送的文件的名称？它只是作为Book1发送。

此外，我想在邮件的正文和主题标题中添加文本。我怎么能这样做呢？

Sub Sendtabonemail() 

Dim wb As Workbook 
Dim strbody As String 


    Set wb = Workbooks.Add 
    ThisWorkbook.Sheets("sheet6").Copy After:=wb.Sheets(1) 
    ThisWorkbook.Sheets("sheet3").Copy After:=wb.Sheets(1) 
    wb.Application.Dialogs(xlDialogSendMail).Show "" & "[email protected]" & "; " & "[email protected]" 

End Sub 

Answer 1

Sub dfjero() 
Dim newWBname As String 

    newWBname = Sheets(1).Name & "_" & Month(Date) & "_" & Day(Date) & "_" & Year(Date) 
    Workbooks.Add 
    If Len(Dir("c:\newFile", vbDirectory)) = 0 Then ' create and delete temporary directory and file 
     MkDir "c:\newFile" 
     ActiveWorkbook.SaveAs Filename:="C:\newFile\" & newWBname & ".xls", FileFormat:=xlExcel8 
     ' This is where you send the book via email 
     ActiveWorkbook.Close 
     On Error Resume Next 
     Kill "C:\newFile\" & newWBname & ".xls" 
     RmDir "C:\newFile\" 
     On Error GoTo 0 
    Else ' or add file to already created directory 
     ActiveWorkbook.SaveAs Filename:="C:\newFile\" & newWBname & ".xls", FileFormat:=xlExcel8 
     ' or alternatively this is where you send the workbook via email 
    End If 

End Sub 

Answer 2

它称之为Book1，因为这是一个新的工作簿的默认名称，直到它的保存。为了命名它，只需在发送之前将文件保存到临时位置（使用您选择的名称）。

wb.SaveAs "C:\temporary folder location\filename_to_use.xlsx"