How to create a macro that allows to automatically send a specific sheet in excel workbook as email?

VinayKumar_B 

Creating an Excel macro to send emails requires using VBA (Visual Basic for Applications). The example below shows a simple VBA code to send 150 emails with a custom subject. Please note that to use this code, you need to have Outlook installed, and you may need to adjust security settings to allow programmatic access.

1. Open Excel and press `ALT + F11` to open the VBA editor.

2. Insert a new module by right-clicking on "VBAProject (Your Workbook Name)" > `Insert` > `Module`.

3. Copy and paste the following code into the module:

```vba

Sub SendCustomEmails()

    Dim OutlookApp As Object

    Dim OutlookMail As Object

    Dim i As Integer

    Dim EmailSubject As String

    Dim EmailBody As String

    ' Set up Outlook

    Set OutlookApp = CreateObject("Outlook.Application")

    ' Loop to send 150 emails

    For i = 1 To 150

        ' Customize subject and body for each email

        EmailSubject = "Custom Subject " & i

        EmailBody = "Hello, this is the body of email " & i

        ' Create a new mail item

        Set OutlookMail = OutlookApp.CreateItem(0)

        ' Set email properties

        With OutlookMail

            .Subject = EmailSubject

            .Body = EmailBody

            ' Add recipient email address

            .To = "email address removed for privacy reasons"

            ' Uncomment the line below if you want to send carbon copies (CC)

            '.CC = "email address removed for privacy reasons"

            ' Uncomment the line below if you want to send blind carbon copies (BCC)

            '.BCC = "email address removed for privacy reasons"

            ' Uncomment and customize the line below if you want to attach files

            '.Attachments.Add "C:\Path\To\Your\File.txt"

            ' Uncomment the line below to display the email before sending (for testing)

            '.Display

            ' Uncomment the line below to send the email without displaying

            '.Send

        End With

        ' Release the mail item object

        Set OutlookMail = Nothing

    Next i

    ' Release the Outlook application object

    Set OutlookApp = Nothing

End Sub

```

4. Customize the email subject, body, and recipient email address.

5. Uncomment lines as needed (e.g., for attaching files or displaying the email before sending).

6. Run the macro by pressing `F5` or choosing `Run` > `Run Sub/UserForm` from the menu.

Remember to thoroughly test the code with a small number of emails first, especially if sending emails automatically can have significant consequences.

Certainly! Below is an example VBA code for Excel that reads email addresses, names, companies, and custom subjects from specific columns and sends 150 emails using Outlook.

https://1drv.ms/i/s!Asyl-Lql_WUNiS_JaLcIQwnCCQJ8

```vba
Sub SendCustomEmails()
Dim OutlookApp As Object

Dim OutlookMail As Object
Dim ws As Worksheet
Dim LastRow As Long
Dim i As Long

' Set up Outlook
Set OutlookApp = CreateObject("Outlook.Application")

' Set the worksheet containing the email data (adjust the sheet name)
Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your actual sheet name

' Find the last row with data in column A
LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

' Loop to send emails
For i = 2 To WorksheetFunction.Min(LastRow, 151) ' Assuming data starts from row 2
' Read data from columns
Dim EmailAddress As String
Dim Name As String
Dim Company As String
Dim Subject As String

EmailAddress = ws.Cells(i, 1).Value ' Assuming email address is in column A
Name = ws.Cells(i, 2).Value ' Assuming name is in column B
Company = ws.Cells(i, 3).Value ' Assuming company is in column C
Subject = ws.Cells(i, 4).Value ' Assuming custom subject is in column D

' Create a new mail item
Set OutlookMail = OutlookApp.CreateItem(0)

' Set email properties
With OutlookMail
.Subject = Subject
.Body = "Hello " & Name & "," & vbCrLf & vbCrLf & "This is a custom email for " & Company & "."
.To = EmailAddress
' Uncomment the line below if you want to send carbon copies (CC)
'.CC = "email address removed for privacy reasons"
' Uncomment the line below if you want to send blind carbon copies (BCC)
'.BCC = "email address removed for privacy reasons"

' Uncomment and customize the line below if you want to attach files
'.Attachments.Add "C:\Path\To\Your\File.txt"

' Uncomment the line below to display the email before sending (for testing)
'.Display
' Uncomment the line below to send the email without displaying
'.Send
End With

' Release the mail item object
Set OutlookMail = Nothing
Next i

' Release the Outlook application object
Set OutlookApp = Nothing
End Sub
```

Make sure to adjust the sheet name and column references according to your actual data structure in Excel. Also, test the code with a small number of emails before running it for the entire dataset.

VinayKumar_B is there a way to send specific data from the sheet instead of the entire sheet?

Similar to mail merge but an automatic email instead?

VinayKumar_B   how can I send an excel sheet to a specific email address entered in the spreadsheet?