Sending email from MS Project - CC managers? 1

[Asspin]

Ok, so what I am needing to do is send this email from Project. It is working fine, however I have a request to carbon copy the supervisors of the users who are being emailed. I am really at a loss as to how to do this... any ideas would be appreciated! I am getting the users out of a cell in Project, in normal semicolon deliminated, Outlook style. There is no supervisor info in the project, I would need to find a way to get it from Outlook. Here is my code... I know it is shoddy, but I am new to Project, I normally work in Excel. Any code cleanup would be helpful as well!

Sub SendEmail()
Dim strName As String, strTraining As String, strStart As String, strDuration As String, strContact As String, strLocation As String, strTime As String
    With ActiveProject.Application
        .ScreenUpdating = False
        .SelectTaskField Row:=0, Column:="Text1"
        strName = .ActiveCell.Text
        .SelectTaskField Row:=0, Column:="Name"
        strTraining = .ActiveCell.Text
        .SelectTaskField Row:=0, Column:="Start"
        strStart = .ActiveCell.Text
        .SelectTaskField Row:=0, Column:="Duration"
        strDuration = .ActiveCell.Text
        .SelectTaskField Row:=0, Column:="Contact"
        strContact = .ActiveCell.Text
        .SelectTaskField Row:=0, Column:="Text2"
        strLocation = .ActiveCell.Text
        .SelectTaskField Row:=0, Column:="Text3"
        strTime = .ActiveCell.Text
        .SelectTaskField Row:=0, Column:="Indicators"
        .ScreenUpdating = True
    End With
    
ESubject = "Upcoming Meeting"
SendTo = strName
CCTo = VBA.Environ("USERNAME") & "; " & strContact
Ebody = "Please plan to attend the following:" & vbNewLine & vbNewLine & _
"Topic: " & strTraining & vbNewLine & _
"Start Date: " & strStart & vbNewLine & _
"Estimated Length: " & strDuration & vbNewLine & _
"Contact(s): " & strContact & vbNewLine & _
"Location: " & strLocation & vbNewLine & _
"Time: " & strTime

Set App = CreateObject("Outlook.Application")
Set Itm = App.CreateItem(0)
With Itm
.Subject = ESubject
.To = SendTo
.CC = CCTo
.Body = Ebody
.Display 
End With
End Sub

Dan

 

[SkipVought]

Hi,

You would need access to a table that associates supervisors with employees.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Asspin]

Thats what I was afraid of, thanks Skip!

Dan