VBA excel to Outlook contact properties

[dniece]

I have a list of names in excel.
The Outlook alias is in one column and is what I am passing to create an outlook message.
What I really need is to also look up the managers name of the outlook contact.
This could be done with two separate macros if needed.
I was hoping that I could pass in the alias as a variable and retrieve the olListMember.Manager.Name from the contact properties.
I have tried a number of things.

My code to create the outlook message is working correctly. I just need to get that cc portion working.

Here is my macro.
Sub sendReminder()
Dim OutlookApp As Object
Dim MItem As Object
Dim cell As Range
Dim cellTwo As Range
Dim email_ As String
Dim ldr_ As String
Dim subject_ As String
Dim body_ As String
Dim attach_ As String

'Create Outlook object
Set OutlookApp = CreateObject("Outlook.Application")

' Loop through the rows
For Each cell In Columns("d").Cells.SpecialCells(xlCellTypeConstants)

email_ = cell.Value
ldr_ = cell.Offset(0, 2).Value
subject_ = "Please respond " & Range("I1").Value
body_ = Range("I2").Value


'Create Mail Item and send it
Set MItem = OutlookApp.CreateItem(0)
With MItem
.To = email_
' .CC = ldr_
.Subject = subject_
.Importance = 2
.Body = body_
.Display
' .Send
End With
Next
End Sub

I currently have the cc portion commented out as the value I was passing in is not the correct name in outlook. Hence why I want to find it via the alias name.
I take no credit for this macro. I have been searching most of the day today and found the code for this here on the excel forum.

On a side note - I did attempt another macro that passes in a DL list in the hopes that it would allow me to specify a DL list or a set of DL lists so that I could just create a lookup to the manager name, but I no matter how many references I updated in my vba editor I kept getting a CDO compile error. so I have tried a number of things.

I'm a total newbie so I apologize if I am not being clear.

Please pardon my

 

[JP2112]

Please wrap your code in code tags, otherwise it's much harder to follow.

Can you post an example row so we can see what kind of data you are working with? Names changed to protect the innocent, of course.

 

[dniece]

Sorry, my first post and I was unsure of what code tags were .. I see them now as an option. Hopefully I am sending the example data correctly.

There are no column headers so I have put the column letters at the bottom of the data example.

***** start of workbook example<TABLE style="WIDTH: 830pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1105><COLGROUP><COL style="WIDTH: 63pt" span=4 width=84><COL style="WIDTH: 107pt; mso-width-source: userset; mso-width-alt: 5193" width=142><COL style="WIDTH: 116pt; mso-width-source: userset; mso-width-alt: 5632" width=154><COL style="WIDTH: 63pt" width=84><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><COL style="WIDTH: 220pt; mso-width-source: userset; mso-width-alt: 10715" width=293><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 63pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=84>415</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 63pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=84>H488000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 63pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=84>No TS</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 63pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=84>h12345</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 107pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=142>myLastName, dniece</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 116pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=154>Doe, John</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 63pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=84></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; WIDTH: 72pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=96>Week Range:</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 220pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 width=293>10/8-10/14/2011</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=21>415</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">H488000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">No TS</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">s45678</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Smith, Suzie</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Doe, John</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>Message Text:</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>An automated process determined earlier this morning that that a reminder message needs sent. Please do not reply to this message, but take action as requested.</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>415</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">H488000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">No TS</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">j23456</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Jones, Harry</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Doe, John</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">B</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">C</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">D</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">E</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">F</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">G</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">H</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66>I</TD></TR></TBODY></TABLE>********* end of workbook example

I am not using the data in column A, B, C or G. This is a standardized spreadsheet that is being produced out of [let's say] Application ABC. The data in column E is the user name of the userid (Outlook alias) in column D. The problem is, Application ABC does not have to contain the same name as Outlook. So, Application ABC may have my formal name, while I have opted to have my nickname in Outlook. That is the same for column F. That is the manager name. I am not passed the manager userid/alias as it is not stored in Application ABC. Again, App ABC may have the manager name has Doe, John but Outlook may have it as Doe, John J. Thus the need to lookup the manager's name based on the alias.

I realize that I likely need a sub routine in my macro to pass in the alias in order to get the manager name first from the outlook contact information. As I notated, just passing in the alias as the TO: and sending the email is working - I've searched around for how to use the alias to determine the manager name but this is new territory to me and I have been unable to find an example. I've looked at your code on your website too -- but have only seen the entire GAL brought in to a spreadsheet.
Or -- I need a macro to run first that will look up the manager name in outlook and then run the macro to create the email. That is why I was trying to find out how to bring in the GAL & properties in the first place. (I failed at that.) I thought maybe if I had the GAL & the manager property from the organization tab, I could then do a lookup and then send the email.

Thanks in advance for any assistance. dNiece.

 

[JP2112]

If your name is "Smith, John" but your "official" Outlook email name is "Smith, Johnnie" -- just put "Smith, Johnnie" in your spreadsheet. Then it would always resolve. Parsing an alias and trying to figure out which contact it belongs to sounds painful and unnecessary. Or am I missing something here?

You could also just use email addresses, they will always resolve. I would put them in a separate column and reference them in your macro.

I don't think CDO will help you, you'll need to use the Redemption library to extract manager information (http://www.dimastr.com/redemption/home.htm).

Also, it's none of my business (as I like to say) but at your company do you always CC someone's manager when you send them an action item? Sounds like tweaking to me.

 

[dniece]

yah, I think you are missing something so I must not be explaining it well.
I can get the email to work using the userid/alias.

The application that is generating the list - the only link between it and outlook is the userid/alias. That is the only field I am given that matches outlook.

Again, I can get that part of the email to work.
I've mocked up the email so to keep it anonymous, so it's not really an action item as it is "you missed something go do it now and by the way we're telling your manager too". Personally, I think that bites the big one but some people just cannot be adults even at work.

The first time I ran the macro (which I got from this excel board-thanks much btw) I used the manager name as created from the application. some emails were sent in my test and some not -- all because the manager name could be correctly resolved as the two systems (application abc and outlook) happened to match. But that appears to be the exception as opposed to the norm. I could say more about naming conventions but I won't.

I think I'm just going to have to rethink this. Thanks for looking it to it. I may give the redemption piece a try just to see if I can.