VBA – Send email from excel

Now, It is really exceptionally easy to send emails from Excel.

A successful way of automating messaging and alerts is to send emails from Spreadsheet using VBA (Visual Basic for Applications). The VBA code example that shows how to send an email from Excel using Outlook as the email client is provided below. Before using this code, make sure Outlook is installed and configured on your machine.

Sub SendEmailFromExcel()
    Dim OutlookApp As Object
    Dim OutlookMail As Object
    Dim MailBody As String
    Dim Recipient As String
    Dim Subject As String
    
    ' Set email parameters
    Recipient = "recipient@example.com"
    Subject = "Subject of the email"
    MailBody = "This is the body of the email." & vbCrLf & _
               "You can add more text here."
    
    ' Create Outlook object
    On Error Resume Next
    Set OutlookApp = GetObject(, "Outlook.Application")
    If Err.Number <> 0 Then
        Set OutlookApp = CreateObject("Outlook.Application")
    End If
    On Error GoTo 0
    
    ' Create new email
    Set OutlookMail = OutlookApp.CreateItem(0)
    With OutlookMail
        .To = Recipient
        .Subject = Subject
        .Body = MailBody
        .Display ' Use .Send to send the email immediately without displaying
    End With
    
    ' Release objects
    Set OutlookMail = Nothing
    Set OutlookApp = Nothing
End Sub

It has a few easy steps involved in adding VBA code to Excel. An instruction manual for adding and using VBA code in Excel is provided below:

1. Open Excel and Access the VBA Editor:
   • Open the Excel workbook in which you want to insert the VBA code.
   • Press ALT + F11 on your keyboard. This shortcut will open the Visual Basic for Applications (VBA) editor.
2. Insert a New Module:
   • In the VBA editor, you’ll see the Project Explorer on the left side. If you don’t see it, press CTRL + R to show it.
   • Locate the workbook where you want to insert the code. It will be listed under “VBAProject (YourWorkbookName)”.
   • Right-click on your workbook’s name, select “Insert,” and then choose “Module.” This will insert a new code module.
3. Enter VBA Code:
   • In the module window that opens, you can now paste or type your VBA code. For example, you can paste the “SendEmailFromExcel” code provided earlier.
   • Customize the code according to your needs, updating variables, content, and other details.
4. Run the VBA Code:
   • Close the VBA editor by clicking the “X” button in the top-right corner or by pressing ALT + Q.
   • Go back to your Excel workbook.
   • Press ALT + F8 to open the “Macro” dialog box.
   • Select the macro you want to run from the list (e.g., “SendEmailFromExcel”) and click the “Run” button.
5. Adjust Macro Security Settings (Optional):
   • Depending on your Excel version and security settings, you might encounter a security warning or prompt when running macros. Excel’s default security setting might block macros. You can adjust these settings via:
     • Excel 2016 or later: File > Options > Trust Center > Trust Center Settings > Macro Settings
     • Older Excel versions: Tools > Macro > Security
   • You can choose to enable macros for the duration of your session or permanently by selecting a different security level.

Remember that VBA code can interact with and modify your Excel data, so it’s essential to use it responsibly and make sure you understand the code you’re implementing. Test your code on sample data before using it on critical spreadsheets.

Once you’ve successfully added and tested your VBA code, you’ll be able to automate various tasks and enhance your Excel experience.