"Find Next" and reverse concatenating

[vermilion]

Hi, new to VBA, have two questions.

As background, I am working with a large spreadsheet with hundreds of clients and info for each client. I am trying to do the following:

(1) Find email addresses and compose an email to the recipient. I have found macros to create the email using the address, I just need the code to, essentially, search the column for the next email address (the column does NOT consist solely of email addressess... if that would make things easier, I may be able to modify the spreadsheet).

(2) The Client's names are listed Lastname, Firstname. When I send emails, I address the clients by their first name only. I need a way to extract the first name from the cell containing their "lastname, firstname" and subsequently enter it in the text of the email.

Thanks in advance for the help. If you need any clarification, please let me know and I'll do what I can.

[Blade Hunter]

split("LastName,FirstName",",")(1)

Splits the string in to an array and takes element 1 (which is the second element).

Edit: If you have a space on the start just trim it.


trim(split("LastName,FirstName",",")(1))


Cheers

Dan

[Chabu]

To find an email in a text use a pattern matching tool.
Regular expressions to the rescue!
The following is compiled from http://www.tmehta.com/regexp/ for the code
and from http://www.regular-expressions.info/email.html for the email matching pattern.

include the following code
#Const LateBind = True

Function RegExpSubstitute(ReplaceIn, _
ReplaceWhat As String, ReplaceWith As String)
#If Not LateBind Then
Dim RE As RegExp
Set RE = New RegExp
#Else
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
#End If
RE.Pattern = ReplaceWhat
RE.Global = True
RegExpSubstitute = RE.Replace(ReplaceIn, ReplaceWith)
End Function

Public Function RegExpFind(FindIn, FindWhat As String, _
Optional IgnoreCase As Boolean = False)
Dim i As Long
#If Not LateBind Then
Dim RE As RegExp, allMatches As MatchCollection, aMatch As Match
Set RE = New RegExp
#Else
Dim RE As Object, allMatches As Object, aMatch As Object
Set RE = CreateObject("vbscript.regexp")
#End If
RE.Pattern = FindWhat
RE.IgnoreCase = IgnoreCase
RE.Global = True
Set allMatches = RE.Execute(FindIn)
ReDim rslt(0 To allMatches.Count - 1)
For i = 0 To allMatches.Count - 1
rslt(i) = allMatches(i).Value
Next i
RegExpFind = rslt
End Function

used in a worksheet =regexpfind(I6;"[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,4}")
this will return the first email address from the text in I6

Greetings