Combobox for new (created) mail item using VBA in Outlook 2007

Hi-

Would appreciate some help please... when I click on "New" to create a new e-mail, I want a combobox to appear that allows me to select one from a list of default strings to place in the SentOnBehalfOfName Field.

Thanks for your help--
Sam

Running W7 64
SAbboushiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris BottomleySoftware Quality Lead EngineerCommented:
How are you at creating a form in the VBE?

Chris
0
SAbboushiAuthor Commented:
I'm not even certain I know what you just asked ; )

My belief is that when I click on "New", an empty form is displayed which is the default form.  I was hoping I could modify that form to just add a combobox.

I wrote a few programs years ago in vb6 so I suspect you are refering to creating a form from scratch (looks like Tools, Forms, Design a form)?
0
Chris BottomleySoftware Quality Lead EngineerCommented:
It is possible I believe to make a template that could include a template .... But this is not in my capabilities.  I have literally finished a test creating a form and using that ... I used code to create the form so you wouldnt need to.  It would involve you enabling trust to the vb project

Not got 2007 but try office button in outlook, select options, select trust centre, select trust centre settings, click macros, check the trust access to the vba project box.  If you can find and do this I can supply some code to create a form easily following which you would again disable programmatic access ... For security.

At this point a few edits and you would have the previous question code with a combo!

Chris
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

SAbboushiAuthor Commented:
Under Tools, Trust Center, I see the following areas that seem relevant:
1) Macro Security: "Warnings for all macros" is checked
2) Programmatic Access: ("Warn me..." is highlighted, but all options greyed out with a line that says "Antivirus status: Valid"
3) E-mail security: "Allow script in Public Folders" is checked
4) Trusted Publishers: I have a certificate (created with selfcert.exe) listed here

Am I looking at the right stuff?
0
Chris BottomleySoftware Quality Lead EngineerCommented:
Yes, looks like 2 is the critical item.

We can try a test, but if it fails we can still proceed as long as you haveforeample excel to load a file.

I will supply some code shortly to paste into any normal code module.

Chris
0
SAbboushiAuthor Commented:
k thanks!
0
Chris BottomleySoftware Quality Lead EngineerCommented:
Add the following snippet to a code module and edit the code within to add the required addresses

        .InsertLines .CountOfLines + 1, "    me.cboitems.additem ""fred@fred.com"""
        .InsertLines .CountOfLines + 1, "    me.cboitems.additem ""doris@doris.com"""
        .InsertLines .CountOfLines + 1, "    me.cboitems.additem ""norman@norm.com"""

Look out for the quotes ,,, they need to be kept as they are, add as many lines as you need at that point.

Once it is edited then execute the sub ... if access is permitted then a new form will created, let me know it's name.

Chris
Sub make_form_1()
Dim tempform As Object 'msforms.UserForm
Dim newbutton As Object 'msforms.CommandButton
Dim newtextbox As Object 'msforms.TextBox
Dim newlabel As Object 'msforms.Label
Dim VBC As Object
Dim vbc_item As Object
Dim Ctrl As Object
Static intFormNumber As Integer
Const strFormNamePrefix As String = "Form_Test"
Const vbext_ct_MSForm As Integer = 3

    Set VBC = Application.VBE.ActiveVBProject.VBComponents
    Application.VBE.MainWindow.Visible = True
    Application.ScreenUpdating = True
' Delete form if pre-existing
    For Each vbc_item In VBC
        If Left(vbc_item.Name, Len(strFormNamePrefix)) = strFormNamePrefix Then VBC.Remove VBComponent:=vbc_item
    Next
    Set tempform = VBC.Add(vbext_ct_MSForm)
    
RedoRename:
    With tempform
    On Error Resume Next
        Do While .Properties("Name") <> "Form_Test" & "_" & intFormNumber
            intFormNumber = intFormNumber + 1
            .Properties("Name") = "Form_Test" & "_" & intFormNumber
        Loop
        On Error GoTo 0
        .Properties("Caption") = "Select Send on Behalf of Name"
        .Properties("Top") = 36
        .Properties("Height") = 60
        .Properties("Left") = 18
        .Properties("Width") = 200
        .Properties("BackColor") = 14737632
        .Properties("ForeColor") = -2147483630
    End With
    
'###############################
    Set Ctrl = tempform.Designer.Controls.Add("Forms.combobox.1") ' (Class, Name, visible)
    With Ctrl
        .Name = "cboItems"
        .Left = 10
        .Top = 10
        .Width = 150
        .Height = 20
        .Visible = True
    End With
    
    With tempform.CodeModule
        .InsertLines .CountOfLines + 1, "'### SUB: UserForm_Activate -|- Event Handler for Form Load ###"
        .InsertLines .CountOfLines + 1, "Private Sub UserForm_Activate()"
        .InsertLines .CountOfLines + 1, "    me.cboitems.additem ""fred@fred.com"""
        .InsertLines .CountOfLines + 1, "    me.cboitems.additem ""doris@doris.com"""
        .InsertLines .CountOfLines + 1, "    me.cboitems.additem ""norman@norm.com"""
        .InsertLines .CountOfLines + 1, "End Sub"
        .InsertLines .CountOfLines + 1, ""
        .InsertLines .CountOfLines + 1, "'### SUB: cboItems_Change -|- Event Handler for combo change ###"
        .InsertLines .CountOfLines + 1, "Private Sub cboItems_Change()"
        .InsertLines .CountOfLines + 1, "    Me.Hide"
        .InsertLines .CountOfLines + 1, "End Sub"
        .InsertLines .CountOfLines + 1, ""
    End With

    Application.VBE.MainWindow.Visible = True
    Application.ScreenUpdating = True
    Set VBC = Nothing
    Set tempform = Nothing
    GoTo endex
endex:
End Sub

Open in new window

0
SAbboushiAuthor Commented:
>> "a code module"

I pasted into a "Module" with default name "Module1" - is that what you meant?

I ran the code and it choked on
Set VBC = Application.VBE.ActiveVBProject.VBComponents

Open in new window

with error "Object doesn't support this property or method"
0
Chris BottomleySoftware Quality Lead EngineerCommented:
Forget the code, upload the excel file here and follow the instructions:

Open the file in excel
Open the excel VBE
Right click the form, Form_Test_1
Export file … Navigate to somewhere specific and REMEMBER!
Switch over to Outlook
In the Outlook VBE
File | Import File
Navigate to the file saved at step 3
Select it and open.
Outlook should now have a form we can use!

Chris form-test-1.xls
0
SAbboushiAuthor Commented:
OK - done.  What's next?
0
Chris BottomleySoftware Quality Lead EngineerCommented:
Just got a meeting, will be back shortly.

Chris
0
Chris BottomleySoftware Quality Lead EngineerCommented:
Quick post!

In the inspector class module, replace the sub: objInspectorCollection_NewInspector for the code below and then save the project.

Close outlook and re-open, see how it goes!

Chris
Private Sub objInspectorCollection_NewInspector(ByVal Inspector As Inspector)
'Triggered whenever a Window or outlook Item is opened
Dim strOnBehalf As String
Dim strSubject As String
     
'    strOnBehalf = "fred@fred.com"
    strSubject = "Canned Subject"
    If Inspector.CurrentItem.Class = olMail Then
        With Inspector.CurrentItem
            If .EntryID = "" And .subject = "" And .Sent = False Then
                Form_Test_1.Show
                strOnBehalf = Form_Test_1.cboItems.List(Form_Test_1.cboItems.ListIndex)
                Unload Form_Test_1
                .SentOnBehalfOfName = strOnBehalf
                .subject = strSubject
            End If
        End With
    End If
     
End Sub

Open in new window

0
Chris BottomleySoftware Quality Lead EngineerCommented:
Slight tweak just in case the form is closed without making a selection.

Chris
Private Sub objInspectorCollection_NewInspector(ByVal Inspector As Inspector)
'Triggered whenever a Window or outlook Item is opened
Dim strOnBehalf As String
Dim strSubject As String
     
'    strOnBehalf = "fred@fred.com"
    strSubject = "Canned Subject"
    If Inspector.CurrentItem.Class = olMail Then
        With Inspector.CurrentItem
            If .EntryID = "" And .subject = "" And .Sent = False Then
                Form_Test_9.Show
                If Form_Test_9.cboItems.ListIndex = -1 Then
                    strOnBehalf = ""
                Else
                    strOnBehalf = Form_Test_9.cboItems.List(Form_Test_9.cboItems.ListIndex)
                End If
                Unload Form_Test_9
                .SentOnBehalfOfName = strOnBehalf
                .subject = strSubject
            End If
        End With
    End If
     
End Sub

Open in new window

0
Chris BottomleySoftware Quality Lead EngineerCommented:
Edited back to your form name, more haste - less speed!

Chris
Private Sub objInspectorCollection_NewInspector(ByVal Inspector As Inspector)
'Triggered whenever a Window or outlook Item is opened
Dim strOnBehalf As String
Dim strSubject As String
     
'    strOnBehalf = "fred@fred.com"
    strSubject = "Canned Subject"
    If Inspector.CurrentItem.Class = olMail Then
        With Inspector.CurrentItem
            If .EntryID = "" And .subject = "" And .Sent = False Then
                Form_Test_1.Show
                If Form_Test_1.cboItems.ListIndex = -1 Then
                    strOnBehalf = ""
                Else
                    strOnBehalf = Form_Test_1.cboItems.List(Form_Test_1.cboItems.ListIndex)
                End If
                Unload Form_Test_1
                .SentOnBehalfOfName = strOnBehalf
                .subject = strSubject
            End If
        End With
    End If
     
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SAbboushiAuthor Commented:
Brilliant!  It will take me some time to try and figure out exactly what is going on.  I am still very hazy on things like:
1) Why you put the code in a class module instead of in ThisOutlookSession
2) ... on second thought, too tired to even think about what I don't yet understand.  4am my time... zzzz....

Thanks again and again Chris.  Have a great day!
0
SAbboushiAuthor Commented:
EXCELLENT!  Thank--
0
CodeCruiserCommented:
Hm. Should have refreshed the page before posting :-)
0
Chris BottomleySoftware Quality Lead EngineerCommented:
When you awake ...

I used a class module as it easier to pick out diverse functions ... I have and still do use thisoutlooksession but am trying to be more tidy these days.

Chris
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Outlook

From novice to tech pro — start learning today.