Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need to determine sender email address in Outlook 2007

Status
Not open for further replies.

Edge003

Technical User
Sep 16, 2008
1
US
I have a macro in Excel 2007 that I'm designing to send a user a confirmation email once an appointment has been made. The macro opens an Outlook 2007 new message window with the date & time of the appointment. What I'm trying to do is detect the user's (sender's) email address (should be the "from:" by default) once the new message window is opened and CC the user in the confirmation email. I have read about the AddressEntry.GetExchangeUser method, but not sure how to use it. The user will be part of a GAL. Here is my code:

Sub Saveas()
Dim WshShell As Object
Dim OutApp As Object
Dim OutMail As Object

Set WshShell = CreateObject("WScript.Shell")
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

Dim strbody As String
strbody = "Hello," & vbNewLine & vbNewLine & _
"This is to confirm that you have an appointment with Finance on " & _
Workbooks("FinanceAppointments.xlsm").ActiveSheet.Range("G3") & " " & _
Workbooks("FinanceAppointments.xlsm").ActiveSheet.Range("H3") & vbNewLine & _
"between " & Workbooks("FinanceAppointments.xlsm").ActiveSheet.Range("C8") & "hours." & _
" Please make a note of your appointment timeslot." & vbNewLine & "Thank you." & _
vbNewLine & vbNewLine & _
"The Finance Office"

With OutMail
.To = "name@email"
.CC = ""
.BCC = ""
.Subject = "Finance Appointment Confirmation"
.Body = strbody
.Display
End With

WshShell.SendKeys ("%s")

On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

MsgBox "Your appointment request has been saved. Finance Appointments will now close.", vbInformation, "Appointment Confirmation"
Sheets("EnableMacros").Select
Sheets("EnableMacros").Range("F2").Select
ActiveWorkbook.Close True
Application.Quit
End Sub

Any help would be appreciated!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top