VBA to add a button to an Excel ribbon when you open Excel

[JeenLeen]

I read somewhere about someone writing a VBA script that created a button in an Excel ribbon, which when clicked would e-mail someone asking them to please call. They used this to be able to answer the phone and pretend to be busy when an annoying co-worker would stop by.

I created the 'e-mail to call me' code, but I'm having trouble modifying the ribbon and was hoping y'all could point me in the right direction.

My current idea is something like this (the Sub AvoidAnnoyance works as-is):


Option Explicit
'note: if code does not work, you may need to go to Tools -- References and select Microsoft Outlook

Private Sub Project_Open()
Call AddButton
End Sub

Sub AddButton()
'code to add a button to the Home ribbon which calls the AvoidAnnoyance() sub
End Sub

'This code sends a meeting request to the selected individual.
Sub AvoidAnnoyance()

Dim outobj As Outlook.Application, outappt As Outlook.AppointmentItem
Dim mtgTime As Date

mtgTime = Now()

Set outobj = CreateObject("outlook.application")
Set outappt = outobj.CreateItem(olAppointmentItem)
With outappt
.MeetingStatus = olMeeting
.Subject = " Call 555-5555"
.Location = "MyName Call"
.Body = "Give me a call and help me out here: 555-5555"
.Start = mtgTime
.Duration = 1 'one minute so not impact calendar negatively
.ReminderSet = True
.ReminderMInutesBeforeStart = 1
.Recipients.Add ("[email protected]")
'.Recipients.Add ("[email protected]") ' you can add more of these if you want
.Send
.save
End With

End Sub


Also, I've noticed that in Excel's VBA, I need to go to References and select the MS Outlook objects in order to use code that messes with Outlook. Is there any way, such as in the Open_Project code, to check if these settings are as I need them and set them programmatically instead of manually? Would that let the code run? (I can see yes, because by the time it gets to the Outlook objects, the settings are such that it knows what they are. But I could also see no, if the code checks everything before it changes the settings.)

[Jasdoif]

I created the 'e-mail to call me' code, but I'm having trouble modifying the ribbon and was hoping y'all could point me in the right direction.I know very little about customizing the ribbon, but this page (http://www.rondebruin.nl/win/s2/win001.htm) meshes with what I know.


Also, I've noticed that in Excel's VBA, I need to go to References and select the MS Outlook objects in order to use code that messes with Outlook. Is there any way, such as in the Open_Project code, to check if these settings are as I need them and set them programmatically instead of manually? Would that let the code run? (I can see yes, because by the time it gets to the Outlook objects, the settings are such that it knows what they are. But I could also see no, if the code checks everything before it changes the settings.)From what I've seen, as long as there's actual code in the VBA project and you're saving the spreadsheet as macro-enabled, it will save the selected references along with the rest of the file and restore them when it's loaded.

[JeenLeen]

I know very little about customizing the ribbon, but this page (http://www.rondebruin.nl/win/s2/win001.htm) meshes with what I know.

So, to your knowledge is it not possible to do this via VBA?
Thank you, still. This does look pretty straight-forward to use, and I'm glad to have a means to do what I want even if the method is different than the one I desire.



From what I've seen, as long as there's actual code in the VBA project and you're saving the spreadsheet as macro-enabled, it will save the selected references along with the rest of the file and restore them when it's loaded.

That is the case, at least from my experience, but I'd like something like that as a tool/utility program I can copy & paste into code, so that whenever I work with Excel-to-Outlook in a new project I can just paste the code in instead of clicking the settings.

[Jasdoif]

So, to your knowledge is it not possible to do this via VBA?
Thank you, still. This does look pretty straight-forward to use, and I'm glad to have a means to do what I want even if the method is different than the one I desire.That's my understanding of it, yeah. While you can have controls on the ribbon to call VBA methods, as that page shows, to add buttons or tabs to the ribbon you need to get an XML definition for that into Excel somehow.


That is the case, at least from my experience, but I'd like something like that as a tool/utility program I can copy & paste into code, so that whenever I work with Excel-to-Outlook in a new project I can just paste the code in instead of clicking the settings.Well, VB can be a little forgiving about not having references in this regard. If you don't set a type on the variable, it can still work with the CreateObject line (and the resulting object).


This code works alright even without the reference set:

Sub CheapTest()
Dim testObject
Set testObject = CreateObject("outlook.application")
Set testObject = Nothing
End Sub

While this one fails with a compile error unless the reference is set:

Sub CheapTest()
Dim testObject As Outlook.Application
Set testObject = CreateObject("outlook.application")
Set testObject = Nothing
End Sub


While this could/should do want you're wanting, I'd still encourage setting the reference (and using types on variables) if you have the chance; you'll have a much easier time with maintaining the code if you actually have object definitions available when you're writing the code.