Isolating first (given) name from cell value structured as Last Name, First Name for use in personal salutation of e-mail

[EvolvedFromQuincy]

Hi all,

I'm trying to automate sending filtered portions of reports in e-mail bodies sent to the people the report is filtered by. I'm already using Ron de Bruin's excellent range to HTML function, but I need some help making my e-mails more personal by starting with a first name salutation, "Hi 'person,'" I'm hoping there's some way I can isolate the first name from the Last Name, First name column (Column Q) as a variable that I can put into the .HTMLBody of the e-mail.

Also for reference, I need my macro to filter through about 7 different people and send them all custom range to HTML of their own filtered data. I'm not sure if there's a way to loop that, but would be interested if anyone has an elegant solution. Perhaps there's a way to loop through each unique non-empty/#N/A value and apply that as filter? If not, I'm fine with the redundancy of making 7 different 'With OutMails', and I realize the risk of manually having to edit this code each time a person changes roles or leaves the company.

And finally, as an etiquette question, is it normal to leave other peoples' signatures in comments of code? Most of this code is Ron de Bruin's, but at some point I deleted some comments before I realized it might be an issue.

With that, here is a sample of what I have so far:

Sub Test_Email()

    Application.ReferenceStyle = xlA1

    Dim OutApp As Object
    Dim OutMail As Object
    Dim rng As Range
    Dim StrBody As String
    Dim SigString As String
    Dim Signature As String

Set rng = Nothing

ActiveSheet.ShowAllData
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=17, Criteria1:= _
        "Smith, Bob"

Set rng = Selection.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    
    If rng Is Nothing Then
        MsgBox "The selection is not a range or the sheet is protected" & _
               vbNewLine & "please correct and try again.", vbOKOnly
        Exit Sub
    End If

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

StrBody = StrBody & "Hi " FirstNameVariable? & ","
StrBody = StrBody & "Something something something "
StrBody = StrBody & "Something else something else something else."
    
    For Each cell In Columns("R").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Value Like "?*@?*.?*" Then
        
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        
        With OutMail
        .to = Cells(cell.Row, "R").Value
        .cc = ""
        .BCC = ""
        .Subject = "Subject"
        .HTMLBody = StrBody & RangetoHTML(rng) & "<br>" & "Thank you," & "<br><br>" & Signature
        .Send
        End With
End If

<new filter for different person>
<repeat above>
    
    Set OutMail = Nothing
    Set OutApp = Nothing
    
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
    
End Sub

Thank you!

 

[OaklandJim]

If I understand what you want to do, see if this helps.

Function GetLastName(prCell As Range)

    Dim asName() As String

    asName = Split(prCell.Value2, ", ")

    GetLastName = asName(0)
    
End Function

 

[OaklandJim]

Or if feeding a string into the function rather than a cell...

Sub Test()
Dim sLastName As String
sLastName = GetLastName("Last, First")
Debug.Print sLastName
End Sub

Function GetLastName(psString)
    
    Dim asName() As String
    
    asName = Split(psString, ", ")

    GetLastName = asName(0)
    
End Function

 

[OaklandJim]

Sorry, posted wrong function. Here is first name from string.

Sub TestFName()
Dim sFirstName As String
sFirstName = GetFirstName("Last, First")
Debug.Print sFirstName
End Sub

Function GetFirstName(psString)
   
    Dim asName() As String
   
    asName = Split(psString, ", ")

    GetFirstName = asName(1)
   
End Function

 

[EvolvedFromQuincy]

Sorry, posted wrong function. Here is first name from string.

Sub TestFName()
Dim sFirstName As String
sFirstName = GetFirstName("Last, First")
Debug.Print sFirstName
End Sub

Function GetFirstName(psString)
  
    Dim asName() As String
  
    asName = Split(psString, ", ")

    GetFirstName = asName(1)
  
End Function

Thanks Jim! I'm not quite sure how to apply that function to the subroutine, but I'll try to figure it out. The goal will be to apply the function to the first cell of a filtered column, then apply the value to the .htmlBody in the e-mail.