Multi Choice For Outlook

[Trevor3007]

Hi ,

I currently use the following code (supplied via Mr Excel , tweaked by me )

Sub sendEmail()
Dim olApp As Outlook.Application
Dim olMsg As Outlook.MailItem
Dim strTemplate As String
   
    Select Case MsgBox("Click Yes to select default template, MFT," & _
                        vbCrLf & "Click No to select Eduroam" & _
                        vbCrLf & vbCrLf & "Click Cancel to abort.", vbYesNoCancel + vbQuestion)
        Case vbYes
            strTemplate = "H:\Desktop\mail\MFA.oft"
        Case vbNo
            strTemplate = "H:\Desktop\mail\Eduroam.oft"
        Case Else
            Exit Sub
    End Select
   
    Set olApp = New Outlook.Application
    Set olMsg = olApp.CreateItemFromTemplate(strTemplate)
   
    olMsg.Display

End Sub

I would like to add more to the 'choice/messagebox :-
Case vbYes
strTemplate = "H:\Desktop\mail\SSP.oft"

and
Case vbYes
strTemplate = "H:\Desktop\mail\StandardReplyoft"

But these need to be numbered accordingly
1,2,3,4 & still have a 'cancel' option too.

Although i have attempted myself to sort, I cannot & therefore seek assistance from Mr Excel again for a solution.

many thanks to the kind person who can.

 

[GWteB]

Would suggest using a homemade message box for that. This can be done by means of a userform.

Let's create a Userform1 and make sure it carries at least a Label1, a ComboBox1, a CommandButton1 and a CommandButton2.

Paste the code below in the code module of Userform1:

Option Explicit

Private Type TOutlookTemplateChoises
    Choise As String
End Type
Private this As TOutlookTemplateChoises

Private Sub UserForm_Initialize()
    Me.CommandButton1.Caption = "Ok"
    Me.CommandButton2.Caption = "Cancel"
    InitComboBox
    Me.Label1.Caption = "Your message here ..."
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
        Cancel = True
    End If
End Sub

Private Sub CommandButton1_Click()
    Me.Hide
End Sub

Private Sub CommandButton2_Click()
    this.Choise = ""
    Me.Hide
End Sub

Private Sub ComboBox1_Change()
    this.Choise = ""
    Select Case Me.ComboBox1.Value
        Case "MFA":             this.Choise = "H:\Desktop\mail\MFA.oft"
        Case "Eduroam":         this.Choise = "H:\Desktop\mail\Eduroam.oft"
        Case "SSP":             this.Choise = "H:\Desktop\mail\SSP.oft"
        Case "StandardReply":   this.Choise = "H:\Desktop\mail\StandardReply.oft"
    End Select
End Sub

Public Function GetDefaultTemplateFullName() As String
    Me.Show vbModal
    GetDefaultTemplateFullName = this.Choise
End Function

Private Sub InitComboBox()
    With Me.ComboBox1
        .MatchRequired = True
        .Clear
        .AddItem "Eduroam"
        .AddItem "MFA"
        .AddItem "SSP"
        .AddItem "StandardReply"

        .Value = "StandardReply"        ' <<< default value
    End With
End Sub

Then modify your own code to use your custom popup:

Sub sendEmail()
Dim olApp As Outlook.Application
Dim olMsg As Outlook.MailItem
Dim strTemplate As String
Dim MyPopUp     As UserForm1

    Set MyPopUp = New UserForm1
    
    strTemplate = MyPopUp.GetDefaultTemplateFullName

    If Len(strTemplate) > 0 Then

        Set olApp = New Outlook.Application
        Set olMsg = olApp.CreateItemFromTemplate(strTemplate)

        olMsg.Display

    End If
End Sub

Hopefully this is what you're looking for.
Of course you are free to change the names of both userform and controls to meaningful names, this is even recommended, as long as you change the names in the code as well.

 

[Trevor3007]

many thanks for your swift reply. As you can see below, I have manged to do 'Label1, a ComboBox1' but how do I add 'CommandButton1 and a CommandButton2.'?

 

[GWteB]

So according to the order in the Toolbox of my version my best guess is:

 

[Trevor3007]

thanks GWteB,

sorry for the delayed reply. Ihave been trying to get this sorted. All I have manged to do is ( again thanks to you)

I also copied the code into userform1
Option Explicit

Private Type TOutlookTemplateChoises
Choise As String
End Type
Private this As TOutlookTemplateChoises

Private Sub UserForm_Initialize()
Me.CommandButton1.Caption = "Ok"
Me.CommandButton2.Caption = "Cancel"
InitComboBox
Me.Label1.Caption = "Your message here ..."
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
End If
End Sub

Private Sub CommandButton1_Click()
Me.Hide
End Sub

Private Sub CommandButton2_Click()
this.Choise = ""
Me.Hide
End Sub

Private Sub ComboBox1_Change()
this.Choise = ""
Select Case Me.ComboBox1.Value
Case "MFA": this.Choise = "H:\Desktop\mail\MFA.oft"
Case "Eduroam": this.Choise = "H:\Desktop\mail\Eduroam.oft"
Case "SSP": this.Choise = "H:\Desktop\mail\SSP.oft"
Case "StandardReply": this.Choise = "H:\Desktop\mail\StandardReply.oft"
End Select
End Sub

Public Function GetDefaultTemplateFullName() As String
Me.Show vbModal
GetDefaultTemplateFullName = this.Choise
End Function

Private Sub InitComboBox()
With Me.ComboBox1
.MatchRequired = True
.Clear
.AddItem "Eduroam"
.AddItem "MFA"
.AddItem "SSP"
.AddItem "StandardReply"

.Value = "StandardReply" ' <<< default value
End With
End Sub

and below

I ran the macro ( thinking all would be lovely) but

Please can you assist me further?

 

[GWteB]

As it works for me can you confirm that your buttons do have the correct name?

To determine al the properties of a userform and its controls (including their names ...) display the userform on your screen using view object.
Now press F4 to open the properties window and click successively on all controls to check their name.
These names must match the names as they are used in the code, see image attached.

I would suggest that you become a little more familiar with using the VBE. There are plenty of articles and videos on the net, such as ...

Index of /excel

Getting Familiar with VB Editor (VBE) : VBA Basics 002

VB Editor (or more commonly known as VBE) is the tool used to create, modify and maintain Visual Basic for Applications (VBA) procedures and modules in Excel

www.exceltrick.com

 

[Trevor3007]

As it works for me can you confirm that your buttons do have the correct name?

To determine al the properties of a userform and its controls (including their names ...) display the userform on your screen using view object.
Now press F4 to open the properties window and click successively on all controls to check their name.
These names must match the names as they are used in the code, see image attached.

I would suggest that you become a little more familiar with using the VBE. There are plenty of articles and videos on the net, such as ...

Index of /excel

Getting Familiar with VB Editor (VBE) : VBA Basics 002

VB Editor (or more commonly known as VBE) is the tool used to create, modify and maintain Visual Basic for Applications (VBA) procedures and modules in Excel

www.exceltrick.com

View attachment 53107

Many, many thanks GWteB,

Sorry for the late reply. This now works a treat . Have a great rest of your day & thanks again.

 

[GWteB]

You are welcome and thanks for letting me know