Automatically Email Separate Sheets via Command Button Click

DimWSH

New Member
Joined
Oct 6, 2016
Messages
14
I have a code that I've been using forever to automatically email a workbook via a command button click. I tried to reformat this code to send 2 individual sheets (named: Pass, Pass Screenshot) from the workbook, but I can't quite get it to work. The sheets won't be active when the email is sent. This is the code I have so far, any help would be greatly appreciated:

Sub SendEmail()
ThisWorkbook.Save
Dim OutApp As Object
Dim OutMail As Object


Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)


On Error Resume Next
With OutMail
.to = "my email"
.Subject = "my subject" & Date
.Attachments.Add Sheets.Pass And Sheets.Pass Screenshot

.Display
End With
On Error GoTo 0


Set OutMail = Nothing
Set OutApp = Nothing
End Sub
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Thank you John. Is there a way to do it without having to create a new temporary workbook? The email function has to happen rather quickly.
 
Upvote 0
No, because .Attachments.Add requires a file.

Alternatively, to send the sheets in the email body look at the "Outlook object model (body)" options on that page.
 
Upvote 0
Thanks John, I ended up using a variation of the code in the link you provided:

tempFile = Environ("Temp") & "\Failed.xlsx"
Set wb = ThisWorkbook
wb.Save
'Creates a new workbook containing copied sheets
wb.Sheets(Array("Fail", "Fail Screenshot")).Copy
Set tempWB = ActiveWorkbook


'Ensures no temp workbook already exists (this can technically still fail if the file is open/locked)
If Len(Dir(tempFile)) <> 0 Then
Kill tempFile
End If
' Save & close the tempFile
tempWB.SaveAs tempFile
tempWB.Close
'Sends email to me
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)


On Error Resume Next
With OutMail
.to = "me"
.Subject = "subject" & Date
.Attachments.Add tempFile
.Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
End If
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top