INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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


RE: Creating a Macro in outlook to schedule weekly meetings

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.

RE: Creating a Macro in outlook to schedule weekly meetings

(OP)
MintJulep,

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

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.

RE: Creating a Macro in outlook to schedule weekly meetings

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: //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

(OP)
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"
.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

But why not just send one recurring appointment?

RE: Creating a Macro in outlook to schedule weekly meetings

(OP)
@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.

RE: Creating a Macro in outlook to schedule weekly meetings

"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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Creating a Macro in outlook to schedule weekly meetings

" 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: //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

(OP)
@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.

RE: Creating a Macro in outlook to schedule weekly meetings

(OP)
@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.

RE: Creating a Macro in outlook to schedule weekly meetings

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Creating a Macro in outlook to schedule weekly meetings

(OP)
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

RE: Creating a Macro in outlook to schedule weekly meetings

As written only 86chevys10 is required.

Was this your intent?

RE: Creating a Macro in outlook to schedule weekly meetings

(OP)
@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
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

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

RE: Creating a Macro in outlook to schedule weekly meetings

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Creating a Macro in outlook to schedule weekly meetings

CODE -->

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

Set myItem = Application.CreateItem(olAppointmentItem) 
With myItem
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").Type = olRequired
Set myRequiredAttendee = myItem.Recipients.Add("drating2d@gmail").Type = olRequired
Set myRequiredAttendee = myItem.Recipients.Add("86chevys10@gmail").Type = olRequired
End With
myRequiredAttendee.Type = olRequired

myItem.Display 

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

(OP)
@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

RE: Creating a Macro in outlook to schedule weekly meetings

End Sub

RE: Creating a Macro in outlook to schedule weekly meetings

(OP)
@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.

RE: Creating a Macro in outlook to schedule weekly meetings

(OP)
@MintJulep,

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

RE: Creating a Macro in outlook to schedule weekly meetings

I was responding to MintJulep and IRStuff.

RE: Creating a Macro in outlook to schedule weekly meetings

@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.

RE: Creating a Macro in outlook to schedule weekly meetings

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.

RE: Creating a Macro in outlook to schedule weekly meetings

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.

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close