Creating a Macro in outlook to schedule weekly meetings

[eit09]

I want to create a macro within outlook that allows me to send emails with meeting invites every Tuesday between 9:00-9:30. The code I wrote in the VBA doesn't work and I think its a problem with the 11th line (.When = "Tuesdays 9:00-9:30") and 12th line (.To = "engineeringeit@gmail.com","86chevys10@gmail.com","drafting2d@gmail.com"). Could someone provide some guidance on the code I am attempting to write below?

Sub Sendofficeweeklymeeting()

Dim myItem As Outlook.MailItem
Dim myolApp As Outlook.Application
Dim myRecipient As Recipient


Sub WeeklyMeeting()
Set myolApp = CreateObject("Outlook.Application")
Set myItem = myolApp.CreateItem(olMailItem)

With myItem
.Importance = olImportanceNormal
.Subject = "Weekly Meeting"
.Location = "Front Conference Room"
.When = "Tuesdays 9:00- 9:30 "
.To = "engineeringeit@gmail.com","86chevys10@gmail.com","drafting2d@gmail.com"
.Body = "All," & vbNewLine & "" & vbNewLine & "Please email me a brief description of what you are working or will be working on for this week including the project number prior to our weekly meeting." & vbNewLine & "" & vbNewLine & "" & vbNewLine & "Name " & vbNewLine & "Position" & vbNewLine & " Company, Inc." & vbNewLine & "Suite ##" & vbNewLine & " City,State" & vbNewLine & "E-Mail: ###@###.com" & vbNewLine & "Phone: ###" & vbNewLine & "Fax: ###"



End Sub

 

[eit09]

@MintJulep,

That would certainly clean things up, but when I change the code (below) I receive a compile error when I try to run it.

Sub Sendofficeweeklymeeting()
Dim myItem As Object


Set myItem = Application.CreateItem(olAppointmentItem)
With myItem
.MeetingStatus = olMeeting
.Subject = "Weekly Meeting"
.Location = "Front Conference Room"
.Start = #2/2/2016 9:30:00 AM#
.Duration = 30
.Recipients.Add ("engineeringeit@gmail")
.Recipients.Add ("drating2d@gmail")
.Recipients.Add ("86chevys10@gmail")
End With


myItem.Display

 

[MintJulep]

End Sub

 

[eit09]

@IRstuff,

I am not sure what you mean at missing MJ's point and which one sticks. All three emails are placed in the meeting invite once I run this macro the way it is written. How would you write the code below to be more correct?

Set myRequiredAttendee = myItem.Recipients.Add("engineeringeit@gmail")
Set myRequiredAttendee = myItem.Recipients.Add("drating2d@gmail")
Set myRequiredAttendee = myItem.Recipients.Add("86chevys10@gmail")

@davidbeach

So how would you of write this to build a list versus what I have written using .add below?

Set myRequiredAttendee = myItem.Recipients.Add("engineeringeit@gmail")
Set myRequiredAttendee = myItem.Recipients.Add("drating2d@gmail")
Set myRequiredAttendee = myItem.Recipients.Add("86chevys10@gmail")

@skipVought,

For the non outlook email addresses.

I think I respond to all and I appreciate all the help and advice. This VBA stuff is both exciting when it works but so frustrating to me.

 

[eit09]

@MintJulep,

Slap my head on the End Sub! THanks that really cleans this macro up!!

 

[davidbeach]

I was responding to MintJulep and IRStuff.

 

[MintJulep]

@davidbeach

Dim myRequiredAttendee As Outlook.Recipient

Set myRequiredAttendee = myItem.Recipients.Add("engineeringeit@gmail")

olAppointmentitem.recipients is a collection. Therefore .Add adds a member to the collection

Outlook.Recipient is one member of a .Recipients collection

Set myRequiredAttendee = myItem.Recipients.Add("engineeringeit@gmail")

In the above line of code the blue part does the heavy lifting. The red part does... well, nothing.

 

[davidbeach]

I read your 2 Feb 16 21:17 as implying that each entry replaced the previous and thus that "As written only 86chevys10 is required." As I understand collections, all entries in the collection would be required.

 

[MintJulep]

Dim myItem As Object
Dim myRequiredAttendee As Outlook.Recipient
Set myItem = Application.CreateItem(olAppointmentItem)
Set myRequiredAttendee = myItem.Recipients.Add("engineeringeit@gmail")


myItem is an object of the type olAppointmentItem. As such it has a .Recipients collection.

myRequired Attendee is an object of the type Recipient. A recipient is one (and only one) member of a .Recipients collection.

myItem.Recipients.Add("member") is fully sufficient to add "member" to the collection.

Set myRequiredAttendee = myItem.Recipients.Add("member") will add "member" to the collection and then set the myRequiredAttendee recipient object to be that member.

In the line above the part left of the = has no real purpose. The object is created and assigned. But that's it. It's there, floating around. Doing nothing.

myItem.Recipients.Add("member1")
myItem.Recipients.Add("member2")
myItem.Recipients.Add("member3")

Adds three new members to the collection. Fully sufficient.

myRequiredAttendee = myItem.Recipients.Add("member1")
myRequiredAttendee = myItem.Recipients.Add("member2")
myRequiredAttendee = myItem.Recipients.Add("member3")

Adds "member1" to the collection then sets myRequiredAttendee to "member1"
Then adds "member2" to the collection and sets myRequiredAttendee to "member2"
Then adds "member3" to the collection and sets myRequiredAttendee to "member3"

After those three lines:

MyItem.Recipients contains member1; member2; member3
MyRequiredAttendee = member3

Now, if we run
myRequiredAttendee.Type = olRequired

member3, and only member3 is changed.

However, because olRequired is the default value of .Type all three members are olRequired because they were made that way when they were added to the collection.