Solved

Create a drop down input box when creating a task

Posted on 2010-11-14
17
773 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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
 

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Find out what you should include to make the best professional email signature for your organization.
This article lists the top 5 free OST to PST Converter Tools. These tools save a lot of time for users when they want to convert OST to PST after their exchange server is no longer available or some other critical issue with exchange server or impor…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…

839 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