May 13 2016

v1.0 – Excel Hint, how to use Excel VBA to send a copy of your Microsoft Excel workbook via email

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)

  1. Create a new Excel workbook.
  2. Create a new procedure and name it ‘EmailWorkbook’.
  3. Create an Active X Command button on the worksheet.
  4. Drop the code below into the ‘EmailWorkbook’ procedure.
  5. Call the ‘EmailWorkbook’ in the command button module.
  6. From the Excel worksheet, click the Command button.
  7. Review the Outlook email that is created.
    1. 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

 

 

Random Posts

Did you find this post helpful? Try signing up for our free daily tips.

Enter your email address: 

          

LEAVE A COMMENT

Subscribe Form

Subscribe to Blog

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories

Archives