How to send a mail from excel vba
To send an email from Excel VBA, you can use the Outlook
object library and the MailItem
class. Here's a step-by-step guide:
Prerequisites:
- You need to have Microsoft Outlook installed on your computer.
- You need to have the
Outlook
object library referenced in your VBA project. To do this, follow these steps:- Open the Visual Basic Editor (VBE) by pressing
Alt + F11
or by navigating toDeveloper
>Visual Basic
in the ribbon. - In the VBE, click
Tools
>References
in the menu. - Check if
Microsoft Outlook XX.X Object Library
(whereXX.X
is the version of Outlook you have installed) is listed and select it if it's not already checked.
- Open the Visual Basic Editor (VBE) by pressing
- You need to set a reference to the
Outlook
object library in your VBA project. To do this, follow these steps:- In the VBE, click
Tools
>References
in the menu. - Check if
Microsoft Outlook XX.X Object Library
is listed and select it if it's not already checked.
- In the VBE, click
Code:
Here's an example code that sends an email using Excel VBA:
Sub SendEmail()
' Set up the email object
Dim olApp As New Outlook.Application
Dim olMail As Outlook.MailItem
' Create a new email object
Set olMail = olApp.CreateItem(olMailItem)
' Set the email properties
With olMail
.Subject = "Test Email"
.Body = "This is a test email sent from Excel VBA."
.To = "[email protected]"
.CC = ""
.BCC = ""
End With
' Send the email
olMail.Send
' Clean up
Set olMail = Nothing
Set olApp = Nothing
End Sub
How to use the code:
- Open the Visual Basic Editor (VBE) by pressing
Alt + F11
or by navigating toDeveloper
>Visual Basic
in the ribbon. - In the VBE, insert a new module by clicking
Insert
>Module
in the menu. - Paste the code into the module.
- Replace the
[email protected]
with the actual email address you want to send the email to. - Run the code by clicking
Run
>Run Sub/User Form
in the menu or by pressingF5
. - The email will be sent using the default email client (Outlook).
Tips and variations:
- You can customize the email properties (subject, body, to, cc, bcc) to suit your needs.
- You can use the
Attachments.Add
method to attach files to the email. - You can use the
Display
method instead ofSend
to display the email in the default email client instead of sending it. - You can use the
Save
method to save the email as a draft instead of sending it.