VBA Mismatch error on calendar meetings

kelliott14

New Member
Joined
Feb 2, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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:
1643858643831.png

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:
VBA 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).
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
It looks like this line:

Rich (BB code):
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.
 
Upvote 0
Do you know how to use the immediate window in the Visual Basic Editor ?
If so add this line before your With Outmeet line.
VBA Code:
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,223,362
Messages
6,171,638
Members
452,412
Latest member
MitchAgain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top