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!
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!