#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2011
    Posts
    2
    Rep Power
    0

    Automate Subject Line using List Box


    Hi,

    I an new to VB and I would apreciate help in developing this code in Outlook. Currently the code looks for restrcited in the subject line and proses a yes no cancel option to insert restricted in the subject line and send email, send the email without restricted in subject line or cancel the email sending.

    What I woould like to do is use a list box to offer the options for the subject line on send ing an email. So the the code will look for restricted and then offer options.

    I have detailed below my current code along with my attempt at a list box. I would like guidance on how I select the option on the list box and then get that option into my code .

    I an new vba so I apologise in advance if I am asking something thats obvious

    Thanking you all in advance

    Jim

    Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
    If LCase(Left(Trim(Item.Subject), 11)) = "restricted" Then
    '
    ' the subject line already shows restricted, so allow the
    ' email to be sent
    '
    Cancel = False
    Else
    '
    ' the subject line has not been marked as restricted, so
    ' ask the user how to handle
    '
    Dim prompt As String
    prompt = "Your email has not been flagged as restricted" & vbCrLf & vbCrLf & _
    "Subject line:" & vbCrLf & _
    Item.Subject & vbCrLf & vbCrLf & _
    "Click YES if you want to flag as restricted and send the email." & vbCrLf & _
    "Click NO if you want to send the email now as not restricted." & vbCrLf & _
    "Click CANCEL to cancel sending the email so you can continue editing."

    Dim result As VbMsgBoxResult
    result = MsgBox(prompt, vbYesNoCancel + vbQuestion + vbMsgBoxSetForeground, "Confirm send")

    Select Case result
    Case VbMsgBoxResult.vbYes:
    '
    ' Add restricted to subject line and allow email to be sent Item.Subject = "restricted " + Item.Subject
    Cancel = False
    Case VbMsgBoxResult.vbNo:
    '
    ' Send message as not restricted
    '
    Cancel = False
    Case VbMsgBoxResult.vbCancel:
    '
    ' do not send the email
    '
    Cancel = True
    End Select
    End If

    LIST Box attempt

    Private Sub UserForm_Initialize()
    With Me.ComboBox1
    .AddItem "Restricted"
    .AddItem "Restricted Staff"
    .AddItem "Restricted Investigation"
    .AddItem "None"
    .AddItem " Re-Edit"
    End With
    End Sub
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    23
    Rep Power
    0

    user options


    Originally Posted by jimhopper
    Hi,

    I an new to VB and I would apreciate help in developing this code in Outlook. Currently the code looks for restrcited in the subject line and proses a yes no cancel option to insert restricted in the subject line and send email, send the email without restricted in subject line or cancel the email sending.

    What I woould like to do is use a list box to offer the options for the subject line on send ing an email. So the the code will look for restricted and then offer options.

    I have detailed below my current code along with my attempt at a list box. I would like guidance on how I select the option on the list box and then get that option into my code .

    I an new vba so I apologise in advance if I am asking something thats obvious

    Thanking you all in advance

    Jim

    Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
    If LCase(Left(Trim(Item.Subject), 11)) = "restricted" Then
    '
    ' the subject line already shows restricted, so allow the
    ' email to be sent
    '
    Cancel = False
    Else
    '
    ' the subject line has not been marked as restricted, so
    ' ask the user how to handle
    '
    Dim prompt As String
    prompt = "Your email has not been flagged as restricted" & vbCrLf & vbCrLf & _
    "Subject line:" & vbCrLf & _
    Item.Subject & vbCrLf & vbCrLf & _
    "Click YES if you want to flag as restricted and send the email." & vbCrLf & _
    "Click NO if you want to send the email now as not restricted." & vbCrLf & _
    "Click CANCEL to cancel sending the email so you can continue editing."

    Dim result As VbMsgBoxResult
    result = MsgBox(prompt, vbYesNoCancel + vbQuestion + vbMsgBoxSetForeground, "Confirm send")

    Select Case result
    Case VbMsgBoxResult.vbYes:
    '
    ' Add restricted to subject line and allow email to be sent Item.Subject = "restricted " + Item.Subject
    Cancel = False
    Case VbMsgBoxResult.vbNo:
    '
    ' Send message as not restricted
    '
    Cancel = False
    Case VbMsgBoxResult.vbCancel:
    '
    ' do not send the email
    '
    Cancel = True
    End Select
    End If

    LIST Box attempt

    Private Sub UserForm_Initialize()
    With Me.ComboBox1
    .AddItem "Restricted"
    .AddItem "Restricted Staff"
    .AddItem "Restricted Investigation"
    .AddItem "None"
    .AddItem " Re-Edit"
    End With
    End Sub
    where you have added "restricted" use restricted_phrase
    generate that phrase from your userform and collect it when you accept the selected value and close the form.

    your instructions are a little confusing given the options on the form

    bill stewart - here to listen
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2011
    Posts
    2
    Rep Power
    0
    Originally Posted by the-listener
    where you have added "restricted" use restricted_phrase
    generate that phrase from your userform and collect it when you accept the selected value and close the form.

    your instructions are a little confusing given the options on the form

    bill stewart - here to listen
    Bill,

    Thank you for your response

    Sorry for the confusion. I would like to use a userform to replace the current code which is restricted to just 3 options

    I would like to call the userform from the first part of the code

    Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
    If LCase(Left(Trim(Item.Subject), 11)) = "restricted" Then
    '
    ' the subject line already shows restricted, so allow the
    ' email to be sent
    '
    Cancel = False
    Else
    '
    ' the subject line has not been marked as restricted, so
    ' ask the user how to handle

    But I do not know how to call it from the code

    Once the form has been called the user will select the option appropraite and the option selcted would be addded to the existing subject line information and then e-mail would be sent.

    I would appreciate any help you can provide on this

    Thanking you in advance
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    23
    Rep Power
    0
    Originally Posted by jimhopper
    Bill,

    Thank you for your response

    Sorry for the confusion. I would like to use a userform to replace the current code which is restricted to just 3 options

    I would like to call the userform from the first part of the code

    Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
    If LCase(Left(Trim(Item.Subject), 11)) = "restricted" Then
    '
    ' the subject line already shows restricted, so allow the
    ' email to be sent
    '
    Cancel = False
    Else
    '
    ' the subject line has not been marked as restricted, so
    ' ask the user how to handle

    But I do not know how to call it from the code

    Once the form has been called the user will select the option appropraite and the option selcted would be addded to the existing subject line information and then e-mail would be sent.

    I would appreciate any help you can provide on this

    Thanking you in advance
    i am not at my own machine...
    but, i have looked at vb within this one outlook 2007

    i simply threw some options at a combobox on a userform and hid the form once i had chosen one of them

    the result appears in the calling routine as userform1.combobox1.seltext

    you can do what you want with the string after that

    Sub forms()
    UserForm1.ComboBox1.AddItem "fred"
    UserForm1.ComboBox1.AddItem "jim"
    UserForm1.ComboBox1.AddItem "harry"
    UserForm1.ComboBox1.AddItem "george"
    UserForm1.Show
    Debug.Print UserForm1.ComboBox1.SelText
    Stop
    End Sub

    on the form the action for the combobox1_change() simply says hide

    showing that the form state is maintained after you close it (hide) and it will persist until you close the outlook session.

    hope this helps

    bill stewart - can do more if needed

IMN logo majestic logo threadwatch logo seochat tools logo