Macro Intro
If you work will multiple clients you might be assigned with separating data in Excel and sending this information from Outlook. To complete this task in Excel you could filter the data and move client specific information into separate Workbooks. Then in Outlook create new emails, enter the appropriate recipients, add an email subject and type out a message in the email body. Finally ensure you attach the correct Workbook to the appropriate email. This process takes a lot of time and leaves a lot of room for error. Avoid the potential to make a mistake and use VBA! VBA gives you a way to automatically create Outlook emails out of Excel and add Workbooks as attachments. This Excel macro will use the Folder Path indicated in the code to find potential email attachments. By Default, the Folder Path will be the same as the Active Workbook. Once run this macro will use the name of each Worksheet to find files with the same name. If a match is found a new email with be generated with the Workbook attached. The Outlook email generated will have the subject and body populated. With a few updates to the VBA code you can have specific recipients pull in based on the Worksheet name. This Excel Create Emails with Workbooks Attached macro can be used as the final action after using Excel Create Worksheet per Filter Value and Excel Save Worksheets as Workbooks. These macros used in this sequence provides an efficient way to communicate relevant information to multiple clients. You can automate an entire process when you combine macros together!See it in Action
Watch this video to see this macro in action.Code
Here is the code for this macro. Make sure the following References are setup before running it: Visual Basic For Applications, Microsoft Excel 16.0 Object Library
Customization
These segments of code can be customized to personalize this macro.By Default the Folder Path is the Active Workbook but you can change this to a different path if desired
By Default this macro will start at the 2nd Worksheet and loop through all the Worksheets following it. You can update 2 to a 1 to include the 1st Worksheet.
Update the Sheet Names and set the Recipients (also CCRecipients) with the appropriate email addresses
If CC’ing Recipients then update this code line to not be a comment
This code line controls what populates in the Email Subject
These code lines control what will populate into the Email Body
If you want the email to send automatically then update this code line to not be a comment
First time using VBA?
The Developer Tab is an additional section of the ribbon when activated allows you access to Visual Basic in Applications like Access, Excel, Outlook, PowerPoint, Word.
Setup Now
Setup Now
Once the Developer Tab is available you will have the capability to update your security to allow Macros to run in the current application.
Setup Now
Setup Now
A sub of code is a collection of objects and variables. For the code to successfully run a library of references needs to be set.
Setup Now
Setup Now
Subscribe as an Insider to receive additional rights.
If you like our content and want to show your support tip us here!
Contact Us
Looking to improve your computer processes?
Leverage & Lean is here to help!
Leverage & Lean
Is focused on improving computer processes by Leveraging programming and applying Lean principles. It is our goal to make you more efficient and effective on your computer producing “Less Clicks and More Results!”
