Create a variable with just the first name from cell with full name

[AlanCT]

I have a spreadsheet which, simplified, is in this format:

EUName >>>>>Product>>>>>>>>>>email
John Smith>>>>Computer>>>>john.smith@abc.com
Sara Jones>>>>Monitor>>>>> sara.jones@abc.com

<end spreadsheet="">
And I have some code (simplified version listed below), that when I put the cursor on a given row, generates an email asking if the EUName would like the Product installed.


If the cursor is on the first row of data, the code:
Set lbEUName = Cells.Find(What:="EUName", SearchOrder:=xlByRows, LookAt:=xlWhole)
Set EUName = Cells(Selection.Row, lbEUName.Column)

creates a variable EUName which has a value of "John Smith"

My question is, what is another line(s) of code that would create a variable EUFirstName which would have a value of "John"?


Thanks,
Alan

<current code,="" simplified<start="" macro="">

<current code,="" simplified="">

Sub CreateEmail()


Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

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


Set lbEUName = Cells.Find(What:="EUName", SearchOrder:=xlByRows, LookAt:=xlWhole)
Set lbProduct = Cells.Find(What:="Product", SearchOrder:=xlByRows, LookAt:=xlWhole)
Set lbEUEmail = Cells.Find(What:="email", SearchOrder:=xlByRows, LookAt:=xlWhole)


Set EUName = Cells(Selection.Row, lbEUName.Column)
Set Product = Cells(Selection.Row, lbProduct.Column)
Set EUEmail = Cells(Selection.Row, lbEUEmail.Column)

strbody = "Hi " & EUName & "," & vbNewLine & vbNewLine & _
"Can we install the " & Product & "?" & vbNewLine

On Error Resume Next

With OutMail
.To = EUEmail.Value
.Subject = "Install of " & Product
.Body = strbody
.Display 'or use .Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub
</current></current></end>

 

[jmacleary]

Add this line after setting the value of EUName:

Set EUFirstName = Split(EUName, " ")(0)

 

[Yongle]

another way ...

Declare the variablle as a string Dim EUFirstName As String
BELOW Set EUName = Cells(Selection.Row, lbEUName.Column)

INSERT

EUFirstName = Left(EUName, InStr(EUName, " ") - 1)

 

[jmacleary]

another way ...

Declare the variablle as a string Dim EUFirstName As String
BELOW Set EUName = Cells(Selection.Row, lbEUName.Column)

INSERT

EUFirstName = Left(EUName, InStr(EUName, " ") - 1)

This works fine, except if there is only a first name in the field with no spaces after it, when you will get an error. My suggestion will cater for that ok.

 

[Yongle]

ADVERTISEMENT

@jmacleary - well spotted

How about....

EUFirstName = Left(EUName, InStr(EUName & " ", " ") - 1)

 

[jmacleary]

That works, Yongle.

 

[Yongle]

ADVERTISEMENT

@jmacleary - a little mod to your code to prevent a mismatch error ...

EUFirstName = Split(EUName, " ")(0)

 

[AlanCT]

Thank you jmacleary and Yongle for your responses. That does the trick.

 

[Yongle]

thanks for your feedback