On UrbanPro

View My Profile       Proud to be a Teacher       Write a Review  

Thursday, February 19, 2015

VBA Code for excel to send multiple emails using Outlook

VBA Code for excel to send multiple emails using Outlook:

Where list of email address in workbook under column "A" and use this VBA code to send an email to each individual email address using MS outlook with the subject in cell "B1" and the body message in cell "B2".

This will send an E-Mail to every address in Column "A" starting at row 2 down to the last row.
At the moment it is set to display before sending.
If you don't want to see each E-Mail first, disable the display line and uncomment the Send line.


Code:

Sub SendEm()
Dim i As Integer, Mail_Object, Email_Subject, o As Variant, lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
Set Mail_Object = CreateObject("Outlook.Application")
For i = 2 To lr
       With Mail_Object.CreateItem(o)
           .Subject = Range("B1").Value
           .To = Range("A" & i).Value
           .Body = Range("B2").Value
           '.Send
           .display 'disable display and enable send to send automatically
    End With
Next i
       MsgBox "E-mail successfully sent", 64
       Application.DisplayAlerts = False
Set Mail_Object = Nothing
End Sub


Source

Popular Posts