How do I write a macro to convert one email format to another. i.e.,
(e-mail address removed) to (e-mail address removed) (the first and last names are
capitalized and need to be first letter of first name combined with last
name, all lowercase and change the multiple email providers and extensions to
"@usuhs.edu"?
To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.
To use this Macro (Sub), first select the range to be processed.
Then <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.
As written, the macro works on "Selection" and writes the result in the
adjacent cell. This can be altered. IT also does not test to see if the
initial email is properly formed. If not, it will just duplicate the contents
of the data cell. This can be changed, but you'll have to determine what you
want to happen.
====================================
Option Explicit
Sub NewEmail()
Dim c As Range
Dim rg As Range
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "(^.)[^.]*\.([^@]+)(.*$)"
Set rg = Selection 'or whatever
For Each c In rg
c.Offset(0, 1).Value = LCase(re.Replace(c.Value, "
[email protected]"))
Next c
End Sub
====================================
--ron
