copy cells and email

[jkessous]

Hi All,
First of all i would like to thank you for your support.

the help i need is with the following:

I have an excel sheet that contains a couple of colums. The two import ones are Column A and G
Column A contains cells with Names
Column G contains cells with yes/no

what i need is the following.
IF cell in column G contains "yes" (for a range of rows) then copy the cell in Column A on the same row to a different sheet called renew. or even better that it will send the name in the cell at Column A by email.

i have the code that will send me an email (just email) for each cell that contains "yes" i just need to know the second part how to add the name in Column A to the email.

thanks

this is the code

Sub message()
Dim OutApp As Object
Dim OutMail As Object
Dim body As String
         For Each c In Worksheets("Renewal").Range("G5:G7").Cells
               If c.Value = "yes" Then
     Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
            With OutMail
            .To = "jkessous@gmail.com"
            .CC = ""
            .BCC = ""
            .Subject = "Renewal required"
            .body = "Hi there"
          '  .Attachments.Add Dest.FullName
            'You can add other files also like this
            '.Attachments.Add ("C:\test.txt")
            .Send   'or use .Display
        End With
End If
Next
     End Sub

thanks again
Jonathan

 

[ketan.patel]

I do not know code but I know Function/Formula for copying name in different sheet:

Lets assume that you have one sheet named "LIST" with following cells:
A1 Name G1 yes/no
A2 Mr.ABC G2 yes
A3 Ms.XYZ G3 no
A4 Mr.PQR G4 YES
A5 Ms.EFG G5 Yes
.
.

Now in second sheet named "RENEW" you have to write following Function in cells(Any one colum A,B,etc.):
A1 Renewal
A2 =IF(AND(ISERROR(SEARCH("yes",LIST!G2))),"",LIST!A2)
A3 =IF(AND(ISERROR(SEARCH("yes",LIST!G3))),"",LIST!A3)
A4 =IF(AND(ISERROR(SEARCH("yes",LIST!G4))),"",LIST!A4)
A5 =IF(AND(ISERROR(SEARCH("yes",LIST!G5))),"",LIST!A5)
.
.
Drag and Drop till the number of Rows you need to apply.

If there is Yes then you will get Name as a Result, if there is No then your Result will be Blank Cell.

For code & mail, hope someone else will help you.

 

[daverunt]

Hi,

This will get the name in column A for Cells in G with a 'Yes'.
If that's what you need.

For Each c In Worksheets("Renewal").Range("G5:G7").Cells
         Num = c.Row
               If c.Value = "yes" Then
               StrName = Range("G" & Num).Offset(0, -5)
 
 
' and perhaps use it here
              .body = "Hi" & " " & StrName

 

[daverunt]

oops...

StrName = Range("G" & Num).Offset(0,[COLOR=red] -6[/COLOR])
 
or  
 
StrName = Range("A" & Num)

 

[jkessous]

That was quick and did the job.
thanks alot for the great support