Combo Box

[Diane Poremsky]

Nothing changed on the forum. Spam filters are known to filter VBA code or short messages, so if you get some but not others, look in the junk folder.

Assuming that is the template name and where it was saved to, yes, that would be correct.

 

[LMS]

And should I use the same template name and where it is saved to for the userform code as below, or is each Additem just a title re each template from the module?

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

 

[Diane Poremsky]

The names in the combo box can be "friendly names" - they serve one purpose - so you know which one to pick.

 

[LMS]

Same debug re the yellow words.....Set oMail = Application.CreateItemFromTemplate(strTemplate)

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 = ("C:\Users\Stoler Law\AppData\Roaming\Microsoft\Templates\E-mail From Lou Stoler.oft")
Case 0
strTemplate = ("C:\Users\Stoler Law\AppData\Roaming\Microsoft\Templates\E-mail From Lou Stoler and Vcard.oft")
Case 1
strTemplate = ("C:\Users\Stoler Law\AppData\Roaming\Microsoft\Templates\E-mail From Lou Stoler - Good Morning Catch-Up.oft")
Case 2
strTemplate = ("C:\Users\Stoler Law\AppData\Roaming\Microsoft\Templates\E-mail From Lou Stoler - Good Afternoon Catch-Up.oft")
Case 3
strTemplate = ("C:\Users\Stoler Law\AppData\Roaming\Microsoft\Templates\E-mail From Lou Stoler - Good Morning Catch-Up to Client.oft")
Case 4
strTemplate = ("C:\Users\Stoler Law\AppData\Roaming\Microsoft\Templates\E-mail From Lou Stoler - Good Afternoon Catch-Up to Client.oft")
Case 5
strTemplate = ("C:\Users\Stoler Law\AppData\Roaming\Microsoft\Templates\E-mail From Lou Stoler - Today Follow-Up From Inital Event Introduction.oft")
Case 6
strTemplate = ("C:\Users\Stoler Law\AppData\Roaming\Microsoft\Templates\E-mail From Lou Stoler - Recent Follow-Up From Inital Event Introduction.oft")
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]

You are duplicating the path

Case 6
strTemplate = ("C:\Users\Stoler Law\AppData\Roaming\Microsoft\Templates\E-mail From Lou Stoler - Recent Follow-Up From Inital Event Introduction.oft")

and

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

if you are using the full path in the case statements, you don't need the second strTemplate line that sets the path - delete it.

 

[LMS]

thanks again....so please be clear to me ....what words do I delete from the module please?

Just: strTemplate = "C:\Users\Stoler Law\AppData\Roaming\Microsoft\Templates\" & strTemplate & ".oft"

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

 

[Diane Poremsky]

just strTemplate = "C:\Users\Stoler Law\AppData\Roaming\Microsoft\Templates\" & strTemplate & ".oft"

the other one tells outlook to create a new message from the template.

if all of the templates are in the same folder, you could keep that line and remove the path from the case statements. The .oft part can be in one lines or the other - its only 4 characters and is not a big deal id its in the case statement.

 

[LMS]

I did it and when I click in each different name in the combo box, and run it, each one opens up an email to the contact I am in, but uses the same template from:
(strTemplate = ("C:\Users\Stoler Law\AppData\Roaming\Microsoft\Templates\E-mail From Lou Stoler and Vcard.oft")

and does not got to any other template that is a part of the macro in the module.......

so what to fix now?

thanks

 

[Forum Admin]

if it works with the full path in the case statements, use it. It should work the other way too, but I'm not sure why its not.

 

[LMS]

Please see below, as what I did, is when I changed a case number to 0 and the original case 0 to a number of the case I changed to 0, the click the combobox just goes to whatever template is in Case 0.

So do you have any thoughts to fix it now?

Here is the useform code:

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

Here is the module code:

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 = ("C:\Users\Stoler Law\AppData\Roaming\Microsoft\Templates\E-mail From Lou Stoler.oft")
Case 0
strTemplate = ("C:\Users\Stoler Law\AppData\Roaming\Microsoft\Templates\E-mail From Lou Stoler and Vcard.oft")
Case -1
strTemplate = ("C:\Users\Stoler Law\AppData\Roaming\Microsoft\Templates\E-mail From Lou Stoler - Good Morning Catch-Up.oft")
Case 2
strTemplate = ("C:\Users\Stoler Law\AppData\Roaming\Microsoft\Templates\E-mail From Lou Stoler - Good Afternoon Catch-Up.oft")
Case 3
strTemplate = ("C:\Users\Stoler Law\AppData\Roaming\Microsoft\Templates\E-mail From Lou Stoler - Good Morning Catch-Up to Client.oft")
Case 4
strTemplate = ("C:\Users\Stoler Law\AppData\Roaming\Microsoft\Templates\E-mail From Lou Stoler - Good Afternoon Catch-Up to Client.oft")
Case 5
strTemplate = ("C:\Users\Stoler Law\AppData\Roaming\Microsoft\Templates\E-mail From Lou Stoler - Today Meeting Thank-You - Friend.oft")
Case 6
strTemplate = ("C:\Users\Stoler Law\AppData\Roaming\Microsoft\Templates\E-mail From Lou Stoler - Recent Meeting Thank-You - Friend.oft")
End Select
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

 

[Diane Poremsky]

in the userform:

lstNo = ComboBox7.ListIndex

in the macro:

Public lstNum As Long

Select Case lstNum

everything needs to match to work.

 

[LMS]

Those are the words in both the userform and macro per what you see I posted....i am confused...what should and where should i fix what? thanks so much as usual!!

 

[Diane Poremsky]

You need to use either lstNum or lstNo both places. The userform is assigning a value to lstNo but the macro isn't using it. The macro is looking for a value in lstNum, which is 0.

 

[LMS]

I did what you said to do, and used lstNum and there is the entire story:

Here is the history and the errors:

In the contact template form, I got to Customize Quick Access Toolbar, and then add the macro named "Choose Template", and when I click on it in any contact, the userform1 shows up and there is the Commandbutton4 to the left side, and then the Combobox7 to the right side of the userform1.

There is the droplist of each AddItem from the Userform that shows up in the Combobox7.

The AddItem list is in this order from the userform1:

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

and here is what happens with each click of each AddItem

Click on: "From_Lou_Stoler"

It runs the template: ("C:\Users\Stoler Law\AppData\Roaming\Microsoft\Templates\E-mail From Lou Stoler and Vcard.oft") from Case 0 which is the next template below the template it is supposed to run.

Click on: "From_Lou_Stoler_and_Vcard"

Invalid procedure or argument and the debug shows "Set oMail = Application.CreateItemFromTemplate(strTemplate)" in yellow

Click on: "Good_Morning_Catch_Up"

It runs the template: ("C:\Users\Stoler Law\AppData\Roaming\Microsoft\Templates\E-mail From Lou Stoler - Good Afternoon Catch-Up.oft") from Case2 which is the next template below the template it is supposed to run.

Click on: "Good_Afternoon_Catch_Up"

It runs the template: ("C:\Users\Stoler Law\AppData\Roaming\Microsoft\Templates\E-mail From Lou Stoler - Good Morning Catch-Up to Client.oft") from Case3 which is the next template below the template it is supposed to run.

Click on: "Good_Morning_Catch_Up_to_Client"

It runs the template: ("C:\Users\Stoler Law\AppData\Roaming\Microsoft\Templates\E-mail From Lou Stoler - Good Afternoon Catch-Up to Client.oft") from Case 4 which is the next template below the template it is supposed to run.

Click on: "Good_Afternoon_Catch_Up_to_Client"

It runs the template: ("C:\Users\Stoler Law\AppData\Roaming\Microsoft\Templates\E-mail From Lou Stoler - Today Meeting Thank-You - Friend.oft") which is the next template below the template it is supposed to run.

Click on: "Today_Meeting_Thank_You_Friend"

It runs the template: ("C:\Users\Stoler Law\AppData\Roaming\Microsoft\Templates\E-mail From Lou Stoler - Recent Meeting Thank-You - Friend.oft") from Case5 which is the next template below the template it is supposed to run.

Click on: "Recent_Meeting_Thank_You_Friend"

Invalid procedure or argument and the debug shows "Set oMail = Application.CreateItemFromTemplate(strTemplate)" in yellow

 

[Diane Poremsky]

On the two yellow errors: copy the template path and make sure it works.

The templates should be in case 0 - case 6 or whatever. Case -1 is what you want to do if nothing is selected - ie, use the template you want to use as default.

 

[LMS]

You are still the best!!!

It is now working and I have a droplist in my contact form!!!

PS, if I create a second userform and just have a different list email templates, other than the number of the comandbutton and combobox and name of the module, just create the additems and the related paths to templates....correct?

PSS. would love to thank you over phone if can at some point!!!

 

[Diane Poremsky]

Yes, you need the add items and the templates in the case statement. Only the userform really needs a new name - Private means they only work within that module or form. So command button and combo box can use the same names as long as they are in different forms. The main macro & the userform will need a new name. If that makes sense... I'm tired and may not be typing straight.

 

[LMS]

In the new Module8 I changed the name Public Sub ChooseTemplateto Thank_You_Emails ()

and I changed:

Dim strTemplate

As String UserForm1.Show

to:

Dim strTemplate As String

UserForm2.Show

I made sure that the Name of the CommandButton is CommandButton4and the name of the ComboBox is ComboBox7 each one which is the names of the first one we put together



And the name of the userform is userform2



And the error shows up as follows:



Ambiguous name detected - 1stNum



and Private SubCommandButton4_Click() is yellow and



lstNum of 1stNum = ComboBox7.ListIndex is blue



and: when i run thefirst userform, it is now the same errors.



Any thoughts on what to fix?

 

[Diane Poremsky]

Each userform needs to use a different variable - you'll need to use something else for lstNum in the second userform and macro.

 

[LMS]

Thanks very much as usual!!

Is lstNum the number one before the words stNum? or is it the letter l?

And can I add a number to the end of it so it would be lstNum2 ??

thanks so much...this is great!