.
Copy paste sheet from another workbook to the workbook being email :
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 :
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