Creating a Macro in outlook to schedule weekly meetings
Creating a Macro in outlook to schedule weekly meetings
(OP)
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
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





RE: Creating a Macro in outlook to schedule weekly meetings
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.
RE: Creating a Macro in outlook to schedule weekly meetings
I am trying to set up a new meeting not appointment if that makes a difference??
1. Instede of using Set myItem = myolApp.CreateItem(olMailItem) what language would set up a meeting?
2. .When = "Tuesdays 9:00- 9:30 " 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" I believe this is what you were saying correct?
RE: Creating a Macro in outlook to schedule weekly 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.
RE: Creating a Macro in outlook to schedule weekly meetings
TTFN
I can do absolutely anything. I'm an expert!
homework forum: //www.engineering.com/AskForum/aff/32.aspx
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers
RE: Creating a Macro in outlook to schedule weekly meetings
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"
.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
RE: Creating a Macro in outlook to schedule weekly meetings
RE: Creating a Macro in outlook to schedule weekly meetings
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.
RE: Creating a Macro in outlook to schedule weekly meetings
...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,
Just traded in my OLD subtlety...
for a NUance!
RE: Creating a Macro in outlook to schedule weekly meetings
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: //www.engineering.com/AskForum/aff/32.aspx
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers
RE: Creating a Macro in outlook to schedule weekly meetings
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.
RE: Creating a Macro in outlook to schedule weekly meetings
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.
RE: Creating a Macro in outlook to schedule weekly meetings
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,
Just traded in my OLD subtlety...
for a NUance!
RE: Creating a Macro in outlook to schedule weekly meetings
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
RE: Creating a Macro in outlook to schedule weekly meetings
Was this your intent?
RE: Creating a Macro in outlook to schedule weekly meetings
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
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
RE: Creating a Macro in outlook to schedule weekly meetings
TTFN
I can do absolutely anything. I'm an expert!
homework forum: //www.engineering.com/AskForum/aff/32.aspx
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers
RE: Creating a Macro in outlook to schedule weekly meetings
RE: Creating a Macro in outlook to schedule weekly meetings
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Creating a Macro in outlook to schedule weekly meetings
CODE -->
Although since olRequired is the default value for type it doesn't change the outcome.
RE: Creating a Macro in outlook to schedule weekly meetings
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
RE: Creating a Macro in outlook to schedule weekly meetings
RE: Creating a Macro in outlook to schedule weekly meetings
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.
RE: Creating a Macro in outlook to schedule weekly meetings
Slap my head on the End Sub! THanks that really cleans this macro up!!
RE: Creating a Macro in outlook to schedule weekly meetings
RE: Creating a Macro in outlook to schedule weekly meetings
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.
RE: Creating a Macro in outlook to schedule weekly meetings
RE: Creating a Macro in outlook to schedule weekly meetings
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.
CODE -->
myItem.Recipients.Add("member1") myItem.Recipients.Add("member2") myItem.Recipients.Add("member3")Adds three new members to the collection. Fully sufficient.
CODE -->
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.