Help modifying a macro to recognise user IDs

M

Monomeeth

Hi Everyone

Our organisation uses Microsoft Exchange Servers to manage email accounts
and each user is given a unique 5 character UserID. All email addresses are
in the form of firstname.lastname@ourdomain.

I have a macro which is used to check a person's name to see if outlook
recognises it and, if so, it identifies the person's UserID. The problem is
that the person's name is entered Column A for the macro to interrogate. I
would like to modify this macro so that I can enter the first name into
column A and the last name into Column B and for the macro to join the two
together as one name before doing its magic.

Any help would be most appreciated.

The code is below:
-----------------------------------------------------------------

Sub CheckUserIDsForEmail()
'
' CheckUserIDsForEmail Macro
'
Dim objOL As Object
Dim objMailItem As Object
Dim strUserIDs As String
Dim i As Integer
Dim c As Object
Dim objRecipient As Object
Dim strAddress As String

If MsgBox("This will check each of the names in the first column to see if
it is recognised by Outlook. Continue?", vbYesNoCancel) = vbYes Then

Application.Cursor = xlWait

Set objOL = CreateObject("Outlook.Application")
Set objMailItem = objOL.CreateItem(olMailItem)

With objMailItem

For Each c In ActiveSheet.UsedRange.Columns(1).Cells
If c.Value <> "UserIDs" And Trim(c.Value) <> "" Then
.To = c.Value
For i = 1 To .Recipients.Count
If .Recipients(i).Resolve Then
Cells(c.Row, c.Column + 1).Value = Cells(c.Row,
c.Column + 1).Value & .Recipients(i).Name & "; "
strAddress = .Recipients(i).Address
strUserIDs = Right(strAddress, 5)
Cells(c.Row, c.Column + 3).Value = Cells(c.Row,
c.Column + 3).Value & strUserIDs & "; "
Else
Cells(c.Row, c.Column + 2).Value = Cells(c.Row,
c.Column + 2).Value & .Recipients(i).Name & "; "
End If
Next i
End If
Next c

End With

Application.Cursor = xlDefault

End If

End Sub
 
J

Jacob Skaria

Try

..To = c.Value & "." & c.Offset(0, 1).Value

OR

..To = c.Value & "." & c.Offset(0, 1).Value & "domain.com"


If this post helps click Yes
 
T

thomas donino

Im not an expert but I've used this

To separate the name Jim.Smith into Jim Smith, with Jim in one column and
Smith in another, first add two columns next to your source column
then loop thru that source and parse the first names into one column and the
last names into another using the code below

use strFirstNames = Left("put your cell ref here", Len("put your cell ref
here") - InStr("put your cell ref here", "."))

use strLastNames = Right("put your cell ref here", Len("put your cell ref
here") - InStr("put your cell ref here", "."))
 
M

Monomeeth

Hi Thomas

Thanks for this. It's not what I was looking to achieve in this instance,
but I've ket this code because I can see that it will help me with something
else.

I very much appreciate your input!

Joe.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top