Combo Box

[Diane Poremsky]

As I mentioned earlier today and in the screencast , you use "With Combox7" and "ComboBox7" - they both need to be spelled the same as the name of the combobox control.

 

[LMS]

I did it, then in my contact form, I put the new macro of the Module7 as a bullet point on the Quick Access Tool Bar, and when I click on it, the userform shows up, and there is the list of the macros.

But the next step is to run one a macro from the list........as I can't figure out how to make it run when I identify the macro in the list of the combobox based on userform.....

So if there is a way to do that, everything is done.

Thanks so much for your great advice.

 

[LMS]

And also, given the macros are related to the emails and he contact itself, it was not clear to me what lines to add to the codes so the macros create the email to the contact.

Thanks very very much!! I am excited to finish this, as I have a number of macros in different email areas, so I can then create a new userform for each area once I know what lines to add as I mentioned here.

 

[Diane Poremsky]

replace

Public Sub NewMessageUsingTemplate()

With these lines

Public oContact As Outlook.ContactItem
Public Sub NewMessageUsingTemplate()
If TypeName(ActiveExplorer.Selection.Item(1)) = "ContactItem" Then
Set oContact = ActiveExplorer.Selection.Item(1) ' this works with the selected item, not opened

replace

End Select

with these lines:

End Select
End if
Set oContact = Nothing

If you are referring to contacts in the macros as something besides oContact, then you need to change oContact to match. For example if you use olContact.Firstname in the other macros- this macro needs to use olContact too.

 

[LMS]

so if the code of the macro for the emails is oContact.EmailAddress, then that's what I put in this code...correct?

An the debug of what you gave shows the following where what i show as red this post, so you see it, is showing up as yellow:

If TypeName(ActiveExplorer.Selection.Item(1)) = "ContactItem" Then

Set oContact = ActiveExplorer.Selection.Item(1) ' this works with the selected item, not opened

 

[Diane Poremsky]

Yes, if you use oContact in the other macros, you use it in this one.

The yellow means it is the problem line when you run the macro. What does the error message say?

Two things i know will cause problems - not selecting a contact and not adding the if statement.

 

[LMS]

The error says "expected: end of statement"

- - - Updated - - -

I emailed back to you from my notice if you can get the email back

 

[LMS]

the macro re emais is:

Public Sub From_Lou_Stoler()

Set oContact = GetCurrentItem()

Dim objMsg As MailItem

' Blank message

' Set objMsg = Application.CreateItem(olMailItem)

' Use a template

Set objMsg = Application.CreateItemFromTemplate("C:\Users\Stoler Law\AppData\Roaming\Microsoft\Templates\E-mail From Lou Stoler.oft")
objMsg.To = oContact.Email1Address
'displays the message form so you can enter more text
objMsg.Display
'use this to send to outbox
'objMsg.Send
Set objMsg = Nothing

End Sub

Function GetCurrentItem2() As Object
Dim objApp As Outlook.Application

Set objApp = Application
On Error Resume Next
Select Case TypeName(objApp.ActiveWindow)
Case "Explorer"
Set GetCurrentItem = objApp.ActiveExplorer.Selection.Item(1)
Case "Inspector"
Set GetCurrentItem = objApp.ActiveInspector.CurrentItem
End Select

Set objApp = Nothing

End Function

And the new macro we are working on right now so far is as follows:

Public lstNo As Long

Public oContact As Outlook.ContactItem

Public Sub NewMessageUsingTemplate()

If TypeName(ActiveExplorer.Selection.Item(1)) = "ContactItem" Then

Set oContact = ActiveExplorer.Selection.Item(1) ' this works with the selected item, not opened

UserForm1.Show
Select Case lstNo
Case -1
From_Lou_Stoler 'macro name
Case 0
From_Lou_Stoler_and_Vcard 'macro name
Case 1
Good_Morning_Catch_Up 'macro name
Case 2
Good_Afternoon_Catch_Up 'macro name
Case 3
Good_Morning_Catch_Up_to_Client 'macro name
Case 4
Good_Afternoon_Catch_Up_to_Client 'macro name
Case 5
Today_Meeting_Thank_You_Friend 'macro name
Case 6
Recent_Meeting_Thank_You_Friend 'macro name

End Select

End If

Set oContact = Nothing

End Sub

Private Sub From_Lou_Stoler()

MsgBox "From_Lou_Stoler"

End Sub

Private Sub From_Lou_Stoler_and_Vcard()

MsgBox "Good_Morning_Catch_Up"

End Sub

Private Sub Good_Morning_Catch_Up()

MsgBox "From_Lou_Stoler_and_Vcard"

End Sub

Private Sub Good_Afternoon_Catch_Up()

MsgBox "Good_Afternoon_Catch_Up"

End Sub

Private Sub Good_Morning_Catch_Up_to_Client()

MsgBox "Good_Morning_Catch_Up_to_Client"

End Sub

Private Sub Good_Afternoon_Catch_Up_to_Client()

MsgBox "Good_Afternoon_Catch_Up_to_Client"

End Sub

Private Sub Today_Meeting_Thank_You_Friend()

MsgBox "Today_Meeting_Thank_You_Friend"

End Sub

Private Sub Recent_Meeting_Thank_You_Friend()

MsgBox "Recent_Meeting_Thank_You_Friend"

End Sub

And the userform1 code is:

Private Sub ComboBox1_Change()

End Sub

Private Sub ComboBox7_Change()

End Sub

Private Sub UserForm_Initialize()

With ComboBox7
.AddItem "From_Lou_Stoler"
.AddItem "From_Lou_Stoler_and_Vcard"
.AddItem "Good_Morning_Catch_Up"
.AddItem "Good_Afternoon_Catch_Up"
.AddItem "Good_Morning_Catch_Up_to_Client"
.AddItem "Good_Afternoon_Catch_Up_to_Client"
.AddItem "Today_Meeting_Thank_You_Friend"
.AddItem "Recent_Meeting_Thank_You_Friend"
End With
End Sub

Private Sub CommandButton4_Click()
lstNo = ComboBox7.ListIndex
Unload Me
End Sub

 

[LMS]

And I created inside the outlook template form, I added the macro to the Quick Access Bar of "NewMessageUsingTemplate" and when I click on it, the userform shows up, all the droplist per the above shows up and then i click on command next to it and it opens up a box that shows the name of the email form and when i click on that at the place "OK" it does not make the email

 

[Diane Poremsky]

This worked for me - only case 0 has a working macro with it - i switched it so it opens a new message addressed to the contact, rather than use a template.

The others pop up the message box, "telling" me the combo box is working.

In one module:

Public lstNo As Long
Public oContact As Outlook.ContactItem
Public Sub NewMessageUsingTemplate()
If TypeName(ActiveExplorer.Selection.Item(1)) = "ContactItem" Then
Set oContact = ActiveExplorer.Selection.Item(1) ' this works with the selected item, not opened
UserForm1.Show
Select Case lstNo
Case -1
From_Lou_Stoler 'macro name
Case 0
From_Lou_Stoler 'macro name
Case 1
Good_Morning_Catch_Up 'macro name
Case 2
Good_Afternoon_Catch_Up 'macro name
Case 3
Good_Morning_Catch_Up_to_Client 'macro name
Case 4
Good_Afternoon_Catch_Up_to_Client 'macro name
Case 5
Today_Meeting_Thank_You_Friend 'macro name
Case 6
Recent_Meeting_Thank_You_Friend 'macro name
End Select
End If
Set oContact = Nothing
End Sub

' Remove these, or replace them with the template macro code.

' i only used them to test the code.
Private Sub From_Lou_Stoler_and_Vcard()
MsgBox "Good_Morning_Catch_Up"
End Sub
Private Sub Good_Morning_Catch_Up()
MsgBox "From_Lou_Stoler_and_Vcard"
End Sub
Private Sub Good_Afternoon_Catch_Up()
MsgBox "Good_Afternoon_Catch_Up"
End Sub
Private Sub Good_Morning_Catch_Up_to_Client()
MsgBox "Good_Morning_Catch_Up_to_Client"
End Sub
Private Sub Good_Afternoon_Catch_Up_to_Client()
MsgBox "Good_Afternoon_Catch_Up_to_Client"
End Sub
Private Sub Today_Meeting_Thank_You_Friend()
MsgBox "Today_Meeting_Thank_You_Friend"
End Sub
Private Sub Recent_Meeting_Thank_You_Friend()
MsgBox "Recent_Meeting_Thank_You_Friend"
End Sub

In another module:

Public Sub From_Lou_Stoler()
Set oContact = GetCurrentItem()
Dim objMsg As MailItem
' Blank message
Set objMsg = Application.CreateItem(olMailItem)
' Use a template
'Set objMsg = Application.CreateItemFromTemplate("C:\Users\Stole r Law\AppData\Roaming\Microsoft\Templates\E-mail From Lou Stoler.oft")
objMsg.To = oContact.Email1Address
'displays the message form so you can enter more text
objMsg.Display
'use this to send to outbox
'objMsg.Send
Set objMsg = Nothing
End Sub

' you had GetCurrentItem6 here.
Function GetCurrentItem() As Object
Dim objApp As Outlook.Application
Set objApp = Application
On Error Resume Next
Select Case TypeName(objApp.ActiveWindow)
Case "Explorer"
Set GetCurrentItem = objApp.ActiveExplorer.Selection.Item(1)
Case "Inspector"
Set GetCurrentItem = objApp.ActiveInspector.CurrentItem
End Select
Set objApp = Nothing
End Function

 

[Diane Poremsky]

This is what I would do - everything in one module, one GetCurrentItem function.

Actually, I'd put the template name in the case and use 1 macro, like I did at VBA UserForm sample: Select from a list of templates - Slipstick Systems

Public lstNo As Long
Public oContact As Outlook.ContactItem
Public Sub NewMessageUsingTemplate()
If TypeName(ActiveExplorer.Selection.Item(1)) = "ContactItem" Then
Set oContact = ActiveExplorer.Selection.Item(1) ' this works with the selected item, not opened
UserForm1.Show
Select Case lstNo
Case -1
From_Lou_Stoler 'macro name
Case 0
From_Lou_Stoler 'macro name
Case 1
Good_Morning_Catch_Up 'macro name
Case 2
Good_Afternoon_Catch_Up 'macro name
Case 3
Good_Morning_Catch_Up_to_Client 'macro name
Case 4
Good_Afternoon_Catch_Up_to_Client 'macro name
Case 5
Today_Meeting_Thank_You_Friend 'macro name
Case 6
Recent_Meeting_Thank_You_Friend 'macro name
End Select
End If
Set oContact = Nothing
End Sub 
 
Private Sub From_Lou_Stoler()
Set oContact = GetCurrentItem()
Dim objMsg As MailItem
' Blank message
 Set objMsg = Application.CreateItem(olMailItem)
' Use a template
'Set objMsg = Application.CreateItemFromTemplate("C:\Users\Stole r Law\AppData\Roaming\Microsoft\Templates\E-mail From Lou Stoler.oft")
objMsg.To = oContact.Email1Address
'displays the message form so you can enter more text
objMsg.Display
'use this to send to outbox
'objMsg.Send
Set objMsg = Nothing
End Sub
Private Sub From_Lou_Stoler_and_Vcard()
Set oContact = GetCurrentItem()
Dim objMsg As MailItem
' Blank message
 Set objMsg = Application.CreateItem(olMailItem)
' Use a template
'Set objMsg = Application.CreateItemFromTemplate("C:\Users\Stole r Law\AppData\Roaming\Microsoft\Templates\E-mail From Lou Stoler.oft")
objMsg.To = oContact.Email1Address
'displays the message form so you can enter more text
objMsg.Display
'use this to send to outbox
'objMsg.Send
Set objMsg = Nothing
End Sub
Private Sub Good_Morning_Catch_Up()
 Set oContact = GetCurrentItem()
Dim objMsg As MailItem
' Blank message
 Set objMsg = Application.CreateItem(olMailItem)
' Use a template
'Set objMsg = Application.CreateItemFromTemplate("C:\Users\Stole r Law\AppData\Roaming\Microsoft\Templates\E-mail From Lou Stoler.oft")
objMsg.To = oContact.Email1Address
'displays the message form so you can enter more text
objMsg.Display
'use this to send to outbox
'objMsg.Send
Set objMsg = Nothing
End Sub
Private Sub Good_Afternoon_Catch_Up()
Set oContact = GetCurrentItem()
Dim objMsg As MailItem
' Blank message
 Set objMsg = Application.CreateItem(olMailItem)
' Use a template
'Set objMsg = Application.CreateItemFromTemplate("C:\Users\Stole r Law\AppData\Roaming\Microsoft\Templates\E-mail From Lou Stoler.oft")
objMsg.To = oContact.Email1Address
'displays the message form so you can enter more text
objMsg.Display
'use this to send to outbox
'objMsg.Send
Set objMsg = Nothing
End Sub
' repeat the private subs as needed
Function GetCurrentItem() As Object
Dim objApp As Outlook.Application
Set objApp = Application
On Error Resume Next
Select Case TypeName(objApp.ActiveWindow)
Case "Explorer"
Set GetCurrentItem = objApp.ActiveExplorer.Selection.Item(1)
Case "Inspector"
Set GetCurrentItem = objApp.ActiveInspector.CurrentItem
End Select
Set objApp = Nothing
End Function

 

[LMS]

So this is macro in one module and stlll have the userform we created? correct?

And as a example, instead of the words Case -1
From_Lou_Stoler 'macro name

use the words for each case the template name.....and is it what is below correct? and the template name is file name but not ost?

strTemplate = "templatename -1"

Is this correct as well?

 

[Diane Poremsky]

Yes, the module and the userform.

If you use the sample i have one the web site, you'll use something like this, where the filename is in the case.

Public lstNum As Long

Public Sub ChooseTemplate()

Dim oMail As Outlook.MailItem

Dim oContact As Outlook.ContactItem

If TypeName(ActiveExplorer.Selection.Item(1)) = "ContactItem" Then
Set oContact = ActiveExplorer.Selection.Item(1)

Dim strTemplate As String
UserForm1.Show
Select Case lstNum
Case -1
strTemplate = "E-mail From Lou Stoler"
Case 0
strTemplate = "E-mail From Lou Stoler welcome"
Case 1
strTemplate = "E-mail From Lou Stoler billing"
Case 2
strTemplate = "Recent_Meeting_Thank_You_Friend"
Case 3
strTemplate = "Good_Afternoon_Catch_Up"
Case 4
strTemplate = "E-mail From Lou Stoler-6"
End Select
strTemplate = "C:\Users\Stoler Law\AppData\Roaming\Microsoft\Templates\" & strTemplate & ".oft"
Set oMail = Application.CreateItemFromTemplate(strTemplate)

With oMail
.To = oContact.Email1Address
.Display

End With
End If

Set oMail = Nothing

End Sub

End Sub

 

[LMS]

I will go for it and let you know what happens. Thanks again so very much.

 

[Diane Poremsky]

If you want it ot work with open or selected contacts, replace the similar lines in the macro with this and use the GetCurrentItem function.

If TypeName(ActiveExplorer.Selection.Item(1)) = "ContactItem" Then
Set oContact = GetCurrentItem()

 

[LMS]

I did it all and not working....

Is the case name the name of the macro (which is what is in it) or the full path of the C drive to the email temptate....for example: "C:\Users\Stoler Law\AppData\Roaming\Microsoft\Templates\E-mail From Lou Stoler - Good Morning Catch-Up.oft"

And an error is that the words "Set oMail = Application.CreateItemFromTemplate(strTemplate)" are yellow and the error is that "File name or class name not found during automation operation.

And here is the full module.

Public lstNum As Long

Public Sub ChooseTemplate()

Dim oMail As Outlook.MailItem
Dim oContact As Outlook.ContactItem

If TypeName(ActiveExplorer.Selection.Item(1)) = "ContactItem" Then
Set oContact = GetCurrentItem()

Dim strTemplate As String
UserForm1.Show

Select Case lstNum
Case -1
strTemplate = "E-mail_From_Lou Stoler"
Case 0
strTemplate = "From_Lou_Stoler_and_Vcard"
Case 1
strTemplate = "Good_Morning_Catch-Up"
Case 2
strTemplate = "Good_Afternoon_Catch_Up"
Case 3
strTemplate = "Good_Morning_Catch_Up_to_Client"
Case 4
strTemplate = "Good_Afternoon_Catch_Up_to_Client"
Case 5
strTemplate = "Today_Meeting_Thank_You_Friend"
Case 6
strTemplate = "Recent_Meeting_Thank_You_Friend"
End Select

strTemplate = "C:\Users\Stoler Law\AppData\Roaming\Microsoft\Templates\" & strTemplate & ".oft"
Set oMail = Application.CreateItemFromTemplate(strTemplate)

With oMail
.To = oContact.Email1Address
.Display
End With
End If
Set oMail = Nothing

End Sub

Function GetCurrentItem() As Object
Dim objApp As Outlook.Application

Set objApp = Application
On Error Resume Next
Select Case TypeName(objApp.ActiveWindow)
Case "Explorer"
Set GetCurrentItem = objApp.ActiveExplorer.Selection.Item(1)
Case "Inspector"
Set GetCurrentItem = objApp.ActiveInspector.CurrentItem
End Select

Set objApp = Nothing

End Function

 

[Diane Poremsky]

This is the form and module I tested (with my own file paths and template names, i updated it to use yours). Right click on your form and module and remove them - use the option to save first so you can restore them if needed.




Unzip the file then go into the VBA editor and right click on project1 and import file. You'll need to import the templates.bas and userform1.frm.




I have a msgbox popping with the path to the template so you can see where it's looking for the template.




That is not the same error i get running your code (and I know the template doesn't exist here) and I'm not sure what the problem is.




View attachment templates.zip

 

[LMS]

thanks...i did it....the error again is:
Set oMail = Application.CreateItemFromTemplate(strTemplate) are yellow and the error is that "File name or class name not found during automation operation.

the case lines are the names of the macros....not the path to each email template......what should I change please?

 

[Diane Poremsky]

Yes, use the template names, that is probably the cause of the error. (fingers crossed)

 

[LMS]

also.....I always got emails that you replied....but I don't get those notices anymore......is your system changed?

- - - Updated - - -

and is this the right full format for the template name: ("C:\Users\Stoler Law\AppData\Roaming\Microsoft\Templates\E-mail From Lou Stoler - Good Morning Catch-Up.oft")