Excel VBA Send Worksheet to Email via Outlook

Below is code that works fine for sending an entire workbook to an Outlook email recipient using Excel VBA. I want to know how to modify this code so that I can send a single worksheet (not the whole workbook, and not necessarily the active worksheet) to an Outlook email recipient.

Thanks for your help!

Glenn
______________________________

Sub Send_Email()

Dim outlookApp As Object
Dim outlookMail As Object

Set outlookApp = CreateObject("Outlook.Application")
Set outlookMail = outlookApp.CreateItem(0)

With outlookMail
.To = "recipient@yourco.com"
.Subject = "Test email File"
.BodyFormat = 2
.HTMLBody = "Hi,<p>This is a test email from Excel using VBA."
.Attachments.Add ActiveWorkbook.FullName
.Importance = olImportanceHigh
.Send
End With

Set outlookMail = Nothing
Set outlookApp = Nothing

End Sub

Glenn StearnsAnalystAsked: 2017-11-09

Who is Participating?

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license! Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 37

Rob HensonFinance AnalystCommented: 2017-11-09

In order to send an individual worksheet it has to be copied out to its own workbook first.

Worksheet.Copy

will create a new workbook from a worksheet. I assume you can then just continue with the remainder of the scrip to send that workbook.

LVL 40

NorieAnalyst Assistant Commented: 2017-11-09

You will need to copy, and save, the worksheet you want to send to a new workbook.

However, once you've sent/saved the email you can delete the new workbook you created.

Here's some rough code to attach only the active sheet.

Sub Send_Email()
Dim outlookApp As Object
Dim outlookMail As Object
Dim wbTemp As Workbook
Dim strFileName As String

Set outlookApp = CreateObject("Outlook.Application")
Set outlookMail = outlookApp.CreateItem(0)

ActiveSheet.Copy

Set wbTemp = ActiveWorkbook

wbTemp.SaveAs "C:\ Test\" & wbTemp.Sheets(1).Name & ".xlsx", xlOpenXMLWorkbook

strFileName = wbTemp.FullName

wbTemp.Close False

With outlookMail
    .To = "recipient@yourco.com"
    .Subject = "Test email File"
    .BodyFormat = 2
    .HTMLBody = "Hi,<p>This is a test email from Excel using VBA."
    .Attachments.Add strFileName
    .Importance = olImportanceHigh
    .Send
End With

Set outlookMail = Nothing
Set outlookApp = Nothing

Kill strFileName 

End Sub

Open in new window

Glenn StearnsAnalystAuthor Commented: 2017-11-09

Norie...

The code works fine to line 14, then I get an error
:
wbTemp.SaveAs "C:\ Test\" & wbTemp.Sheets(1).Name & ".xlsx", xlOpenXMLWorkbook

The workbook I am putting this code into is 'Book1' and the sheet I want to send is 'Sheet2'.

I get an error message when using the line the way you wrote it. Should I replace some of your text with my actual workbook name and worksheet name?

Thanks!

Glenn

LVL 40

NorieAnalyst Assistant Commented: 2017-11-09

Glenn

What's the error message?

Is it anything to do with file access/permissions?

Glenn StearnsAnalystAuthor Commented: 2017-11-09

See attachment for the error message. It doesn't have anything to do with permissions...looks like a file-naming or locating error.

Glenn
VBA-Error.JPG

LVL 40

NorieAnalyst Assistant Commented: 2017-11-09

Glenn

In the code I posted try changing "C:\ Test\" here to a valid directory.

wbTemp.SaveAs "C:\ Test\" & wbTemp.Sheets(1).Name & ".xlsx", xlOpenXMLWorkbook

Open in new window

PS I can post code so the file is saved to a temporary directory.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial