Solved

Create a drop down input box when creating a task

Posted on 2010-11-14
17
756 Views
Last Modified: 2012-08-13
Hello Experts,

I would like to do the following with your generous help:

1) Select a button which creates a task from an existing e-mail
2) Upon creation of task, have an input box that is a drop down list which is stored as part of a text string that ultimately becomes part of the subject line of the task.

WHERE I NEED HELP, MY DEAR EXPERTS:
a) How to create this input box drop down
b) Where/How to maintain the list of values that appears in the drop down.  This would be a manually managed list which I am assuming I can just edit within a given VBA module.

Thanks so much.  I look forward to your replies.
0
Comment
Question by:dustywork
  • 10
  • 7
17 Comments
 
LVL 13

Expert Comment

by:gbanik
ID: 34131538
Firstly, since it is Outlook you can make use of the Categories. It is very flexible and may be sufficient for what u r trying to do.

If you still think u want more, you can create a VBA code to sit on the Outlook toolbar. It is basically a form shown as "vbmodeless". It contains a drop down. If any mail is selected (or multiple mails), then the drop down is enabled. Else it is disabled. If you change the drop down value, a task is automatically created from the selected email with the drop down text being appended to the subject (as u want).

Suggest.
0
 

Author Comment

by:dustywork
ID: 34131588
Hi gbanik,

Thank you for your prompt reply.  I am utilizing Categories for another dimension of my task tracking, and since establishing a category heirarchy is simply too difficult in Outlook, I wanted to at least standardize some text which will serve as key words for searches.

Would it be possible to have this drop down embedded in the code (just to not add more clutter to an already busy toolbar)?  Also, how may I direct this drop down to look to a pre-established list as the items displayed in its drop-down?

Again, thanks.
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34131660
I have quickly created a form "frmMain". Add it to your Outlook project by importing it in. (Place both the attached files in a directory and open the Outlook VBA project. Right click and import the UserForm "frmMain")

Open the form using a button in the Outlook toolbar or place the following code in the NewMailEx event (this is the event that gets fired when a new mail arrives)
frmMain.show vbModeless

Keeping a mail selected, select one of the items in the dropdown and hit "OK". The email is marked as a task and the drop down text is appended to the subject.

Is this a good direction?
Option Explicit



Private colCats As Collection



Private Sub cmdOK_Click()

On Error GoTo ErrCheck

Dim oMail As MailItem

For Each oMail In Application.ActiveExplorer.Selection

    If InStr(1, oMail.Subject, "[" & ComboBox1.Text & "] ", vbTextCompare) = 0 Then

        oMail.MarkAsTask olMarkNoDate

        oMail.Subject = "[" & ComboBox1.Text & "] " & oMail.Subject

        oMail.Save

    End If

Next

Exit Sub

ErrCheck:

Err.Clear

End Sub

Open in new window

frmMain.frm
frmMain.frx
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34131677
Missed the code to add the list items in the drop down.

Private Sub UserForm_Initialize()
Dim iCtr As Integer

Set colCats = New Collection
colCats.Add "Type A"
colCats.Add "Type B"
colCats.Add "Type C"
colCats.Add "Type D"
ComboBox1.Clear

For iCtr = 1 To colCats.Count
    ComboBox1.AddItem colCats(iCtr)
Next
End Sub

Open in new window

0
 

Author Comment

by:dustywork
ID: 34131753
gbanik,

Thanks - this is a big step forward.  The collection you have coded is not displaying in the drop down.  What adjustment do I need to make to the code.  Also, is there a way to allow items to be added to the collection on the fly from the combo box.

Thanks so much.
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34131772
Add both the codes in the form. It should work.
Option Explicit



Private colCats As Collection



Private Sub cmdOK_Click()

On Error GoTo ErrCheck

Dim oMail As MailItem

For Each oMail In Application.ActiveExplorer.Selection

    If InStr(1, oMail.Subject, "[" & ComboBox1.Text & "] ", vbTextCompare) = 0 Then

        oMail.MarkAsTask olMarkNoDate

        oMail.Subject = "[" & ComboBox1.Text & "] " & oMail.Subject

        oMail.Save

    End If

Next

Exit Sub

ErrCheck:

Err.Clear

End Sub



Private Sub UserForm_Initialize()

Dim iCtr As Integer



Set colCats = New Collection

colCats.Add "Type A"

colCats.Add "Type B"

colCats.Add "Type C"

colCats.Add "Type D"

ComboBox1.Clear



For iCtr = 1 To colCats.Count

    ComboBox1.AddItem colCats(iCtr)

Next

End Sub

Open in new window

0
 

Author Comment

by:dustywork
ID: 34131810
gbanik,

Thanks!  When I insert the form into my existing code (see line 17 below) the input boxes will appeart before I am able to select a value from the combo box.  Is there a better way to go about this?

Also - is there a way to add items to the collection on the fly, or establish a form that will allow me to manage the items in the collection?  Just wondering...

Thanks.
Sub ProjectDelegateIt()



Dim currentOLApp As Outlook.Application

Dim objNameSpace As NameSpace

Dim prpCategories As categories

Dim mthCategory As Category

Dim strProjectTitle As String

Dim strActionTitle As String

Dim strDelegateTo As String

Dim strErrMsg As String

Dim strNoSlct As String

Dim newTask As Outlook.TaskItem



Set currentOLApp = CreateObject("Outlook.Application")

Set currentNameSpace = currentOLApp.GetNamespace("MAPI")



frmMain.Show vbModeless

'strProjectTitle = InputBox("Enter Project Name")

strDelegateTo = InputBox("Delegate To")

strActionTitle = InputBox("Enter Next Action")

 

' Assign Waiting For Category

Set prpCategories = currentNameSpace.categories

Set mthCategory = prpCategories.item(WaitingForText)



CreateTask:

' Something needs to be selected

    If ExplorerHasSelectedItems() Then

        Dim selectedItem As Object

        

        Set selectedItem = Outlook.Application.ActiveExplorer.Selection.item(1)

            

        Set newTask = currentOLApp.CreateItem(olTaskItem)

        

        newTask.subject = "[ " & ComboBox1.Text & " ] " & strDelegateTo & ", " & strActionTitle

        'newTask.subject = GetSubjectFromItem(selectedItem)

        newTask.body = GetItemAsText(selectedItem)

        

        ' Add the item as an attachment

        ' Remove for Outlook 2010.

        newTask.Attachments.Add selectedItem

         

        ' Now we can do the task editing.

        newTask.Display

        newTask.categories = mthCategory

        'newTask.ShowCategoriesDialog

         

    Else

    

        strNoSlct = MsgBox("You must select an item to create an action", vbCritical)

            

    End If



End Sub

Open in new window

0
 
LVL 13

Expert Comment

by:gbanik
ID: 34131832
I dont know exactly what u r trying to do, but to add the items in the fly, remove the code from the UserForm_Initialize to a new subroutine like the attached code and call it when u need it.

So UserForm_Initialize will look like:

Private Sub UserForm_Initialize()
Set colCats = New Collection
colCats.Add "Type A"
colCats.Add "Type B"
colCats.Add "Type C"
colCats.Add "Type D"
End Sub

Create another sub that will look like

Private sub AddItemsToCombo()
Dim iCtr as integer
ComboBox1.Clear
For iCtr = 1 To colCats.Count
    ComboBox1.AddItem colCats(iCtr)
Next
End Sub

Now call the "AddItemsToCombo" after you have accepted your details.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:dustywork
ID: 34131863
Hi gbanik,

I'm not doing a very good job of explaining myself.  Sorry about that.  I have two remaining items that I would be most grateful for your assistance:

1) In the code I submitted, I am not clear on how to cause line 18 to wait until I have clicked ok from the form you created which I call in line 17

2) The second item is - other than adding lines to the code (e.g., colCats.Add "Type E", colCats.Add "Type F").  Is there a way for me to add these new items without having to go to the code directly.

If #2 is too difficult or convoluted - no worries.  I just was interested.

Kind Regards,
Gary
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34131896
1. use vbModal instead of vbModeless
2. how do you want them to be added? please explain in detail. we may be able to do it.
0
 

Author Comment

by:dustywork
ID: 34131943
1) This works.  I am getting an "Object Required" on line 35 of the code above due to my referencing the combobox1.text.  I tried establishing a global variable in the form, but no luck.

2) When the combo box appears, I would like to be able to type in a new category and when "Ok" is selected this text is added to the existing items in collection.  E.g., I type in Type E into the combo box, click Ok and Type E is both added to the collection as well a the combobox.text value.

Many thanks for your time.
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34132010
1. If the form object is not being created or disposed when line 35 is being called then we have a problem. I am assuming that this line is being called after the form has been opened, combo selected and button clicked. On button click, set a global variable with the value combobox1.text. Call
unload frmMain
to close the form as the last statement of the button click
Use that value for your code.

2. In order to add the newly added text to the collection colCats, declare this collection globally. Add the item if it does not exist in the collection. Next time when u load the combo, this newly added text will display as drop down.
Remember, this item will reset once the project is closed, that is, when the Outlook is shut and restarted.
In order to salvage that value, write the items into a registry key and load them always when the Outlook starts or the form opens.
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34132036
Use the following code to check if item exists in collection

Private Function CheckIfItemExists(sItem As String) As Boolean
On Error Resume Next
sItem = colCats("K" & sItem)
If Err.Number = 0 Then
    CheckIfItemExists = True
Else
    Err.Clear
End If
End Function

And use this code to add the item into collection
colCats.Add "K" & sItem, sItem
instead of just
colCats.Add sItem
0
 

Author Comment

by:dustywork
ID: 34132142
Regarding #1,what am I doing wrong?  I can't keep the variable in line 17 to hold the combobox1.text.  Once I get to "Exit Sub" the value of strProjectTitle returns to ""
 
Option Explicit

Public strProjectTitle As String



Private colCats As Collection



Private Sub cmdOK_Click()

On Error GoTo ErrCheck

Dim oMail As MailItem

'For Each oMail In Application.ActiveExplorer.Selection

'    If InStr(1, oMail.subject, "[" & ComboBox1.Text & "] ", vbTextCompare) = 0 Then

'       oMail.MarkAsTask olMarkNoDate

'        oMail.subject = "[" & ComboBox1.Text & "] " & oMail.subject

'        oMail.Save

'    End If

'Next



strProjectTitle = ComboBox1.Text

Unload frmMain



Exit Sub

ErrCheck:

Err.Clear

End Sub

Open in new window

0
 
LVL 13

Accepted Solution

by:
gbanik earned 500 total points
ID: 34132164
strProjectTitle has to be kept outside the form. Add a new module and here declare "strProjectTitle" as a public variable. So the code in the new module. (Remove the declaration from the current place).

Public strProjectTitle as string

thats it!
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34132175
and use
Unload Me
rather than
Unload frmMain
in your frmMain code
0
 

Author Closing Comment

by:dustywork
ID: 34132229
Thank you so much for the time.  Extremely helpful!
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Get an idea of what you should include in an email disclaimer with these Top 5 email disclaimer tips.
If you don't know how to downgrade, my instructions below should be helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now