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

 

[MintJulep]

Did you know that you can create recurring appointments in Outlook? Much easier than trying to automate doing this every week.

Why doesn't you code work?

Set myItem = myolApp.CreateItem(olMailItem)

You want to create an appointment, but you created an email.

.When = "Tuesdays 9:00- 9:30 "

Near as I can see, there is no such object.

.To = "engineeringeit@gmail.com","86chevys10@gmail.com","drafting2d@gmail.com"

Doesn't do what you think it does, and even if it did your syntax would be incorrect.

From helo Returns or sets a semicolon-delimited String list of display names for the To recipients for the Outlook item. Read/write.
Syntax

expression.To

expression A variable that represents a MailItem object.

Remarks


This property contains the display names only. The To property corresponds to the MAPI property PR_DISPLAY_TO. The Recipients collection should be used to modify this property.

 

[eit09]

MintJulep,

I am trying to set up a new meeting not appointment if that makes a difference??

1. Instede of using [highlight #FCE94F]Set myItem = myolApp.CreateItem(olMailItem)[/highlight] what language would set up a meeting?
2. [highlight #FCE94F].When = "Tuesdays 9:00- 9:30 "[/highlight] I know this is incorrect but not sure how to find correct language to set up the meeting time to every Tuesday 9-930
3. .To = "engineeringeit@gmail.com";"86chevys10@gmail.com";"drafting2d@gmail.com" [highlight #FCE94F]I believe this is what you were saying correct?[/highlight]

 

[MintJulep]

I am trying to set up a new meeting not appointment if that makes a difference?? Yes, it does. You need to read the help about the relationship between Appointments and Meetings.

but not sure how to find correct language to set up the meeting time to every Tuesday 9-930. You need to read the help about appointments. Appointments start. Appointments end. Recurring appointments have a recurrence pattern.

I believe this is what you were saying correct? No.

 

[IRstuff]

You can do this directly in Outlook as a recurring meeting. Add your 3 invitees and make user the reminder is set. Then every week, the reminder with mention the meeting at whatever offset time was set, be it 15 minutes, an hour, whatever.

TTFN
I can do absolutely anything. I'm an expert!
homework forum: //

http://www.engineering.com/AskForum/aff/32.aspx

faq731-376 forum1529

 

[eit09]

MintJulep,

Thank you for your ideas/comments! I do realize the difference between an appointment and meeting I just wasn't sure if you meant the code language made much of a difference between meeting and appointment. What I am truly looking for is the correct code language to create a macro to set up a email/meeting invite to the same recipients at the same time each week. Once I run this email/meeting macro, 9 times out of 10 I can just hit send after the macro has run, but the other 10% of the time I need the option to adjust something within this macro generated email/meeting before hitting send. Shown below is what the code will closely look like.I wrote this macro by using examples from various sources (excel macro recorder, online resources...).

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"
[highlight #FCE94F].When = "Tuesdays 9:00- 9:30 "[/highlight]
.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

 

[davidbeach]

But why not just send one recurring appointment?

 

[eit09]

@davidbeach,

That is what I currently have setup, but it isn't working exactly how I would want. If I can fix this VBA language above I will get exact results/capabilities I am looking for.

 

[SkipVought]

"but it isn't working exactly how I would want."

...does not help us know what it is that you want.

As has been stated, the recurring feature has all this, except perhaps when this week's meeting has some exception(s). Well in THAT case, you merely EDIT the notification for THAT week as needed and SEND. Only THAT week is modified. All other weeks remain as previously sent. Or you have the option to actually CHANGE all subsequent meeting reminders.

I really can't understand what you might want that would be materially different than the built-in features.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[IRstuff]

" If I can fix this VBA language above I will get exact results/capabilities I am looking for."

That assumes that the "fix" can actually exist and that you aren't asking VBA to do something it can't do. But, you've not exactly been clear about what you want, and why the built-in features that 99% of the world uses doesn't work for you.

TTFN
I can do absolutely anything. I'm an expert!
homework forum: //

http://www.engineering.com/AskForum/aff/32.aspx

faq731-376 forum1529

 

[eit09]

@SkipVought,

What I would like to get out of this thread is help writing this specific macro which creates a scheduled meeting email invite. Once I run the macro the only thing I would have to do is send the meeting request. Every thing you & others have mentioned I am utilizing now with the built in features, but that being said I am still looking for help with this specific macro I am trying to write.

 

[eit09]

@IRstuff,
I am assuming that this is not something that VBA can do and if that is the case than I will continue with the built in features. I couldn't figure it out so thought I would throw it out there and ask before I just gave up on it! It would be cool if outlook could have a macro recorder than just step in and view the code. Obviously it cannot have a macro recorder I was just saying.

 

[SkipVought]

You posted some code. That's good!

Now from there,
1) what is it doing that you do not want the code to do and
2) what is it not doing that you want the code to do?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[eit09]

Below is the code I found to work

Sub Sendofficeweeklymeeting()
Dim myItem As Object
Dim myRequiredAttendee As Outlook.Recipient

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

myItem.Display

End Sub

 

[MintJulep]

As written only 86chevys10 is required.

Was this your intent?

 

[eit09]

@MintJulep,

If you look at the lines before 86chevys10 I have 2 other email recipients.

Sub Sendofficeweeklymeeting()
Dim myItem As Object
Dim myRequiredAttendee As Outlook.Recipient

Set myItem = Application.CreateItem(olAppointmentItem)
myItem.MeetingStatus = olMeeting
myItem.Subject = "Weekly Meeting"
myItem.Location = "Front Conference Room"
myItem.Start = #2/02/2016 9:30:00 AM#
myItem.Duration = 30
[highlight #FCE94F]Set myRequiredAttendee = myItem.Recipients.Add("engineeringeit@gmail")
Set myRequiredAttendee = myItem.Recipients.Add("drating2d@gmail")
Set myRequiredAttendee = myItem.Recipients.Add("86chevys10@gmail")[/highlight]
myRequiredAttendee.Type = olRequired

myItem.Display

End Sub

 

[IRstuff]

And, you missed MJ's point, which is that you assigned 3 different things to the same variable; guess which one sticks?

TTFN
I can do absolutely anything. I'm an expert!
homework forum: //

http://www.engineering.com/AskForum/aff/32.aspx

faq731-376 forum1529

 

[davidbeach]

".Add" should be building a list, not replacing the previous item.

 

[SkipVought]

So you never explained how this procedure does something different than a recurring meeting invitation. I'd like to know in the event that I or some other member might use it in the future.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[MintJulep]

Sub Sendofficeweeklymeeting() 
Dim myItem As Object 
[s]Dim myRequiredAttendee As Outlook.Recipient[/s] Unnecessary

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

myItem.Display

Although since olRequired is the default value for type it doesn't change the outcome.