Avatar of Glenn Stearns
Glenn Stearns
Flag for United States of America asked on

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
VBAMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Glenn Stearns

8/22/2022 - Mon
Rob Henson

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.
Norie

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 Stearns

ASKER
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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Norie

Glenn

What's the error message?

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

ASKER
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
ASKER CERTIFIED SOLUTION
Norie

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Glenn Stearns

ASKER
Norie...

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

Thanks much!
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Glenn Stearns

ASKER
Thanks for the help - worked great!