Send email as To address to all email id stored in excel

**santbiju1** · 11-04-2015, 09:16 AM

Hi

i have an excel file having as per below

Name email id
=================
AAA AAA@1234.com
BBB BBB@1234.com
CCC CCC@1234.com
DDD DDD@1234.com

My requirement is if i open the outlook and after typing the matter and press send button i want to send the same email to all the address separately which is the excel file and the address should be in To address .

The name also should display at the beginning of the email content

ie Dear AAAA or Hi AAAA

<Content>

is it possible to do ie to send email to bulk of employees

**peterjuhnke** · 11-04-2015, 09:36 AM

I send many emails separately by doing this, I simply copy the addresses from excel and paste them into the BCC address bar, this way each player (player in my case; employer in your case)gets the same email. I think outlook has a maximum of 100 or120 addresses. Gmail is a lot higher; I'll send out to 250 or more email addresses with the one letter.
Getting the name in the start, well sorry in can't help with that.
Peter

**mainemojo** · 11-04-2015, 09:37 AM

Try this code. This is assuming you have names in column A and email addresses in column B.

Sub SendEmail()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range

    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")

    On Error GoTo cleanup
    For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Value Like "?*@?*.?*" Then

            Set OutMail = OutApp.CreateItem(0)
            On Error Resume Next
            With OutMail
                .To = cell.Value
                .Subject = "Reminder"
                .Body = "Dear " & Cells(cell.Row, "A").Value _
                      & vbNewLine & vbNewLine & "PUT YOUR EMAIL BODY IN HERE"
                '.Attachments.Add ("C:\test.txt")  Delete this and uncomment to send an attachment
                .Send
            End With
            On Error GoTo 0
            Set OutMail = Nothing
        End If
    Next cell

cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub

**6StringJazzer** · 11-04-2015, 11:20 AM

Originally Posted by mainemojo

Try this code.

mainemojo, include attribution if you provide code taken from another source.

**santbiju1** · 11-05-2015, 03:03 AM

Hi,

Thank you , its working,

More queries

1. is it possible that if i write all the body part in outlook 2013 and send emails from the excel list.
2. if my outlook 2013 is having 3 email ,how can i select the email id for sender.

Thank You

**Obsessed** · 11-04-2015, 09:38 AM

Assuming you have headers:

A1: Employee Name
B1: Employee Email
C1: Subject
D1: Salutation
E1: Body

You can use this:

Sub SendMail()
Dim OutApp As Object, OutMail As Object
Dim Body As String, Subject As String, Salutation As String, EmployeeName As String, EmployeeEmail As String
Dim LastEmployee As Long, Employee As Long

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")

LastEmployee = ActiveSheet.Range("A" & ActiveSheet.UsedRange.Rows.Count + 2).End(xlUp).Row
If LastEmployee = 1 Then
    MsgBox ("No Employees in List")
    Application.ScreenUpdating = True
    Exit Sub
End If

Subject = Range("C2").Value
Salutation = Range("D2").Value
Body = Range("E2").Value

For Employee = 2 To LastEmployee
    EmployeeName = Cells(Employee, 1)
    EmployeeEmail = Cells(Employee, 2)
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
        .To = EmployeeEmail
        .Subject = Subject
        .Body = Salutation & " " & EmployeeName & "," & vbNewLine & vbNewLine & Body
        .Send
    End With
    Set OutMail = Nothing
Next Employee

Set OutApp = Nothing
Application.ScreenUpdating = True
        
End Sub