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:
Who is Participating?
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.

Rob HensonFinance AnalystCommented:
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.
0
NorieVBA ExpertCommented:
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

0
Glenn StearnsAnalystAuthor Commented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Download your Guide
NorieVBA ExpertCommented:
Glenn

What's the error message?

Is it anything to do with file access/permissions?
0
Glenn StearnsAnalystAuthor Commented:
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
0
NorieVBA ExpertCommented:
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.
0

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
Glenn StearnsAnalystAuthor Commented:
Norie...

That was the problem. Changed to a valid directory and it worked fine.

Thanks much!
0
Glenn StearnsAnalystAuthor Commented:
Thanks for the help - worked great!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.