Call Function To Send Email Without So Much Code In Excel

[Legacy 286866]

I have an excel spreadsheet that select pre-defined cells and from this creates and email when a user presses a button. This worked fine when I had about 3 to 4 rows of data but now I have over 500 rows.
Please bare with me here as my coding in VBA is not so good. What I would like to do is instead of duplicating the code for each row is have one function that gets called on each time. I want the code to work out the row from a link at the end of the Row (which I also need to figure out how to link to the VBA, I know how to do it via a button but a link at the end of each row would be much better). The Link will say send email. If the user presses this link, then it will select the row the link is on and send the email. Hope that makes sense. I just wanted 1 function this could be called from. Instead of having to duplicate the code each time for each row. Any ideas?
Any good ways of doing this? Please see my code and spreadsheet below.
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Sub SendEmail()
Dim objOutlook As Outlook.Application
Set objOutlook =New Outlook.Application
Dim objEmail As Outlook.MailItem
Set objEmail = objOutlook.CreateItem(olMailItem)
objEmail.Subject = Cells(2,1).Text
objEmail.Body ="============"& vbNewLine & Cells(2,3).Text & vbNewLine &"============"& vbNewLine & Cells(2,6).Text
objEmail.To= Cells(2,5).Text
objEmail.SentOnBehalfOfName ="test@test.com"
objEmail.Display
EndSub

</code>I have also attached an example of my spreadsheet. Please note the full on spreadsheet has over 500 records. This is a much condensed version:

https://drive.google.com/folderview...dnekVMREZmVWpPT3h6RmxHRGFRQ3RHYTQ&usp=sharing

 

[AlphaFrog]

You wouldn't need hyperlinks in column G for this to work. Just type in cell G2 Send Email and drag\copy it down column G for each used row. You could format the font color and underline it to make it "look" like a hyperlink if you want.

Then paste this code in the worksheet's code module. It replaces your existing code. Do not change the name of the procedure. It's triggered when the user selects a cell in column G that has Send Email as its value.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_SelectionChange([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
    [COLOR=darkblue]Dim[/COLOR] r As Long
    [COLOR=darkblue]If[/COLOR] Target.Count = 1 [COLOR=darkblue]Then[/COLOR]
        [COLOR=darkblue]If[/COLOR] Target.Value = "Send Email" [COLOR=darkblue]Then[/COLOR]
            r = Target.Row
            [COLOR=darkblue]With[/COLOR] CreateObject("Outlook.Application").CreateItem(0)
                .Subject = Cells(r, 1).Text
                .Body = "============" & vbNewLine & Cells(r, 3).Text & vbNewLine & "============" & vbNewLine & Cells(r, 6).Text
                .To = Cells(r, 5).Text
                .SentOnBehalfOfName = "test@test.com"
                .Display
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]

 

[Legacy 286866]

Absolutely blown away by this. I never thought of doing it this way. So many thanks Alpha Frog. You sir Rock. If I want to move the Send Email to another column instead of G is this simple to do?

Just seen you can enter Send Email anywhere on the sheet. This is amazing.

I have one last question.

There is a date field in column C. If its been on the sheet for 17 days I would like it to go Red (the row) how is this done in the conditional formatting?

 

[AlphaFrog]

You're welcome. Thanks for the feedback.

The code will trigger on any cell with Send Email in it. The column doesn't matter in this case.

 

[Legacy 286866]

Sorry Alpha Frog dude . Just realised you replied.

I have one last question.

There is a date field in column C. If its been on the sheet for 17 days I would like it to go Red (the row) how is this done in the conditional formatting?

 

[AlphaFrog]

There is a date field in column C. If its been on the sheet for 17 days I would like it to go Red (the row) how is this done in the conditional formatting?

Select your data range A2:G5 and use this as the CF formula:
Formula is =AND($C2<>"", $C2<=TODAY()-17)

 

[Legacy 286866]

Thanks so much. You rock.