View Full Version : Problem with string insert in email body

2013-01-24, 05:05 AM

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

2014-10-03, 06:44 AM
See http://msdn.microsoft.com/en-us/library/office/aa171472(v=office.11).aspx the field is called location

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
End Sub