How to send a mail using vba

To send an email using VBA in Excel, you can use the Outlook.Application and Outlook.MailItem objects. 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 Microsoft 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 Object Library is listed and select it if it's not already checked.
3. Make sure you have the necessary permissions to send emails using Outlook.

Code:

Here's an example code that sends an email using VBA:

Sub SendEmail()
    ' Set up the Outlook application
    Dim olApp As New Outlook.Application
    Dim olMail As Outlook.MailItem

    ' Create a new email
    Set olMail = olApp.CreateItem(olMailItem)

    ' Set the email properties
    With olMail
       .Subject = "Test Email"
       .Body = "This is a test email sent using VBA."
       .To = "[email protected]"
       .CC = ""
       .BCC = ""
       .Send
    End With

    ' Clean up
    Set olMail = Nothing
    Set olApp = Nothing
End Sub

Explanation:

1. The code starts by setting up the Outlook application using Dim olApp As New Outlook.Application.
2. It then creates a new email using Set olMail = olApp.CreateItem(olMailItem).
3. The email properties are set using the With statement:
   • Subject: sets the subject of the email.
   • Body: sets the body of the email.
   • To: sets the recipient's email address.
   • CC and BCC: set the carbon copy and blind carbon copy recipients, respectively.
4. The email is sent using the Send method.
5. Finally, the code cleans up by setting the olMail and olApp objects to Nothing.

Tips and Variations:

• You can customize the email properties to suit your needs.
• You can use the Attachments.Add method to attach files to the email.
• You can use the HTMLBody property to set the email body as HTML.
• You can use the Display method instead of Send to display the email in the Outlook client instead of sending it.