Clickable hyperlinks that create emails based on values in the same row?

Question

I'm trying to create a macro that runs when a hyperlink that references itself is clicked. I'd like to create an outlook email with predefined TO, CC, Subject, FROM, and hopefully the signature line. The target cell would be the hyperlink, and the above mentioned values are already stored in cells in the same row (to the right).

Email      To     CC    From   Subject  Body     Store    Variance   Date
Email now  Value  Value Value  Value    Not sure Value    Value      Value

All values are currently stored as text strings. I'd like for the user to be able to click "Email Now" and have an email generated based on the cells in the same row to the right.

Example Email:
From: [email protected]
To: [email protected]; [email protected]
CC: [email protected]; [email protected]; <- CC line varies based on variance value
Subject: Store# Product type date variance
Body: Premade text with values as per above also in here

Can the users signature be added as well?

I've looked through ron de bruin's email information, I'm just not the greatest at this stuff and couldn't figure it out.

Answer 1

Here is the most simple way to achieve what you want.

TRIED AND TESTED

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim Rw As Long

    If Target.Range.Column = 1 Then
        If UCase(Target.Range.Value) = "EMAIL NOW" Then
            Rw = Target.Range.Row

            Dim OutApp As Object, OutMail As Object

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

            With OutMail
                .To = Cells(Rw, 2)
                .CC = Cells(Rw, 3)
                '
                '~~> So On
                '

                .Display
            End With
        End If
    End If
End Sub