VBA Mismatch error on calendar meetings

[kelliott14]

Hello,
I've followed a tutorial online to create meeting invites in Outlook from Excel. As far as I can tell, I've followed the tutorial exactly (besides changing the details to my spreadsheet). Currently, it's working. I run the macro and it creates all the meeting invites with the correct details, everything is good. However, it still throws an error in Excel. I've had someone else test it on their laptop and same thing, it works but it throws an error. Hoping someone might be able to help please?

Here's the table it's pulling from:

Note: the cells here are populated by formulas. I've tried swapping the formulas for actual data and it's the same error.

Here's my code:

Option Explicit

Sub CalendarBuild()
    Dim OutApp As Outlook.Application, Outmeet As Outlook.AppointmentItem
    Dim I As Long, setupsht As Worksheet
    
    Set setupsht = Worksheets("Calendar Link")
    
    For I = 6 To Range("M" & Rows.Count).End(xlUp).Row
        Set OutApp = Outlook.Application
        Set Outmeet = OutApp.CreateItem(olAppointmentItem)
        
                                                
        With Outmeet
            .Subject = "Shift: " & setupsht.Range("O" & I).Value
            .Start = setupsht.Range("M" & I).Value
            .End = setupsht.Range("N" & I).Value
            .Body = setupsht.Range("P" & I).Value & vbLf & vbLf & "Email to request to change this shift"
            .BusyStatus = olFree
            .ReminderMinutesBeforeStart = 30
            .Display
        End With
        
    Next I
    Set OutApp = Nothing
    Set Outmeet = Nothing
    
End Sub

In the debugger it shows

.Start = setupsht.Range("M" & I).Value

as the line with the error.

Error message:
Run-time error'13':
Type mismatch.

I've confirmed the value I'm putting into .Start is a date (according to the debugger print).

 

[StephenCrump]

It looks like this line:

For I = 6 To Range("M" & Rows.Count).End(xlUp).Row
'should be
For I = 6 To setupsht.Range("M" & Rows.Count).End(xlUp).Row

But I'm guessing that you have a rogue cell, perhaps some text value, further down in 'Calendar Link'!M:M.

You'll need to have nothing below your dates if you're going to rely on .Range("M" & Rows.Count).End(xlUp).Row to locate the last row for the loop.

 

[Alex Blakenburg]

Do you know how to use the immediate window in the Visual Basic Editor ?
If so add this line before your With Outmeet line.

Debug.Print setupsht.Range("M" & I).Address & vbTab & setupsht.Range("M" & I).Value

What cell / line is the last line in the immediate window and what is the content of that line ?
If the immediate window is not visible Ctrl+G will display it.

 

[kelliott14]

Hi Alex & Stephen,
Thanks for replying! I've found the bug - from Stephen's comment

But I'm guessing that you have a rogue cell, perhaps some text value, further down in 'Calendar Link'!M:M.

It wasn't rogue though - it was meant to be there, the column has formula (=IFERROR(VLOOKUP(L7,allShifts_lookup,4,0),"")) ready to vlookup the date and time from another sheet. I have a UNIQUE formula in L6 which kicks off the process, so all I had to do was change the top to:

For I = 6 To Range("L" & Rows.Count).End(xlUp).Row

So it's only running the loop on rows with data and not getting stuck on "empty" rows of formula.
And now it works, no errors. Happy days!

 

[StephenCrump]

That's great, I'm glad we could help.