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:

  1. You need to have Microsoft Outlook installed on your computer.
  2. 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 to Developer > Visual Basic in the ribbon.
    • In the VBE, click Tools > References in the menu.
    • Check if Microsoft Outlook XX.X Object Library (where XX.X is the version of Outlook you have installed) is listed and select it if it's not already checked.
  3. 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.

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:

  1. Open the Visual Basic Editor (VBE) by pressing Alt + F11 or by navigating to Developer > Visual Basic in the ribbon.
  2. In the VBE, insert a new module by clicking Insert > Module in the menu.
  3. Paste the code into the module.
  4. Replace the [email protected] with the actual email address you want to send the email to.
  5. Run the code by clicking Run > Run Sub/User Form in the menu or by pressing F5.
  6. The email will be sent using the default email client (Outlook).

Tips and variations: