Results 1 to 2 of 2

Thread: Problem with string insert in email body

  1. #1

    Problem with string insert in email body

    Register for a FREE account, and/
    or Log in to avoid these ads!


    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

  2. #2
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Excel Version
    Office 365
    See 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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts