Send and email using an Outlook template

[Razor_Rob]

Hi everyone,

I would like to add a simple "Send an Email" text in a column which then opens up a saved Outlook Template and populate the data like email addresses to send it to, Names, Company Name.
Ie
Cell A
A1- "Send Email"
Column B
B1 - "Full Name 1"
Column C
C1 - "Student ID"
Column-D
D1 - "Email Address 1"
Column E
E1 - "Full Name 2"
Column F
F1 - "Employee ID"
Column G
G1 - "Email Adress 2"
And so on....

The Excel data gets populated manually meaning added names weekly. So I would like to drag the cell with the code "Send Email" to the new rows. When the Send Email link gets clicked the information on that row gets picked and an email template opens up with the data on it.

On the email the email address to send it to = D1 and G1
Subject = B1 and C1
Email message from the template will take B1 and E1

I don't want to send it automatically, I would like to review and add stuff on it.

Can anyone point me where I can start to write these code? I've seen some youtube videos writing formula but I'm not sure if this will work.

Thanks in advance.

 

[Razor_Rob]

Sub SendEmailPreview()

Dim OutApp As Object, OutMail As Object
Dim link As String, recipient As String, cc As String, subject As String, body As String

' Check if trigger button is clicked (assuming separate clicks)
If Range("A1").Value = "Send Email" Then

    ' Get data from the same row (assuming data starts from row 1)
    row = Range("A1").Row
    link = Range("B" & row).Value
    recipient = Range("C" & row).Value
    cc = Range("D" & row).Value
    subject = Range("E" & row).Value
    body = Range("F" & row).Value

    ' Construct email content with link
    body = "This is a preview of the email body. " & vbNewLine & _
           "Click the link below for more information: " & vbNewLine & _
           "<a href=\"" & link & "\">Link Text</a>"

    ' Create Outlook objects and set email properties
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
        .To = recipient
        .CC = cc
        .Subject = subject
        .HTMLBody = body
        .Display ' Preview the email as draft
    End With

    ' Clean up
    Set OutMail = Nothing
    Set OutApp = Nothing

End If

End Sub