troubleshooting Question

Excel VBA Send Worksheet to Email via Outlook

Avatar of Glenn Stearns
Glenn StearnsFlag for United States of America asked on
VBAMicrosoft ExcelMicrosoft Office
8 Comments1 Solution630 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
NorieSenior Associate
Join our community to see this answer!
Unlock 1 Answer and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros