VBA Code for to send email from outlook based on Cell Value

[Likith]

Hello All,

Can anyone help me in getting the code for below need

I would like to send an email based on cell value , for example A2 cell consist of email address and in A3 I have conditions like "Yes" or "No" , if A3 consist of "Yes" excel should trigger a email to email address in A2 by copying the value in A4 as a body of Email . This should continue for next rows .. ...

I have tried couple ways but nothing worked .

Any help here much appreciated!!

Thanks you so much in Advance

Regards ,
Likith

 

[daverunt]

Hi,

Here's some basic code with no error checking based on the data going down a single column as specified.......which is unusual.

It is assumed the mail data is always in the 3 rows as specified starting at A2
It starts checks for Yes or No in A3 to determine if an email is required then steps 3 rows to where the next Yes/No is expected until the last row in column A. Copying the data from the row above and below it for the mail address and content.

Sub Mail_it()

Dim OutApp As Object
Dim OutMail As Object

LastRow = Range("A" & Rows.Count).End(xlUp).Row

'Look in first row where Yes/No would reside every 3 rows
  For i = 3 To LastRow Step 3

   If UCase(Cells(i, 1).Value) = "YES" Then

'Address is offset 1 row above, Mail Body 1 row below
emailTo = Cells(i - 1, 1).Value
    emailBody = Cells(i + 1, 1).Value


Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

  With OutMail
.To = emailTo
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = emailBody
'.Send
.Display
End With

Set OutMail = Nothing
Set OutApp = Nothing
End If

Next
End Sub

 

[Likith]

Thank you So much Daverunt for taking time and doing this , I appreciate the help .

you are right I messed up with my request let me re-write my request

I would like to send an email based on cell value , for example A2 cell consist of email address and in B2 I have conditions like "Yes" or "No" , if B2 consist of "Yes" excel should trigger a email to email address in A2 by copying the value in C2 as a body of Email . This should continue for next rows .. ...

Could you please help here !

Regards ,
Likith

 

[Likith]

Hi,

Here's some basic code with no error checking based on the data going down a single column as specified.......which is unusual.

It is assumed the mail data is always in the 3 rows as specified starting at A2
It starts checks for Yes or No in A3 to determine if an email is required then steps 3 rows to where the next Yes/No is expected until the last row in column A. Copying the data from the row above and below it for the mail address and content.

Sub Mail_it()

Dim OutApp As Object
Dim OutMail As Object

LastRow = Range("A" & Rows.Count).End(xlUp).Row

'Look in first row where Yes/No would reside every 3 rows
  For i = 3 To LastRow Step 3

   If UCase(Cells(i, 1).Value) = "YES" Then

'Address is offset 1 row above, Mail Body 1 row below
emailTo = Cells(i - 1, 1).Value
    emailBody = Cells(i + 1, 1).Value


Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

  With OutMail
.To = emailTo
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = emailBody
'.Send
.Display
End With

Set OutMail = Nothing
Set OutApp = Nothing
End If

Next
End Sub

Thank you So much Daverunt for taking time and doing this , I appreciate the help .

you are right I messed up with my request let me re-write my request

I would like to send an email based on cell value , for example A2 cell consist of email address and in B2 I have conditions like "Yes" or "No" , if B2 consist of "Yes" excel should trigger a email to email address in A2 by copying the value in C2 as a body of Email . This should continue for next rows .. ...

Could you please help here !

Regards ,
Likith

 

[daverunt]

No problem. A few changes to rows, column code.

- You might want to add a line of code to change Yes to No in column B so that you don't repeat the emails by accident?
Cells (i, 2) = "No" placed inside the loop - before 'Next' will do that or you may have other ideas. I commented that out.

Sub Mail_it()

'Look in column B2 onward, where Yes/No would reside
LastRow = Range("B" & Rows.Count).End(xlUp).Row

For i = 2 To LastRow

If UCase(Cells(i, 2).Value) = "YES" Then

'Address is offset 1 column left, Mail Body 1 column right
     emailTo = Cells(i, 2 - 1).Value
    emailBody = Cells(i, 2 + 1).Value

Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

  With OutMail
.To = emailTo
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = emailBody
'.Send
.Display
End With

Set OutMail = Nothing
Set OutApp = Nothing
End If

'Cells(i, 2).Value = "No"
Next

End Sub