Excel VBA Send Worksheet to Email via Outlook

Glenn Stearns
Glenn Stearns used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Rob HensonFinance Analyst

Commented:
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.
NorieAnalyst Assistant

Commented:
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 StearnsAnalyst

Author

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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

NorieAnalyst Assistant

Commented:
Glenn

What's the error message?

Is it anything to do with file access/permissions?
Glenn StearnsAnalyst

Author

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
Analyst Assistant
Commented:
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.
Glenn StearnsAnalyst

Author

Commented:
Norie...

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

Thanks much!
Glenn StearnsAnalyst

Author

Commented:
Thanks for the help - worked great!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial