Solved: Outlook Email Blast from Excel

[Kindly_Kaela]

Hi Everyone,

A Sales Rep in my company wants to blast out emails from Outlook to all his prospects that are stored in Excel.

Column A - Name
Column B - Company
Column C - email address

Can someone please help me write a macro that will grab that data from each row and send out multiple emails to the email address (column C).

The macro would also grab the name and company and insert them in specific spots of the email.

Thank you!!
Kaela

[Bob Phillips]

[vba]

Private OLApp As Object
Private OLNS As Object

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

Set OLApp = CreateObject("Outlook.Application")
Set OLNS = OLApp.GetNameSpace("MAPI")
OLNS.Logon , , True

For i = 2 To LastRow

MailIt .Cells(i, "C").Value, .Cells(i, "A").Value, .Cells(i, "B").Value
Next i

End With

End Sub

Private Sub MailIt(eMail As String, pName As String, pCompany As String)
Dim oMailItem As Object
Dim oRecipient As Object

Set oMailItem = OLApp.CreateItem(0)
Set oRecipient = _
oMailItem.Recipients.Add(eMail)
oRecipient.Type = 1
With oMailItem
.Subject = "Here is my subject"
.Body = "To: " & pName & vbNewLine & _
"of: " & pCompany & vbNewLine & vbNewLine & _
"This is an automatic email notification."
.Send 'use .Display to test
End With

End Sub
[/vba]