v1.0 – Excel Hint, how to use Excel VBA to send a copy of your Microsoft Excel workbook via email
- 0 Comments
Excel Hint, how to send a copy of your Microsoft Excel workbook to one or more users as an attachment to a Microsoft Outlook email, and how to do so programmatically by adding VBA code to your Excel workbook.
It is actually pretty easy to do, and there are plenty of different ways to do it, some simple, as below, and some more advanced, coming shortly. For over the next several weeks we will post a few of those methods, so check back frequently to see how those would work.
The code below was written by Microsoft Excel MVP, Jacob Hilderbrand.
If you have any questions about the code below, please post your question(s) on our Excel forums at vbaexpress.com.
Follow these steps to test the code on your computer (Or download the fully functional workbook at the end of this post)
- Create a new Excel workbook.
- Create a new procedure and name it ‘EmailWorkbook’.
- Create an Active X Command button on the worksheet.
- Drop the code below into the ‘EmailWorkbook’ procedure.
- Call the ‘EmailWorkbook’ in the command button module.
- From the Excel worksheet, click the Command button.
- Review the Outlook email that is created.
- This will show you just one of the many options available to you, based on the code added to the workbook.
Past the code below into your Visual Basic Editor
Option Explicit
Sub EmailWorkbook()
Dim AppOL As Object
Dim EmailItem As Object
Const olMailItem As Long = 0
‘Initialze Objects
Set AppOL = CreateObject(“Outlook.Application”)
Set EmailItem = AppOL.CreateItem(olMailItem)
‘Save Workbook
ActiveWorkbook.Save
‘Create Email
With EmailItem
.Subject = “Insert Subject Here”
.Body = “Insert message here”
.To = “User@Domain.Com”
.Attachments.Add ActiveWorkbook.FullName
.Display
End With
ExitSub:
‘Release Object Variables
Set EmailItem = Nothing
Set AppOL = Nothing
End Sub
Visit our sister site, VBAExpress.Com, you can reach us live at 1-844-VBA-Help, or you can email info@vbaexpress.com
Click Here to Download The Workbook: vba-code-to-send-email-from-excel-version-1-by-jacob-hilderbrand-at-excelandaccessllc