Macro required for arranging data

Maybe :

Sub Test()
  Dim a, i As Long
  With Sheets("Sheet1")
    a = .Range("A2:O" & .Cells(.Rows.Count, "A").End(xlUp).Row)
    ReDim b(1 To UBound(a, 1), 1 To 1)
    For i = 1 To UBound(a, 1)
        If Len(a(i, 1)) Then b(i, 1) = "Hi " & Split(a(i, 15), " ")(0) & ", I wanted to apply for a " & a(i, 5) & " in " & Split(a(i, 7), " ")(0) & ", Kind regards, " & Split(Split(a(i, 1), "@")(0), ".")(0)
    Next i
    .Range("U2").Resize(UBound(b, 1)).Value = b
  End With
End Sub