VBA Meeting with .BusyStatus mismatch error

[lojup]

Hello all,

I'm new at VBA and I have some difficulties automating some properties of outlook emails

The goal is to set importance from a table indicating High, Normal or Low priority and then in the code complete the missing part.

The property of the method is:
.Importance = olImportanceHigh

And what I want is to achieve is something like the code below but it keeps saying type mismatch
.Importance = "olImportance" & setupsht.Range("F" & I).Text

I've found a solution using nested ifs and converting "High" to 3 and "Normal" to 2 and so on, but this is not what I'm looking for.
I also have the same problem with BusyStatus...

Is there any idea how to solve this issue?

I attach you my code:

Sub SendInviteToMultiple()

    Dim OutApp As Outlook.Application
    Dim OutMeet As Outlook.AppointmentItem
        
    Dim I As Long
    Dim setupsht As Worksheet
    
        
    Set setupsht = ActiveSheet
    
    For I = 8 To Range("A" & Rows.Count).End(xlUp).Row
    
        Set OutApp = Outlook.Application         
        Set OutMeet = OutApp.CreateItem(olAppointmentItem)
            
            With OutMeet
                
                .SendUsingAccount = OutApp.Session.Accounts(setupsht.Range("A" & I).Value)
                ' .SentOnBehalfOfName = "Sales@something.nl"
                
                .Subject = setupsht.Range("B" & I).Value
                
                .RequiredAttendees = setupsht.Range("C" & I).Value
                
                .OptionalAttendees = setupsht.Range("D" & I).Value
                                               
                    
                .BusyStatus = "ol" & setupsht.Range("E" & I).Value    ' this doesn't work. It says type mismatch
               
                .Importance = "ol" & setupsht.Range("F" & I).Value    ' this doesn't work. It says type mismatch
                
                .Categories = setupsht.Range("G" & I).Value
                
                If Not IsEmpty(setupsht.Range("H" & I).Value) Then
                        .Attachments.Add (setupsht.Range("H" & I).Value)
                End If
                
                .ReminderMinutesBeforeStart = setupsht.Range("I" & I).Value
                
                .Start = setupsht.Range("J" & I).Text & " " & setupsht.Range("K" & I).Text
                
                If setupsht.Range("L7").Value = "Duration" Then
                    .Duration = setupsht.Range("L" & I).Value
                    .Body = setupsht.Range("M" & I).Value
                    
                Else
                    .End = setupsht.Range("L" & I).Text & " " & setupsht.Range("M" & I).Text
                    .Body = setupsht.Range("N" & I).Value
                End If
                
                .MeetingStatus = olMeeting
                .Display
                
            End With
            

    Next I
    
    Set OutApp = Nothing
    Set OutMeet = Nothing

End Sub

 

[GWteB]

Hi & welcome to MrExcel!

The problem you are experiencing is due to the fact that you are trying to assign a text (is var type String) to a property which expects a different var type than String.
In both cases, a member of a specific Enumeration (OlImportance & OlBusyStatus) is expected. Those members are actually Constants of var type Long (and a String doesn't match a Long).
If you want to continue working with (readable) text pulled from worksheet ranges, you cannot avoid converting the text to the correct var type by means of an If-ElseIf construct.
This can be done by means of a separate function, so that your original code remains readable. If you're interested, let me know.

 

[lojup]

Hi & welcome to MrExcel!

The problem you are experiencing is due to the fact that you are trying to assign a text (is var type String) to a property which expects a different var type than String.
In both cases, a member of a specific Enumeration (OlImportance & OlBusyStatus) is expected. Those members are actually Constants of var type Long (and a String doesn't match a Long).
If you want to continue working with (readable) text pulled from worksheet ranges, you cannot avoid converting the text to the correct var type by means of an If-ElseIf construct.
This can be done by means of a separate function, so that your original code remains readable. If you're interested, let me know.

Yes I'm interested! Please could you tell how you would do it? Thanks you in advance!!!

 

[GWteB]

I would use two custom functions as per below. Then from your original code these two line needs to be changed:

Replace this ...

.BusyStatus = "ol" & setupsht.Range("E" & i).Value    ' this doesn't work. It says type mismatch
.Importance = "ol" & setupsht.Range("F" & i).Value    ' this doesn't work. It says type mismatch

with this ...

.BusyStatus = BusyStatusFromText(setupsht.Range("E" & i).Value)
.Importance = ImportanceFromText(setupsht.Range("F" & i).Value)

Additional dependencies:

Public Function BusyStatusFromText(ByVal argText As String) As OlBusyStatus
    If VBA.StrComp(argText, "olBusy", vbTextCompare) = 0 Then
        BusyStatusFromText = olBusy
    ElseIf VBA.StrComp(argText, "olFree", vbTextCompare) = 0 Then
        BusyStatusFromText = olFree
    ElseIf VBA.StrComp(argText, "olOutOfOffice", vbTextCompare) = 0 Then
        BusyStatusFromText = olOutOfOffice
    ElseIf VBA.StrComp(argText, "olTentative", vbTextCompare) = 0 Then
        BusyStatusFromText = olTentative
    ElseIf VBA.StrComp(argText, "olWorkingElsewhere", vbTextCompare) = 0 Then
        BusyStatusFromText = olWorkingElsewhere
    Else
        ' at least return something to prevent errors
        BusyStatusFromText = olFree
    End If
End Function

Public Function ImportanceFromText(ByVal argText As String) As OlImportance
    If VBA.StrComp(argText, "olImportanceHigh", vbTextCompare) = 0 Then
        ImportanceFromText = olImportanceHigh
    ElseIf VBA.StrComp(argText, "olImportanceLow", vbTextCompare) = 0 Then
        ImportanceFromText = olImportanceLow
    ElseIf VBA.StrComp(argText, "olImportanceNormal", vbTextCompare) = 0 Then
        ImportanceFromText = olImportanceNormal
    Else
        ' at least return something to prevent errors
        ImportanceFromText = olImportanceNormal
    End If
End Function