Attach an excel file to outlook email without sending it

[Oran]

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.

[Logit]

.

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

[Oran]

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

[Logit]

.
Glad you have your answer. Happy to help.

[majewski1978]

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