Hello Excellers, I am going to share what I think is one of my favorite Macros to date. It emails the active workbook as an attachment from Outlook. How often do you send the same updated workbook to the same people every week?, yes me too weekly reports to the same people. Save some time with the cute little piece of code. If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips. If you want to see all of the blog posts in the Macro Mondays Series you can do so by clicking on the link below.
What Does The Macro Do?
This Macro will send the active workbook to email recipients that you specify.
How Does It Work?
The first thing we need to do as this code runs from Excel is to set a reference to Microsoft Outlook. This is easy enough.
- Tools – References – Select the Microsoft Outlook Reference.
- Hit Ok and you are all done. Now we can start writing that code.
FIRST. Open Visual Basic – by hitting ALT +F11 or Developer Tab – Right Click the Project or Workbook name – Insert – Module
Step 2. We need to declare a few variables for this Macro. This ensures that Excel creates a memory container for each of them.
Dim OLApp As Outlook.Application (this references the Outlook Application object)
Dim OLMail As Object ( this is the object variable that holds a mail item for us)
Step 3. This activates Outlook and begins a new session for us. This is the same as selecting New Message in Outlook Mail.
Step 4 . This is the meat of the code where we build our mail to send. We can include recipients, which are entered with quotes and separated by a semi-colon. We can also use the CC and BCC as indicated. We attach the Excel workbook with the code .Attachment.Add ActiveWorkbook.Fullname and using the ActiveWorkbook.Fullname, we set the current workbook as the attachment. Once the message is ready we can display the contents of the message to review it.
Step 5. This last piece of code releases the objects assigned to our variables and prevents the objects remaining in open memory by setting the variable to nothing.
Step 6. Test Your Code!!
Dont forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.