Macro to create an email from data in an excel sheet

[Alvinpereira]

Hi,

I have a code to refer to an excel sheet & create an email.

I need the code to be modified so that which ever row which contains "yes" in column "F" (name =Send file), a mail has to be created and an attachment inserted, with a specific body & subject. Can you please help me with this?

The data for subject , e-mail recipient are all in columns "b to e". Need to know how to loop it till the end.

Regards,
Alvin Pereira

PFB the Current Code:

Sub CreateMail()


Dim objOutlook As Object
Dim objMail As Object
Dim rngTo As Range
Dim rngSubject As Range
Dim rngBody As Range
Dim rngAttach As Range


Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.CreateItem(0)


With ActiveSheet
Set rngTo = .Range("B2")
Set rngSubject = .Range("C3")
Set rngBody = .Range("D4")
Set rngAttach = .Range("E5")
End With


With objMail
.to = rngTo.Value
.Subject = rngSubject.Value
.Body = rngBody.Value
.Attachments.Add rngAttach.Value
.Display 'Instead of .Display, you can use .Send to send the email _
or .Save to save a copy in the drafts folder
End With


Set objOutlook = Nothing
Set objMail = Nothing
Set rngTo = Nothing
Set rngSubject = Nothing
Set rngBody = Nothing
Set rngAttach = Nothing


End Sub