VBA - Connect excel with Outlook

[Phil810]

Hello,

I have a problem with VBA when trying to look at multiple cells and compare them to a criteria. I am new to VBA but I dont understand why this problem is happening.

The matter at hand is, that im trying to link excel with outlook in order to get my work shifts from excel into outlook which is then connected to my phone.

I have the code that connects me with outlook sorted. I am able to look at a single cell and if the requirements are met then it will make a entry in outlook.

The problem is, that when I try to look at multiple cells with the same criteria as before, then it gives me an error message saying: "Runtime error: 13 type mismatch"

I have no idea why this is happening.

Here is the code so far and the excel sheet:

Sub OpdaterOutlook()


Dim obJ0L As Object
Set obJ0L = CreateObject("Outlook.Application")




Set obJ0L = New Outlook.Application


Dim ONS As Outlook.Namespace
Set ONS = obJ0L.GetNamespace("MAPI")


Dim CAL_FOL As Outlook.Folder
Set CAL_FOL = ONS.GetDefaultFolder(olFolderCalendar)


Dim myapt As Outlook.AppointmentItem
Set myapt = CAL_FOL.Items.Add(olAppointmentItem)




With myapt
Dim MyCheck As String
MyCheck = "dag"
MyRange = Range("D10:D17")
MyRange2 = Range("B10:B17")


        If MyCheck = MyRange Then
        .Start = MyRange2 + TimeValue("06:45:00")

        .End = MyRange2 + TimeValue("15:00:00")

        .Subject = "Dagsvagt"
    End If

    If Sheets("MIT TIMEREGNSKAB 2017-2018").Range("D10") = "aften" Then
        .Start = Range("B10") + TimeValue("14:45:00")
        .End = Range("B10") + TimeValue("23:00:00")
        .Subject = "Aftenvagt"
    End If

    If Sheets("MIT TIMEREGNSKAB 2017-2018").Range("D10") = "nat" Then
        .Start = Range("B10") + TimeValue("22:45:00")
        .End = Range("B11") + TimeValue("07:00:00")
        .Subject = "Nattevagt"

    End If

    .Save
End With


End Sub

And about 30 min ago I knew nothing about VBA, so bare with me if the are any bad syntax.


The first column "Dato" is B10:B40 and D10:40 is the "Vagt" column
[TABLE="width: 412"]
<tbody>[TR]
[TD]Dato[/TD]
[TD]Ugedag[/TD]
[TD]Vagt[/TD]
[TD]Ekstra timer[/TD]
[TD]Timer[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Over[/TD]
[/TR]
[TR]
[TD]Arbejde[/TD]
[/TR]
[TR]
[TD]01-maj[/TD]
[TD]tirsdag[/TD]
[TD]dag[/TD]
[TD][/TD]
[TD]8,25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02-maj[/TD]
[TD]onsdag[/TD]
[TD]aften[/TD]
[TD][/TD]
[TD]8,98[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03-maj[/TD]
[TD]torsdag[/TD]
[TD]aften[/TD]
[TD]8[/TD]
[TD]16,98[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]04-maj[/TD]
[TD]fredag[/TD]
[TD]aften[/TD]
[TD][/TD]
[TD]8,98[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]05-maj[/TD]
[TD]lørdag[/TD]
[TD]aften[/TD]
[TD][/TD]
[TD]8,98[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]06-maj[/TD]
[TD]søndag[/TD]
[TD]aften[/TD]
[TD][/TD]
[TD]8,98[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]07-maj[/TD]
[TD]mandag[/TD]
[TD]aften[/TD]
[TD][/TD]
[TD]8,98[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]08-maj[/TD]
[TD]tirsdag[/TD]
[TD]aften[/TD]
[TD][/TD]
[TD]8,98[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]09-maj[/TD]
[TD]onsdag[/TD]
[TD]overtid aft udb[/TD]
[TD][/TD]
[TD][/TD]
[TD]17,96[/TD]
[/TR]
[TR]
[TD]10-maj[/TD]
[TD]torsdag[/TD]
[TD]aften[/TD]
[TD][/TD]
[TD]8,98[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11-maj[/TD]
[TD]fredag[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12-maj[/TD]
[TD]lørdag[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13-maj[/TD]
[TD]søndag[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14-maj[/TD]
[TD]mandag[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15-maj[/TD]
[TD]tirsdag[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16-maj[/TD]
[TD]onsdag[/TD]
[TD][/TD]
[TD]4,5[/TD]
[TD]4,5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17-maj[/TD]
[TD]torsdag[/TD]
[TD]dag[/TD]
[TD]1[/TD]
[TD]9,25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18-maj[/TD]
[TD]fredag[/TD]
[TD]dag[/TD]
[TD][/TD]
[TD]8,25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19-maj[/TD]
[TD]lørdag[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20-maj[/TD]
[TD]søndag[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21-maj[/TD]
[TD]mandag[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22-maj[/TD]
[TD]tirsdag[/TD]
[TD]dag[/TD]
[TD]1,25[/TD]
[TD]9,5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23-maj[/TD]
[TD]onsdag[/TD]
[TD]dag[/TD]
[TD]1,75[/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24-maj[/TD]
[TD]torsdag[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25-maj[/TD]
[TD]fredag[/TD]
[TD]aften[/TD]
[TD][/TD]
[TD]8,98[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26-maj[/TD]
[TD]lørdag[/TD]
[TD]aften[/TD]
[TD]8,25[/TD]
[TD]17,23[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]27-maj[/TD]
[TD]søndag[/TD]
[TD]aften[/TD]
[TD]8,25[/TD]
[TD]17,23[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]28-maj[/TD]
[TD]mandag[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]29-maj[/TD]
[TD]tirsdag[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30-maj[/TD]
[TD]onsdag[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31-maj[/TD]
[TD]torsdag[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I hope someone can help me

 

[Phil811]

Re: VBA - Connect excel with Outlook problem?!

Seems a bit weird that it has to be this much hassle to count in ranges..but it works like a charm - thanks!

How do I then update this cell when ever a new value is added to the range? So that I dont have to update it constantly or forget to update it?

 

[Kamolga]

Re: VBA - Connect excel with Outlook problem?!

If you want the total of Dag and Nacht for example, you can use

[LEFT][COLOR=#333333][FONT=monospace] If (rCell.Value = "Dag" or [COLOR=#333333][FONT=monospace]rCell.Value = "Nacht") then[/FONT][/COLOR][/FONT][/COLOR][/LEFT]

.
and keep on adding or rcell.value="xxx".
If you want the total of all values without knowing what they would be, you need to count whenever no empty

[LEFT][COLOR=#333333][FONT=monospace]if rCell <> "" then[/FONT][/COLOR][/LEFT]

 

[Phil811]

Re: VBA - Connect excel with Outlook problem?!

Okay good to know.

But I still have the problem of how I then update this cell when ever a new value is added to the range? So that I dont have to update it constantly or forget to update it?

And also in reference to the original Excel to outlook matter - the Cases you showed me are case sensitive so if you for instance type in Dag instead of dag into excel it wont enter it in outlook - is there a way to make the cases non case sensitive - i tried just adding an 'or' so Case "Dag" or "dag"...But it didnt work.

Hope you can help

 

[Kamolga]

Re: VBA - Connect excel with Outlook problem?!

The function ucase transform the text in UPPERCASE, so

[LEFT][COLOR=#008000][FONT=monospace]Select Case ucase(dCell.Value)
       Case "DAG"
[/FONT][/COLOR][/LEFT]

will concider Dag, dag, dAG, etc. being true

 

[Kamolga]

Re: VBA - Connect excel with Outlook problem?!

how I then update this cell when ever a new value is added to the range? So that I dont have to update it constantly or forget to update it?

My way is to work with tables: when you add a row to a table, it extends automatically, so the range is dynamic.
Another way is to name (define name in formula tab) and use those names in VBA, so you update a name once and it works wit all your macro. The issue is that if you don't use your file for 2 months, it is very difficult to get back to it...so using tables (with names, you can name columns) is a bit more time consuming in the beginning but worth the effort