VBA - Connect excel with Outlook

P

Phil810

New Member
Joined
Sep 18, 2018
Messages
13
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:

Code: 
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 :)
 
Last edited by a moderator:

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
Re: VBA - Connect excel with Outlook problem?!

Where do you get the error?
 
Upvote 0
Re: VBA - Connect excel with Outlook problem?!

Hi,

From my old souvenir, I had an error message when connecting 2 office applications if in VBA, I was not going to tools/reference and click outlook project library.

Else you might have an issue with a string=range (and it can not be multiple cells) in
Code: 
[LEFT][COLOR=#333333][FONT=monospace]If MyCheck = MyRange Then
[/FONT][/COLOR][/LEFT]

Oh and basically if you want to look for a cell called "dag" in a range (MyRange) of multiple cells, you need to do a loop (having a cell that will be compared to the value, do something if it equals, and go to the next one). If you want to do something if one the cell equals dag and leave the loop, simply type
Goto OutofLoop before "end if" and OutofLoop: after "next dCell". That would be "if condition is met once" equivalent while the code below is "every time a cell meets the condition"

Code: 
[LEFT][COLOR=#333333][FONT=monospace]MyCheck = "dag"
[/FONT][/COLOR][/LEFT]
[COLOR=#0000cd][LEFT]Dim dCell as Range[/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=monospace]
MyRange = Range("D10:D17")
MyRange2 = Range("B10:B17")

      F[/FONT][/COLOR][COLOR=#0000cd]or each dCell in MyRange[/COLOR]
[COLOR=#333333][FONT=monospace]
        If [/FONT][/COLOR][COLOR=#0000cd]dCell.value = MyCheck [/COLOR][COLOR=#333333][FONT=monospace]Then
[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]        .Start = MyRange2 + TimeValue("06:45:00")

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

        .Subject = "Dagsvagt"
    End If[/FONT][/COLOR][/LEFT]
[COLOR=#0000cd]Next dCell[/COLOR]

Hope it helps
 
Last edited:
Upvote 0
Re: VBA - Connect excel with Outlook problem?!

Hi Kamolga,

Thanks Ive just tried your code, and now it says that I need an object as error
 
Upvote 0
Re: VBA - Connect excel with Outlook problem?!

I think you have the same issue with Range2, you need to define a specific cell.

If I understand what you try to do, when the condition is met in colomn D "MyRange" you would like to take the value in MyRange2 (column B).

I would not define Range2 but when the value dag is met with dCell in MyRange, you can use
Code: 
dCell.offset(0,-2)
to refer to the cell 0 line below, 2 columns to the left...so the first time
01-maj ​
and
17-maj ​
​the second,...

Trye to change
Code: 
[LEFT][COLOR=#333333][FONT=monospace]Start = MyRange2 + TimeValue("06:45:00")
[/FONT][/COLOR][/LEFT]
by
Code: 
[LEFT][COLOR=#333333][FONT=monospace]Start = [COLOR=#222222][FONT=Verdana]dCell.offset(0,-2)[/FONT][/COLOR] + TimeValue("06:45:00")[/FONT][/COLOR][/LEFT]
and same with end
 
Last edited:
Upvote 0
Re: VBA - Connect excel with Outlook problem?!

What I would like to do is that if the cell equals "dag" then it should look at the date in column B which is B10:B40 and then use this date to set the appointment in outlook. I dont know if this is the correct way or even possible this way. And it still says object required.
 
Upvote 0
Re: VBA - Connect excel with Outlook problem?!

ok, I did paste your data from 01-maj in B10 and all the data until D40. I then replaced 01-maj by 01/05/2018,set them up as short date format and dragged it down to have all date formed in column B (not general).

I then opened VBA, clicked on tools, then reference and put a little "v" in Microsoft Outlook 16.0 Object Library". This step in mandatory to avoid errors.

Now when I use this macro,

Code: 
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
Dim dCell As Range
    For Each dCell In Range("D10:D40")
        If dCell.Value = "dag" Then
            Set myapt = CAL_FOL.Items.Add(olAppointmentItem)
                With myapt
                        .Start = dCell.Offset(0, -2) + TimeValue("06:45:00")
                        .End = dCell.Offset(0, -2) + TimeValue("15:00:00")
                        .Subject = "Dagsvagt"
                    .Save
                End With
        End If
    Next dCell
End Sub

I got 5 appointments (1 may, 17 May, 18 May, 22 May, and 23 MAy) named "Dagsvagt" in my outlook calendar. No object required message with this one :)
 
Last edited:
Upvote 0
Re: VBA - Connect excel with Outlook problem?!

It works perfectly! Thanks alot Kamolga! The only problem now, is that it creates dublicates.. Ive tried to sort it out in the IF sentence with "dag" and then adding AND NOT appointment exists =true sort of thing but to no avail. Do you have any tips? :)
 
Upvote 0
Re: VBA - Connect excel with Outlook problem?!

Hi,

I updated the file, so you can download it. Basically I added a function "checkappointmentexist" (that returns true if the start date and time is the same and subject is identical). You can post it in the same module
Code: 
[LEFT][COLOR=#222222][FONT=Verdana]Public Function CheckAppointmentExists(argCheckDate As Date, argCheckSubject As String) As Boolean
  Dim oApp As Outlook.Application
  Dim oNameSpace As Outlook.Namespace
  Dim oApptItem As Outlook.AppointmentItem
  Dim oFolder As Outlook.MAPIFolder
  Dim oMeetingoApptItem As Outlook.MeetingItem
  Dim oObject As Object
  On Error Resume Next
  ' check if Outlook is running
  Set oApp = GetObject("Outlook.Application")
  If Err <> 0 Then
    'if not running, start it
    Set oApp = CreateObject("Outlook.Application")
  End If
  Set oNameSpace = oApp.GetNamespace("MAPI")
  Set oFolder = oNameSpace.GetDefaultFolder(olFolderCalendar)
  CheckAppointment = False
  For Each oObject In oFolder.Items
    If oObject.Class = olAppointment Then
      Set oApptItem = oObject
      If oApptItem.Start = argCheckDate Then
        CheckAppointment = True
      End If
    End If
  Next oObject
  Set oApp = Nothing
  Set oNameSpace = Nothing
  Set oApptItem = Nothing
  Set oFolder = Nothing
  Set oObject = Nothing
End Function
[/FONT][/COLOR][/LEFT]
Then the macro is aupdated with "if checkappointmentexist = true do nothing else add the appointment" and that works for me, he adds only the one I deleted after I runned the macro and no more duplicates :)
​

Code: 
[LEFT][COLOR=#222222][FONT=Verdana]Sub OpdaterOutlook()[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Dim obJ0L As Object[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Set obJ0L = CreateObject("Outlook.Application")[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Set obJ0L = New Outlook.Application[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Dim ONS As Outlook.Namespace[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Set ONS = obJ0L.GetNamespace("MAPI")[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Dim CAL_FOL As Outlook.Folder[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Set CAL_FOL = ONS.GetDefaultFolder(olFolderCalendar)[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Dim myapt As Outlook.AppointmentItem[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Dim dCell As Range[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    For Each dCell In Range("D10:D40")[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]        If dCell.Value = "dag" Then[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]            If CheckAppointmentExists(dCell.Offset(0, -2) + TimeValue("06:45:00"), "Dagsvagt") = True Then[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]              Else[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]               Set myapt = CAL_FOL.Items.Add(olAppointmentItem)[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]                   With myapt[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]                           .Start = dCell.Offset(0, -2) + TimeValue("06:45:00")[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]                           .End = dCell.Offset(0, -2) + TimeValue("15:00:00")[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]                           .Subject = "Dagsvagt"[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]                       .Save[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]                   End With[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]            End If[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]        End If[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    Next dCell[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]End Sub
[/FONT][/COLOR][/LEFT]
​
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
Count hours on specific days and time, return value in decimal hours
2
Replies
17
Views
1K
BSALV
BSALV

Forum statistics

Threads
1,223,893
Messages
6,175,239
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

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