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 with attachments using Outlook

VBA Code for excel to send multiple emails with attachments 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", the body message in cell "B2" and for the attachment, full file path in cell “B3”.

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.


Sub SendEm()
Dim i As Integer, Mail_Object, Email_Subject, o As Variant, lr As Long, file As Variant
lr = Cells(Rows.Count, "A").End(xlUp).Row
file = Range("B3").Value
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
           .Attachments.Add file
           '.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