How to send automatic mail in vba macro

To send an automatic email using a VBA macro in Excel, 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. If not, click Browse and navigate to the C:\Program Files\Microsoft Office\OfficeXX\OUTLOOK.DLL file (where XX is the version of Office you have installed).
    • Click OK to close the References dialog box.

The VBA code:

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

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

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

    ' Set the email properties
    With olMail
       .Subject = "Automatic Email from Excel Macro"
       .Body = "This is an automatic email sent from an Excel macro."
       .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 VBE and create a new module by clicking Insert > Module in the menu.
  2. Paste the code into the module.
  3. Modify the code to suit your needs, such as changing the email subject, body, and recipient.
  4. Save the module by clicking File > Save in the menu.
  5. To run the macro, click Run > Run Sub/User Form in the menu, or press F5.
  6. The macro will send an automatic email using the specified settings.

Tips and variations: