Set (own) outlook appointment/meeting from Excel with VBA

K

Kuan_Ji

New Member
Joined
May 5, 2016
Messages
7
Hi! :)

This is Kuan writing from Barcelona! (As a brief presentation:stickouttounge:)

I'm trying to set up a outlook appointment from Excel/VBA. As I usually do this appointment few times a month, I thought on doing it from an excel file, so I can automate things like subject / location / dates / hours

In order to do so, I needed to learn how to do it in VBA (I've used VBA for other simpler tasks, not for creating appointments), so I'm trying to do it first with a simpler spreadsheet (just one single meeting assigned through a single command button executing a macro).

I'm currently using Excel/Outlook 2013 under Windows 7.

The code is as follows:

Code: 
Sub AddMeeting()


    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim myRequiredAttendee As Outlook.Recipient
    
    Dim strFecha As Range
    Set strFecha = Range("F3")                          ' this is to see in the debugger the value of Range("F3")
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olAppointmentItem)


    strbody = "Hi there" & vbNewLine & vbNewLine & _
              "This is line 1... I'm setting up a Meeting reminder" & vbNewLine & _
              "This is line 2... Do you fancy some donuts or coffee?" 


    On Error Resume Next
    With OutMail
        OutMail.Start = Range("F3")                      ' in "F3", the "string" that the formula returns it's a value for instance #5/7/2016 2:00:00 PM#
        'OutMail.Start = #5/7/2016 2:00:00 PM#           ' Working, but I need to pick the date/time value from excel cell to automatize (don't want to change this part of the code for every single meeting)
        OutMail.Duration = Range("D5")                   ' Working, this uses the value in D5 as meeting duration
        OutMail.Subject = Range("D7")                    ' Working, this uses the value in D7 as meeting location
        OutMail.Location = Range("D8")                   ' Working, this uses the value in D8 as meeting subject
        OutMail.Body = strbody                           ' Working, this uses the value in D5 as meeting subject
        OutMail.BusyStatus = olBusy
        OutMail.ReminderMinutesBeforeStart = Range("D6") ' Working, this uses the value in D6 as reminder timer
        OutMail.ReminderSet = True
        OutMail.Save
        'OutMail.Display
        Set myRequiredAttendee = OutMail.Recipients.Add("B1")
        myRequiredAttendee.Type = olRequired
    End With


    On Error GoTo 0


    Set OutMail = Nothing
    Set OutApp = Nothing


End Sub

The value of F3 is something like: #5/7/2016 2:00:00 PM# and this come from a formula (not a written value, but a "concatenate" of different cells changing date & time format)

What happens is: the meeting it's created "Today", in an incorrect timeslot, but with the correct subject & meeting room correct, as with appropiated reminder...

The question is ¿why today/that time slot?... all the other stuff seems to be correct... at least from my point of view.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
Hi and welcome to the MrExcel Message Board.

I was getting some "mixed messages" from your code - so I have re-arranged it somewhat.

I assumed that if you were specifying "Outlook.Recipient" then you must have set up a reference to Outlook. In which case, why not Dim everything else the same way? It is called "Early Binding". It enables Intellisense in the VB Editor and should run slightly faster.

Also, you set up a "With/End With block but then did not use it. So I left the With block and changed the code to make use of it.

On Error Resume Next will hide the problems you are trying to find so I commented that out as well. (I also switched on the "Tools-->Options-->General: Break on all errors" option to make sure that I missed nothing.)

The problem seems to be that you had defined OutMail as an Object then assigned a Range to it. If VBA does not know the type of Object it may not default to Range.Value. It thinks you need all the Range details (e.g. Row, Column, Count, Parent, Value, Value2, Text etc etc). I changed it at first by adding .Value to each Range. However, after Dimming the variables more accurately, VBA knew that it needed to pass a string so it makes the right assumption now.

Try this:
Code: 
' Note: Requires Tools-->References-->Microsoft Outlook Object Library

Sub AddMeeting()


    Dim OutApp              As Outlook.Application
    Dim OutMail             As Outlook.AppointmentItem
    Dim myRequiredAttendee  As Outlook.Recipient
    Dim strbody             As String
    
    Dim strFecha As String
    strFecha = Range("F3")                          ' this is to see in the debugger the value of Range("F3")
    
    Set OutApp = New Outlook.Application
    Set OutMail = OutApp.CreateItem(olAppointmentItem)


    strbody = "Hi there" & vbNewLine & vbNewLine & _
              "This is line 1... I'm setting up a Meeting reminder" & vbNewLine & _
              "This is line 2... Do you fancy some donuts or coffee?"


    'On Error Resume Next
    With OutMail
        .Start = Range("F3")                        ' in "F3", the "string" that the formula returns it's a value for instance #5/7/2016 2:00:00 PM#
        '.Start = #5/7/2016 2:00:00 PM#             ' Working, but I need to pick the date/time value from excel cell to automatize (don't want to change this part of the code for every single meeting)
        .Duration = Range("D5")                     ' Working, this uses the value in D5 as meeting duration
        .Subject = Range("D7")                      ' Working, this uses the value in D7 as meeting location
        .Location = Range("D8")                     ' Working, this uses the value in D8 as meeting subject
        .Body = strbody                             ' Working, this uses the value in D5 as meeting subject
        .BusyStatus = olBusy
        .ReminderMinutesBeforeStart = Range("D6")   ' Working, this uses the value in D6 as reminder timer
        .ReminderSet = True
        .Save
        '.Display
        Set myRequiredAttendee = .Recipients.Add("B1")
        myRequiredAttendee.Type = olRequired
    End With


    'On Error GoTo 0


    Set OutMail = Nothing
    Set OutApp = Nothing


End Sub
 
Last edited:
Upvote 0
RickXL said:
Hi and welcome to the MrExcel Message Board.
Click to expand...

Thanks :).

I'll try to quote & comment on each part, so you can have an idea of my thoughts on this. Hopefully I can learn and understand the solution! :)

RickXL said:
I was getting some "mixed messages" from your code - so I have re-arranged it somewhat.

I assumed that if you were specifying "Outlook.Recipient" then you must have set up a reference to Outlook. In which case, why not Dim everything else the same way? It is called "Early Binding". It enables Intellisense in the VB Editor and should run slightly faster.
Click to expand...

OK to the re-arrangement & Early Binding (I didn't know that it was possible to dim a variable directly as outlook item).

RickXL said:
Also, you set up a "With/End With block but then did not use it. So I left the With block and changed the code to make use of it.

On Error Resume Next will hide the problems you are trying to find so I commented that out as well. (I also switched on the "Tools-->Options-->General: Break on all errors" option to make sure that I missed nothing.)
Click to expand...

I've never used it before (With/End With), as I've found few examples googleing it... I've did a mix to try to make it work... said this, you can imagine you can expect a lot of mistakes in my coding, I don't consider myself a programmer (I'm more a PowerPoint and Excel person lol)... just someone trying to learn a bit each day... and VBA looks interesting :)

RickXL said:
The problem seems to be that you had defined OutMail as an Object then assigned a Range to it. If VBA does not know the type of Object it may not default to Range.Value. It thinks you need all the Range details (e.g. Row, Column, Count, Parent, Value, Value2, Text etc etc). I changed it at first by adding .Value to each Range. However, after Dimming the variables more accurately, VBA knew that it needed to pass a string so it makes the right assumption now.
Click to expand...

I'm not able to see any .Value in each Range in that version of code... where is that? or where should it be? in .Start line? (I've tried but seems not to be working)

By default, trying your proposed code instead of mine, with the "Break on all errors" option, it drops me an error [Run-time error '13': Type mismatch] in the line [.Start = Range("F3")] at the beginning of With/End block

The code (I've removed some non-needed lines, in order to keep it simpler)

Code: 
Sub AddMeeting()

    Dim OutApp              As Outlook.Application
    Dim OutMail             As Outlook.AppointmentItem
    Dim myRequiredAttendee  As Outlook.Recipient
    Dim strbody             As String
    
    Set OutApp = New Outlook.Application            ' Why "New" there?
    Set OutMail = OutApp.CreateItem(olAppointmentItem)


    strbody = "Hi there"


    'On Error Resume Next                           ' On Error Resume Next will hide the problems you are trying to find
    With OutMail
        .Start = Range("F3")                        ' in "F3" Cell, the "string" that the formula returns it's a value for instance #5/7/2016 2:00:00 PM# // Tried to add .Value, .Value2, and .Text ... nothing works
        .Duration = Range("D5")
        .Subject = Range("D7")
        .Location = Range("D8")
        .Body = strbody
        .BusyStatus = olBusy
        .ReminderMinutesBeforeStart = Range("D6")   ' Working, this uses the value in D6 as reminder timer
        .ReminderSet = True
        .Save
        '.Display
        Set myRequiredAttendee = .Recipients.Add("B1")
        myRequiredAttendee.Type = olRequired
    End With


    'On Error GoTo 0                                ' What does this do?


    Set OutMail = Nothing
    Set OutApp = Nothing


End Sub
 
Upvote 0
Solved!!! (stupid me!)

The F3 Cell value should be in format [d/m/yyyy hh:mm:ss AM/PM] if you pick it from the cell... in case you write-up manually on the code that thing only works with [#m/d/yyyy hh:mm:ss AM/PM#]

I can imagine this is due the # characters are something like "reserved characters" for VBA, so I don't need to put them in excel.

I'm not sure why I needed to change the m/d/yyyy format for d/m/yyyy format... but it works.

Hope that help for someone else!

Thanks a lot for the help!

2ea35a766c27256bb3a099f9d93da19d.gif
 
Upvote 0
Glad you sorted it out.

In my opinion, computers work better when they are not trying to be "helpful". Excel tries to be very helpful with dates. Consequently, they are one of the hardest things to use. :) A value can look like a date in so many ways but only a few of them are actually "real" dates. Excel likes dates to be stored as number of days after 1 Jan 1900. I deduced that you are using character strings so my next guess was to suggest using the DateValue() function in the VBA like this:
Code: 
.Start = DateValue(Range("F3"))
That should take something that looks like a date and convert it into an Excel date.
The # characters are a way of defining a date constant in VBA but you would not use it in a worksheet.

When the code Dim'd OutMail as Object I tried this and it worked:
Code: 
Dim OutMail             As Object
...

    With OutMail
        .Start = Range("F3").Value
However, Dimming it as Outlook.Application seemed to remove the need for the .Value part.

The With/End With construct is not always obvious to everyone. It is supposed to speed things up slightly. I think of it like this. If you use:
Code: 
        OutMail.Start = Range("F3")         
        OutMail.Duration = Range("D5") 
        OutMail.Subject = Range("D7")                    
        OutMail.Location = Range("D8")
then Excel has to get the OutMail book from the shelf, go to the "Start" chapter, set it to a value then close the book and put it back on the shelf.
For the next line it re-fetches the book, finds the "Duration" chapter etc etc.

However, if you use With/End With then Excel can get the book once and open it. It can then make changes to the various chapters and when all are complete it can put the book back.

To make it work you need to omit OutMail in this case from the Property names and start them with a dot instead. That is Excel's way of saying "use the book that we currently have open."
Code: 
    With OutMail                                    ' Open the OutMail "book"
        .Start = Range("F3")                        ' Change the "Start" chapter in the open book 
        .Duration = Range("D5")                     ' Change the "Duration" chapter in the open book 
        .Subject = Range("D7")                      ' etc
        .Location = Range("D8")                     ' 
        .Body = strBody                             ' 
        .BusyStatus = olBusy
        .ReminderMinutesBeforeStart = Range("D6")   
        .ReminderSet = True
        .Save
        '.Display
        Set myRequiredAttendee = .Recipients.Add("B1")
        myRequiredAttendee.Type = olRequired
    End With                                        ' Close the "book" and return it to the shelf
It does have a secondary benefit as well. it encourages you to group together all the changes to a particular object. This can make the code easier to read and understand later on because it imposes more structure.

Regards,
 
Last edited:
Upvote 0
New Challenge for this code.

I was wondering if I could send the same appointment to my team mates, (I wish I could make their lives easyer!)

So I've added a ".Send" at the end of the OutMail object.

Then I've changed the e-mail address in B1 from mine to other one (mycolleague@mycompany.com)

And it worked!

Here the code:

Code: 
Sub AddMeeting()


    Dim OutApp              As Outlook.Application
    Dim OutMail             As Outlook.AppointmentItem
    Dim myRequiredAttendee  As Outlook.Recipient
    Dim strbody             As String
    
    Set OutApp = New Outlook.Application            ' Why "New" there?
    Set OutMail = OutApp.CreateItem(olAppointmentItem)


    strbody = "Hi there"


    'On Error Resume Next                           ' On Error Resume Next will hide the problems you are trying to find
    With OutMail
        .MeetingStatus = olMeeting
        .Start = Range("E3")                        ' Format d/m/yyyy hh:mm:ss AM/PM
        '.Start = #5/7/2016 2:00:00 PM#
        .Duration = Range("D5")
        .Subject = Range("D7")
        .Location = Range("D8")
        .Body = strbody
        .BusyStatus = olBusy
        .ReminderMinutesBeforeStart = Range("D6")
        .ReminderSet = True
        .Save
        Set myRequiredAttendee = .Recipients.Add(Range("B1"))   
        myRequiredAttendee.Type = olRequired                   
        .Send
    End With


    Set OutMail = Nothing                           ' Are those two lines strictly needed?
    Set OutApp = Nothing                            ' What does they do?


End Sub

Then I tried to set multiple recipients (in same B1 Cell), adding the string [mycolleague@mycompany.com;mycolleague2@mycompany.com]

That doesn't work...

Do I need to set multiple lines (set myRequiredAttendee) for each required attendee?

Code: 
Set myRequiredAttendee = .Recipients.Add(Range("B1"))   
myRequiredAttendee.Type = olRequired

Do you know if there a (easy if possible) way to read all attendees from a single cell?

Thanks!
 
Upvote 0
Sorry, this is not tested but it looks OK. :)

Code: 
Sub AddMeeting()


    Dim OutApp              As Outlook.Application
    Dim OutMail             As Outlook.AppointmentItem
    Dim myRequiredAttendee  As Outlook.Recipient
    Dim strbody             As String
    Dim Address             As Variant
    
    Set OutApp = New Outlook.Application            ' Why "New" there?
    Set OutMail = OutApp.CreateItem(olAppointmentItem)


    strbody = "Hi there"


    'On Error Resume Next                           ' On Error Resume Next will hide the problems you are trying to find
    With OutMail
        .MeetingStatus = olMeeting
        .Start = Range("E3")                        ' Format d/m/yyyy hh:mm:ss AM/PM
        '.Start = #5/7/2016 2:00:00 PM#
        .Duration = Range("D5")
        .Subject = Range("D7")
        .Location = Range("D8")
        .Body = strbody
        .BusyStatus = olBusy
        .ReminderMinutesBeforeStart = Range("D6")
        .ReminderSet = True
        .Save
        For Each Address In Split(Range("B1"), ";")
            Set myRequiredAttendee = .Recipients.Add(Address)
            myRequiredAttendee.Type = olRequired
        Next
        .Send
    End With


    Set OutMail = Nothing                           ' Are those two lines strictly needed?
    Set OutApp = Nothing                            ' What does they do?


End Sub

I set up an extra variant called Address. (It needs to be either a variant or an object to be used in a For/Each loop.)
Code: 
Split(Range("B1"), ";")
turns the string in B1 into an Array. The string is split at each semi-colon ( ; ).
The string between the semi-colons is called Address and that is then used to supply the data to Recipients.Add.

By the way, the lines
Code: 
    Set OutMail = Nothing                        
    Set OutApp = Nothing
are not strictly necessary. A purist might argue that they are "good practice", though.

Basically, when you create an Object it takes up memory space. So this statement:
Code: 
Set OutApp = New Outlook.Application
requests Windows to allocate some space in memory. Think of it as creating a template or a blank form. The program then fills in the blank form as it needs to. When the code ends Windows will usually take back all that allocated memory. If something untoward happens Windows might skip the clear up but I believe that hardly ever happens these days. For personal PC use when you restart the machine every day anyway, even if something disastrous happened you will not run out of memory. If you are programming a server that might be left running for weeks at a time many would still go for the belt and braces approach - just in case.

Regards,
 
Last edited:
Upvote 0
Looks & Works as expected!

Very helpful! Thank you so much!

2ea35a766c27256bb3a099f9d93da19d.gif


Now looking forward to integrate that code in the real version of the Excel (much more complex than the "testing environment" one)

Thanks again!
 
Upvote 0
Great!

Integrated and working!

Now the code is:

Code: 
Sub AddMeetingJ5()

    Dim OutApp              As Outlook.Application
    Dim OutMail             As Outlook.AppointmentItem
    Dim myRequiredAttendee  As Outlook.Recipient
    Dim strbody             As String
    Dim Address             As Variant
    
    Set OutApp = New Outlook.Application
    Set OutMail = OutApp.CreateItem(olAppointmentItem)


    strbody = Range("Q21") & " " & Range("R21") & vbNewLine & vbNewLine & _
              "This is an automated e-mail. But if there is any concerns you are worrying about you can just reply and we will answer you as soon as posible" & vbNewLine & vbNewLine & _
              Range("U21") & Range("K21") & vbNewLine & vbNewLine & _
              "Thank you"


    With OutMail
        .MeetingStatus = olMeeting
        .Start = Range("H21")
        .Duration = Range("G21")
        .Subject = Range("I21")
        .Location = Range("J21")
        .Body = strbody
        .BusyStatus = olBusy
        .ReminderMinutesBeforeStart = Range("Q43")
        .ReminderSet = True
        .Save
        For Each Address In Split(Range("B13"), ";")
            Set myRequiredAttendee = .Recipients.Add(Address)
            myRequiredAttendee.Type = olRequired
        Next
        .Send
    End With


    Set OutMail = Nothing
    Set OutApp = Nothing
    
End Sub

The problem now is, sometimes, in cell K21 there is some content needed for the body, some times there is an "error" (#N/A, as is the result for a VLOOKUP formula)

So I would like to "report" the error with a msgbox (Somethink like "hey! you cannot do that as there's no meeting for selected recipients!") and stop the script (so the meeting is not sent)

Then, if in K21 there is some content (different from that "error") I would like the program to continue and send the meeting invitation.


I thought on putting something like

Code: 
If Range("K21") = "#N/A" Then        MsgBox "There's no appointment for that date for selected recipient type", vbOKOnly, "Something went wrong!"
        Exit Sub ' Something to stop script (not sure if "break" exists in VBA and I've read this can work over there!)
End If

Between "strbody = ..." and "With OutMail" but seems not to be working... it gives me "Run-time error '13': Type mismatch"
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
VBA Excel - Recurring Calendar Meeting - Include Appointment Edits/Exceptions
Replies
3
Views
306
John_w
J
O
  • Solved
MS Excel VBA Meeting Request Code to Set Meeting to Property
Replies
4
Views
264
OilEconomist
O
S
  • Question Question
VBA sending an email of two conditions are met
Replies
5
Views
139
mumps
mumps
H
  • Question Question
Macro to email Report
Replies
2
Views
551
howard
H
S
  • Solved
Tweak to VBA email code
Replies
3
Views
757
rollis13
rollis13

Forum statistics

Threads
1,223,911
Messages
6,175,327
Members
452,635
Latest member
laura12345

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