Create a form in outlook to pre-populate values

[Derek_123]

Hi Guys

I am new to outlook vba and would like to create a form that will open up when user click on a button on the ribbon. It should pre-populate the following things:

1. The email subject on the form label.
2. 10 digit account number in a textbox on the form. (The account number is the part of the subject line of email so it needs validation like finding 10 digits in the subject line and store it in textbox)
3. There should be listbox which needs to be populated with 20 items suppose A,B,C....T

Can anyone please help me with this?

Many Thanks

[Derek_123]

Any help on this anyone?

[gmayor]

Creating a VBA userform is easy enough - see attached, which you can unzip and import into the Outlook VBA editor. The bigger question is what you want to do with the form when you have displayed it.

You can call the attached form with the following code


Sub MyForm()
Dim olMsg As MailItem
Dim strSubject As String
Dim vWord As Variant
Dim i As Integer, j As Long
Dim bValid As Boolean
Dim oFrm As frmMessage
On Error Resume Next
bValid = False
Set olMsg = ActiveExplorer.Selection.item(1)
strSubject = olMsg.Subject
vWord = Split(strSubject, Chr(32))
For i = 0 To UBound(vWord)
If Len(vWord(i)) = 10 Then
bValid = True
Exit For
End If
Next i
If bValid = True Then
Set oFrm = New frmMessage
With oFrm
.txtAccount = vWord(i)
.txtSubject = strSubject
With .ListBox1
For j = 1 To 20
.AddItem Chr(64 + j)
Next j
End With
.Show
If .Tag = 1 Then MsgBox "Do something with the form"
End With
Unload oFrm
End If
lbl_Exit:
Set olMsg = Nothing
Set oFrm = Nothing
Exit Sub
End Sub

[Derek_123]

I'm getting complie error 'user-defined type not defined' at the following line. Also I can't open the Files attached.


Dim oFrm As frmMessage

[gmayor]

You have the error because you haven't installed the form (frmMessage) which is in the zip. Unzip the files and import the form (frmMessage.frm) to the VBA editor (File > Import File).

[Derek_123]

Thanks Graham, I have imported the form in my outlook but it doesn't do anything . How can I display the subject and account number from the selected email in the controls on the form and the listbox should get populated with values from A to T ?? I want something like a button on the ribbon when you open an email and then when that button is clicked then the userform 'frmMessage' is opened and then controls get populated with the selected email 'subject line' ????

[gmayor]

You need to run the macro MyForm with a message selected that has a 10 digit word in the subject. You can add the macro to a ribbon button or the QAT (Quick Access Toolbar)
The procedure is much the same as that described at http://www.gmayor.com/installing_macro.htm

[Derek_123]

Thanks Graham, Now the form gets populated with subject line, Account number and items in the listbox.

How can I customize the ribbon so that when the user double click on email to open it then on the ribbon there is a button "Click Me" and when the button is clicked then automatically the userform 'frmMessage' gets displayed and the controls on it get populated with the values .

[Derek_123]

Graham, I have managed to create a button on the ribbon and assigned a macro 'MyForm' to it but when I select an email and click on it then it doesn't do anything.

Any you please shed any light on this?

Many Thanks

[Derek_123]

Can anyone please help me in this ? It's very urgent to resolve. Thanks