Thread: Debug error '13' - Type Mismatch

First off Thanks for the Title change, explains it better!!

Secondly I have dates in column J, formatted to show as dates, however just changed to show as text for a test run and still no joy.

Oh and it does add the appointments to Outlook anyway. So it appears to run but with this error??

Yeah I'm not sure what outlook uses either, will do some more research on that, I've tried taking DateValue out before and whilst I had no errors it took the date of 30/10/2010 and put in in the calendar as Sat 30/12/1899, slight difference there!

What's annoying is I'm sure I've had it running before, will do some more digging and once I have it sussed will post the answer (fingers crossed!)

Ok I have checked it again without DateValue, now I am confused, it appears that the dates are getting entered as I wanted, however each time I run it 5 extra appointments are added with the date of 30/12/1899 and not sourcing any other information?? Ideas?? This is what I have entered in J, first line is blank followed by:


DATE OF DEPARTURE
30/10/2010
31/10/2010
01/11/2010
02/11/2010
03/11/2010
04/11/2010
05/11/2010
06/11/2010
07/11/2010
08/11/2010
09/11/2010
10/11/2010
11/11/2010
12/11/2010

I love code when it works!!

ahhhhh, now I thought like you did blank data at the start, so checked for hidden rows etc etc

But I have the cells formatted so they are all pretty with 5 blank rows after my last row of data!! Seeing you write that made me recheck and "oh look 5 blank rows" just at the bottom and not the top. Hence the random appointments being added. Just need some code to stop it when it hits an empty cell.

I can't thank you guys enough for helping me through this, I'm happy I was on the right track just with your input it made me take one step at a time.

THANK YOU, THANK YOU, THANK YOU, THANK YOU, THANK YOU, THANK YOU, THANK YOU, THANK YOU!!!!!!!!

I was just trying to work out what to do and if it was worth changing another code I have to format the cells as it added (not sure I'm up to that job at all yet!!) but this worked perfectly.

Finished code is:

HTML Code: 

Sub AddToOutlook()

'!! Reference to Outlook object library required !!

    Dim olAppointment As Outlook.AppointmentItem
    Dim olApp As Outlook.Application
    Dim lngRow As Long, shtSource As Worksheet


    'Get reference to MS Outlook
    On Error Resume Next
    Set olApp = GetObject(, "Outlook.Application")
    If Err.Number <> 0 Then
        Set olApp = CreateObject("Outlook.Application")
    End If

    On Error GoTo 0



    Set shtSource = ActiveSheet


    For lngRow = 3 To shtSource.Cells(Rows.Count, 10).End(xlUp).Row

        Set olAppointment = olApp.CreateItem(olAppointmentItem)

        With olAppointment
            .Subject = shtSource.Cells(lngRow, 2) & " " & shtSource.Cells(lngRow, 3) & " " & shtSource.Cells(lngRow, 4)
            .Start = shtSource.Cells(lngRow, 10)
            .Duration = 100
            .Location = shtSource.Cells(lngRow, 6)
        .Body = shtSource.Cells(lngRow, 6) & " to " & shtSource.Cells(lngRow, 7) & " Departing " & shtSource.Cells(lngRow, 10) & vbNewLine _
                & shtSource.Cells(lngRow, 8) & " to " & shtSource.Cells(lngRow, 9) & " Arriving " & shtSource.Cells(lngRow, 11)
        .BusyStatus = olBusy
        .ReminderMinutesBeforeStart = 10080
        .ReminderSet = True
            .Save
        End With

    Next lngRow

End Sub

Yes Sir I have done it now hehehe

Cheers guys