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.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
VBAMicrosoft ExcelMicrosoft Office
Last Comment
Glenn Stearns
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
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
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
Glenn
What's the error message?
Is it anything to do with file access/permissions?
What's the error message?
Is it anything to do with file access/permissions?
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
Glenn
VBA-Error.JPG
ASKER CERTIFIED SOLUTION
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.
ASKER
Norie...
That was the problem. Changed to a valid directory and it worked fine.
Thanks much!
That was the problem. Changed to a valid directory and it worked fine.
Thanks much!
ASKER
Thanks for the help - worked great!
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.