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:
- 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. If not, clickBrowse
and navigate to theC:\Program Files\Microsoft Office\OfficeXX\OUTLOOK.DLL
file (whereXX
is the version of Office you have installed). - Click
OK
to close the References dialog box.
- Open the Visual Basic Editor (VBE) by pressing
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:
- Open the VBE and create a new module by clicking
Insert
>Module
in the menu. - Paste the code into the module.
- Modify the code to suit your needs, such as changing the email subject, body, and recipient.
- Save the module by clicking
File
>Save
in the menu. - To run the macro, click
Run
>Run Sub/User Form
in the menu, or pressF5
. - The macro will send an automatic email using the specified settings.
Tips and variations:
- You can use the
olMailItem
object to attach files to the email by using theAttachments
property. - You can use the
olMailItem
object to set the email priority by using theImportance
property. - You can use the
olMailItem
object to set the email sensitivity by using theSensitivity
property. - You can use the
olMailItem
object to set the email categories by using theCategories
property. - You can use the
olMailItem
object to set the email expiration date by using theExpirationDate
property.