Display Excel List in Outlook Combo box (rather than using .AddItem)

[pk247]

Hi Everyone,

I hope you are all well. I'm hoping maybe someone could possibly help me with a piece of code. I got the idea that this might be possible from Graham Mayor in this post (http://www.vbaexpress.com/forum/showthread.php?54200-Outlook-VBA-help-please-Automate-and-Bcc-based-on-word-in-subject-line) I created a few weeks back. What I see is that we can reference an excel list and use it for other code to reference.

What I'd like to do then (and I've failed miserably thus far on my own with google) is have a list from an Excel Sheet display in a Combo Box rather than having to edit the ComboBox1.AddItem "..." each time. Assuming this is possible I would then like to be able to use the selection from within the Combobox to tack on to my subject line.

The code I have thus far is very manual to update each time I work on a new Project. I really hope what I've written makes sense and is possible. I'd appreciate any help or pointers in the right direction if anyone could spare some time please.

Thanks! and if I need to clarify anything please get in touch.

Cheers,

Paul, Ireland :beerchug:

WITHIN THE USERFORM1

Private Sub ComboBox1_Change()

End Sub

Private Sub Userform_Initialize()

ComboBox1.AddItem "no change"
ComboBox1.AddItem "ABCDEF123111"
ComboBox1.AddItem "ABCDEF123555"
ComboBox1.AddItem "ABCDEF123777"
ComboBox1.AddItem "ABCDEF123888"
ComboBox1.AddItem "ABCDEF123999"


End Sub

Private Sub CommandButton1_Click()
lstNo = ComboBox1.ListIndex
Unload Me
End Sub




WITHIN THE CODE TO SELECT THE PROJECT CODE

Public lstNo As Long

Public Sub SubjectUpdate()


Dim objItem As Object
Dim oMail As Outlook.MailItem


Set objItem = GetCurrentItem()
Set oMail = Application.ActiveInspector.CurrentItem

With oMail

UserForm1.Show

Select Case lstNo
Case 0
oMail.Subject = objItem.Subject
Case 1
oMail.Subject = oMail.Subject & " - PROJECT CODE: ABCDEF123111"
Case 2
oMail.Subject = oMail.Subject & " - PROJECT CODE: ABCDEF123555"
Case 3
oMail.Subject = oMail.Subject & " - PROJECT CODE: ABCDEF123777"
Case 4
oMail.Subject = oMail.Subject & " - PROJECT CODE: ABCDEF123888"
Case 5
oMail.Subject = oMail.Subject & " - PROJECT CODE: ABCDEF123999"
End Select

End With



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

[gmayor]

See http://www.gmayor.com/Userform_ComboBox.html which includes code to read a worksheet into a combobox/listbox.

[pk247]

Thank you very much Graham!

I can get the list to populate now and it's very quick. The only trouble I'm having is capturing the selection from the ComboBox so that it pulls into the Subject Line. The code in my starting post above only works because of Select Case but I don't know how to pass the selection to Subject Line.

Thanks very much for the help with this. It's the final piece to all the coding you've helped me with so far. The process goes:

1. Any email I send I'm prompted to select the [Project Code]

a. If I don't want to or the Project Code is already in the Subject Line I just leave the combobox blank click OK
or
b. If I select a Project Code from the combobox, then click OK it adds the default text to the end of the subject line e.g. " - Company Name Project Code - ABCDE1234123"

2. If the Project Code in the Subject Line is in the Excel List (the code you helped me with recently) then ask "Do you want to Bcc?" Yes or No

3. If Yes then Bcc the Project Code + email@domain.com into the Public Folder, if no then exit procedure and let email send

I have all of the above working well thanks to you Graham but like I say I just need to get the selection from the ComboBox1 into the Subject Line. I hope you can help, I've tried a few different ways but I just don't know how to do it.

Cheers,

Paul, Ireland :beerchug:

[gmayor]

The code I directed you to fills a multicolumn combo or list box with the content of the worksheet, displaying only one selected column. However you can read any value from any of the columns to use in your process. See http://www.gmayor.com/ColumnValues.htm.

You would then probably need something like the following - here the reference is to 'ListRecords' You can change that to the name of your combo box.

Note that if you use a list box rather than a combo box the line

If .ListRecords.ListIndex > 0 Then
needs to be

If .ListRecords.ListIndex > -1 Then


'Declare the variables used
Dim strCode As String
Dim i As Long

If .ListRecords.ListIndex > 0 Then
strCode = .ListRecords.Column(1) 'the column you want to extract
.To = .To & " Project Code - " & strCode
End If
If InStr(1, .To, "Project Code -") > 0 Then
strCode = .To
strCode = Right(strCode, Len(strCode) - InStrRev(strCode, Chr(32)))
For i = 0 To .ListRecords.ListCount - 1
If Trim(strCode) = Trim(.ListRecords.Column(1)) Then
If MsgBox("Do you want to Bcc?", vbYesNo) = vbYes Then
.BCC = "email@domain.com"
End If
Exit For
End If
Next i
End If

[pk247]

Dear Graham,

Thank you! (again and again and again...)

Mixing the code above with that from your website (which I will need a month to read through cause it's so extensive) I got it to work :rotlaugh:

This is really going to help me save time in my working day.

Cheers!

Paul, Ireland :beerchug: