Problem with string insert in email body

sjbtax

New member
Joined
Jan 24, 2013
Messages
1
Reaction score
0
Points
0
Hi,


I am trying to code a macro to automatically create an email to confirm an appointment from the details of the appointment made. When the appointment is selected or open the macro is run and the email populates with the information that I need. As part of the email I confirm the Appointment Location (either at the address specified in the location field or defaulting to our office address if the location field is left blank)


I have confirmed that the macro is returning the correct details by displaying them in a MsgBox, but I cannot get the macro to place the correct location address in the body of the email.


Can anybody help with where I am going wrong.




Public Sub Appointment_Confirmation()
Dim Item As AppointmentItem
Dim objMsg As MailItem
Dim AppointLocation As String
Set objMsg = Application.CreateItem(olMailItem)


Set Item = GetCurrentItem()


With objMsg
.Subject = "Appointment Confirmation - " & Item.Subject
.Body = "Hi " & Left$(Item.Subject, InStr(1, Item.Subject, " ") - 1) & "," & vbCrLf & _
"Appointment confirmed for " & Item.Start & vbCrLf & _
"Address: " & AppointLocation & vbCrLf & _
vbCrLf & _
"Regards," & vbCrLf & _
"My name" & vbCrLf & _
"My phone number"




If Len(Item.Location) <> 0 Then
AppointLocation = Item.Location
MsgBox AppointLocation
End If


If Len(Item.Location) = 0 Then
AppointLocation = "Office Address"
MsgBox AppointLocation
End If




.Display ' use .Send to send it instead
End With




Set objMsg = Nothing
Set Item = Nothing
AppointLocation = vbNullString


End Sub


Function GetCurrentItem() As Object
Dim objApp As Outlook.Application


Set objApp = Application
On Error Resume Next
Select Case TypeName(objApp.ActiveWindow)
Case "Explorer"
Set GetCurrentItem = objApp.ActiveExplorer.Selection.Item(1)
Case "Inspector"
Set GetCurrentItem = objApp.ActiveInspector.CurrentItem
End Select


Set objApp = Nothing
End Function
 

WizzardOfOz

New member
Joined
Sep 4, 2013
Messages
184
Reaction score
0
Points
0
Location
Australia
Excel Version(s)
Office 365
See http://msdn.microsoft.com/en-us/library/office/aa171472(v=office.11).aspx the field is called location

Code:
Sub ScheduleMeeting()
	Dim myOlApp As Outlook.Application
	Dim myItem as AppointmentItem
	Dim myRequiredAttendee As Recipient
	Dim myOptionalAttendee As Recipient
	Dim myResourceAttendee As Recipient
	Set myOlApp = CreateObject("Outlook.Application")
	Set myItem = myOlApp.CreateItem(olAppointmentItem)
	myItem.MeetingStatus = olMeeting
	myItem.Subject = "Strategy Meeting"
	myItem.Location = "Conference Room B"
	myItem.Start = #9/24/2002 1:30:00 PM#
	myItem.Duration = 90
	Set myRequiredAttendee = myItem.Recipients.Add ("Nate Sun")
	myRequiredAttendee.Type = olRequired
	Set myOptionalAttendee = myItem.Recipients.Add ("Kevin Kennedy")
	myOptionalAttendee.Type = olOptional
	Set myResourceAttendee = myItem.Recipients.Add("Conference Room B")
	myResourceAttendee.Type = olResource
	myItem.Send
End Sub
 
Top