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

[Kuan_Ji]

Hi!

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

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:

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.

 

[RickXL]

Try this:

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)

    If IsError(Range("K21")) 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
    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

 

[Kuan_Ji]

Again working, again thanks!

IsError()... I didn't know that was existing!
Is there any site/pdf I can have a look with a "list of all available functions"? Any recommendation?

Thanks again!

 

[RickXL]

Microsoft documents most things. The only problem is finding it! https://msdn.microsoft.com/en-us/library/office/jj692818.aspx