How to Send Email from Excel
Sending an email from Excel might sound complex, but it’s actually pretty straightforward. By using a bit of VBA (Visual Basic for Applications) code, you can automate the process of sending emails directly from your Excel spreadsheet. This can be incredibly useful for sending bulk emails, alerts, or reports. Follow these steps to learn how to send an email from Excel.
Step-by-Step Tutorial: How to Send Email from Excel
This step-by-step guide will walk you through the process of sending an email from Excel. By the end, you’ll know how to use a simple VBA script to get the job done.
Step 1: Open Excel and Press ALT + F11
Open your Excel workbook and press ALT + F11 to open the VBA editor.
This will open the Visual Basic for Applications (VBA) editor, where you can write and edit code.
Step 2: Insert a New Module
In the VBA editor, click on ‘Insert’ and then select ‘Module’ from the dropdown menu.
You now have a blank module where you can write your VBA code. This module is where the magic happens.
Step 3: Write the VBA Code
Copy and paste the following VBA code into your new module:
Sub SendEmail()
Dim outlookApp As Object
Dim outlookMail As Object
Set outlookApp = CreateObject("Outlook.Application")
Set outlookMail = outlookApp.CreateItem(0)
With outlookMail
.To = "[email protected]"
.Subject = "Subject of the email"
.Body = "Body of the email"
.Send
End With
Set outlookMail = Nothing
Set outlookApp = Nothing
End Sub
This VBA script sets up an Outlook application instance and configures the email’s recipient, subject, and body before sending it.
Step 4: Customize the Code
Customize the .To
, .Subject
, and .Body
fields in the VBA script to meet your needs.
Feel free to replace "[email protected]" with the actual email address you want to send to, and change the subject and body text accordingly.
Step 5: Run the Code
Press F5 or click on the ‘Run’ button to execute the VBA script.
This action will send the email according to the parameters you’ve set in the VBA code.
Step 6: Save Your Workbook
Save your Excel workbook with the code included by clicking ‘File’ -> ‘Save As’, and choosing the ‘Excel Macro-Enabled Workbook’ (.xlsm) format.
This ensures that your VBA script is saved along with your workbook, so you can run it whenever you need to send an email.
Once you’ve completed these steps, an email will be sent from your Outlook application with the specified recipient, subject, and body text.
Tips for Sending Email from Excel
- Make sure Outlook is installed and configured on your computer before running the VBA script.
- You can add more email addresses by separating them with a semicolon in the
.To
field. - To include CC or BCC recipients, use
.CC = "[email protected]"
or.BCC = "[email protected]"
in the VBA code. - If you need to attach files, use
.Attachments.Add "C:pathtoyourfile.txt"
within theWith outlookMail
block. - Debug your VBA code by using the ‘Step Into’ function (F8) to execute your script line by line.
Frequently Asked Questions
Is it necessary to have Outlook installed?
Yes, Outlook must be installed and configured on your computer for the VBA script to work.
Can I send emails to multiple recipients?
Absolutely! Just separate each email address with a semicolon in the .To
field.
How can I include attachments in the email?
You can add attachments by using the .Attachments.Add
method within the With outlookMail
block of the VBA code.
Do I need to save my workbook in a specific format?
Yes, save your workbook as an ‘Excel Macro-Enabled Workbook’ (.xlsm) to ensure the VBA script is preserved.
What if the script doesn’t work?
Double-check your VBA code for any typos and ensure Outlook is set as your default email client. You can also use the ‘Step Into’ function (F8) to debug the script.
Summary
- Open Excel and press ALT + F11.
- Insert a new module.
- Write the VBA code.
- Customize the code.
- Run the code.
- Save your workbook.
Conclusion
Sending an email from Excel using VBA is a handy trick that can save you loads of time, especially when dealing with large datasets or needing to send repetitive reports. By following the steps outlined in this guide, you’ll be able to automate your emailing process straight from your spreadsheet.
Remember, the key ingredient here is the VBA script, which acts as the bridge between your Excel data and your Outlook email. Don’t be afraid to tweak the code to fit your specific needs.
Get comfortable with the VBA editor, and you’ll soon find all sorts of other fun and useful ways to automate tasks in Excel. If you found this guide helpful, stay tuned for more tutorials on automating tasks and enhancing your productivity.
Matt Jacobs has been working as an IT consultant for small businesses since receiving his Master’s degree in 2003. While he still does some consulting work, his primary focus now is on creating technology support content for SupportYourTech.com.
His work can be found on many websites and focuses on topics such as Microsoft Office, Apple devices, Android devices, Photoshop, and more.