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!
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.Appl
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
Thanks for your help!
Glenn
__________________________
Sub Send_Email()
Dim outlookApp As Object
Dim outlookMail As Object
Set outlookApp = CreateObject("Outlook.Appl
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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.
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.
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
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
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
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
Glenn
VBA-Error.JPG
Premium Content
You need an Expert Office subscription to comment.Start Free Trial