Attach subject of email to a cell value?

[Jyggalag]

Dear all,

I currently have this VBA code (please note the part highlighted in bold):

Option Explicit

Private Const FilePath As String = "\\COMPANY.SSSS.COMPANY.NET\userdata\t5382304\home\Documents\TEST folder\"
Sub send_email_complete()
Dim OutApp As Object
    Dim OutMail As Object
    Dim i As Long
    Dim ws As Worksheet
   
    '~~> Change this to the relevant worksheet
    '~~> that has the emails (right now Sheet1 has it)
    Set ws = ThisWorkbook.Sheets("Sheet1")

    Set OutApp = CreateObject("Outlook.Application")
   
    '~~> Looping from rows 2 to 10 (update if necessary)
    For i = 2 To 10
        '~~> This creates a new email (so we can send out multiple emails)
        Set OutMail = OutApp.CreateItem(0)

With OutMail
.To = ws.Cells(i, 15).Value2
.BCC = ws.Cells(i, 16).Value2
[B].Subject = "Files for Everyone"[/B]
.HTMLBody = "Dear all,<br/>" & "<BR>" & _
"bunch of text that does not matter for this question" & "<BR>"
.Attachments.Add FilePath & ws.Cells(2, 17).Value2

.Display

End With
Next i

End Sub

This is linked to the following Excel sheet:

My question is, is there some way to code the SUBJECT in the email coding, so it says Greetings Dear (Entity Name), so for example it would say in the subject for nr. 1: "Greetings Dear Pentagon".

I have the entities currently in the range N2:N10, but I expect to have somewhere around N2:N150 for my actual version.

Would truly appreciate some assistance here!!

Thank you all!

Kind regards,
Jyggalag

 

[RoryA]

.Subject = "Greetings Dear " & ws.Cells(i, "N").value

should do it.

 

[Jyggalag]

.Subject = "Greetings Dear " & ws.Cells(i, "N").value

should do it.

Thank you so much RoryA!!

Much appreciated! It worked

Kind regards,
Jyggalag