This guide will show you how to send emails directly from Microsoft Excel using Visual Basic for Applications (VBA).
Step 1: Enable Developer Tab in Excel
- Go to ‘File’ > ‘Options’ > ‘Customize Ribbon’.
- Check the ‘Developer’ checkbox and click ‘OK’.
Step 2: Access VBA Editor
- Press
Alt + F11to open the VBA Editor.
Step 3: Insert a New Module
- In the VBA Editor, right-click on ‘VBAProject (YourWorkbookName.xlsm)’ > ‘Insert’ > ‘Module’.
Step 4: VBA Code to Send Email
- Copy and paste the following VBA code into the 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]"
.CC = ""
.BCC = ""
.Subject = "This is the Subject Line"
.Body = "Write your email body here."
.Attachments.Add ("C:\\path\\to\\your\\file.pdf") ' Optional
.Send
End With
Set OutlookMail = Nothing
Set OutlookApp = Nothing
End Sub
Step 5: Run the VBA Script
- Close the VBA Editor and return to Excel.
- Press
Alt + F8, select ‘SendEmail’, and click ‘Run’.
Note: Ensure Microsoft Outlook is installed on your computer and properly set up with your email account. This script uses Outlook to send the email.
Additional Tips:
- Modify the .To, .CC, .BCC, .Subject, and .Body fields as needed for your email.
- If you don’t need to attach a file, remove or comment out the .Attachments line.
