Attach an excel file to outlook email without sending it

Hi

Please assist

I want to copy an excel sheet from workbook a to a new workbook(workbook b) and send that workbook b to people using outlook. However I want to type new comments in the body of the email and manually press the send button.

Reason:
Emailing list of people change as well as instructions.

.

Copy paste sheet from another workbook to the workbook being email :

Code:

---

Option Explicit


Sub Add_Bridge_1()
    Dim wbk1 As Workbook, wbk2 As Workbook
    Dim fileStr As String
    Dim ws2 As Worksheet


    'add your own file path
   
    fileStr = "C:\Users\My\Desktop\New Workbook Name.xlsx"


    Set wbk1 = ActiveWorkbook
    Set wbk2 = Workbooks.Add(fileStr)
   
   
   
    'wbk2.Sheets("WORKINGS").Copy After:=wbk1.Sheets("Sheet3")
    wbk1.Sheets("Sheet1").Copy wbk2.Sheets("Sheet2").Paste
 
    wbk2.Saved = True
End Sub

---

This macro will allow you to select which file to attach to the email, then it displays the email so you can edit anything desired before sending :

Code:

---



Sub SendEmail()
 
    Dim xStrFile As String
    Dim xFilePath As String
    Dim xFileDlg As FileDialog
    Dim xFileDlgItem As Variant
    Dim xOutApp
    Dim xMailOut
    Dim olMailItem
   
    Application.ScreenUpdating = False
    Set xOutApp = CreateObject("Outlook.Application")
    Set xMailOut = xOutApp.CreateItem(olMailItem)
    Set xFileDlg = Application.FileDialog(msoFileDialogFilePicker)
    If xFileDlg.Show = -1 Then
        With xMailOut
            .To = "happy.xuebi@163.com"
            .Subject = "test"
            .HTMLBody = "test"
            For Each xFileDlgItem In xFileDlg.SelectedItems
                .Attachments.Add xFileDlgItem
            Next xFileDlgItem
            .Display
        End With
    End If
    Set xMailOut = Nothing
    Set xOutApp = Nothing
    Application.ScreenUpdating = True


End Sub

---

Thank you so much. I modified it slightly to add the attachment I want.

.
Glad you have your answer. Happy to help.

How can I make the .To field pull all the names from a table on another worksheet?