Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2012
    Posts
    2

    Unanswered: VBA, more for Word than Excel

    I am fairly new with VBA. I have created a document which, when completed, allows the user to click on a button and email the whole document to me. That works fine.

    I want to modify it and am getting lost.
    What I want is:
    A list box which has the names of four different people in it.
    The user clicks on one, two, three, or all four of those listed names to highlight them. If they click on a name a second time, it unhighlights that name.
    Once the user has selected the person or persons they wanted, they will click the button, and it will send a copy of the document to the email address of each person highlighted in the list box.

    Here is the code I have used for my single button, and as I said, this works fine:

    Private Sub CommandButton1_Click()
    Dim oStory As Range
    For Each oStory In ActiveDocument.StoryRanges
    oStory.Fields.Update
    If oStory.StoryType <> wdMainTextStory Then
    While Not (oStory.NextStoryRange Is Nothing)
    Set oStory = oStory.NextStoryRange
    oStory.Fields.Update
    Wend
    End If
    Next oStory
    Set oStory = Nothing

    ActiveDocument.Fields.Update
    ActiveDocument.SaveAs FileName:="PERMISSIONS REQUEST.doc"


    MsgBox "Thank you. Your Permissions Request was sent to your IT Dept", vbOKOnly, "Thank you. Your Permissions Request was sent to your IT Dept"


    'This macro requires the Outlook Object library to be checked

    'in the vba editor Tools > References
    Dim bStarted As Boolean
    Dim oOutlookApp As Outlook.Application
    Dim oItem As Outlook.MailItem
    On Error Resume Next
    If Len(ActiveDocument.Path) = 0 Then 'Document has not been saved
    ActiveDocument.Save 'so save it
    End If
    'see if Outlook is running and if so turn your attention there
    Set oOutlookApp = GetObject(, "Outlook.Application")
    If Err <> 0 Then 'Outlook isn't running
    'So fire it up
    Set oOutlookApp = CreateObject("Outlook.Application")
    bStarted = True
    End If
    'Open a new e-mail message
    Set oItem = oOutlookApp.CreateItem(olMailItem)
    With oItem 'and add the detail to it
    .To = "stezan@sstuwa.org.au" 'send to this address
    .Subject = "Permissions Request Form" 'This is the message subject
    .Body = "Thank you. Your IT Department will review your form and contact you if there are any questions." ' This is the message body text
    .Attachments.Add Source:=ActiveDocument.FullName, Type:=olByValue
    .Send
    '**********************************

    End With
    If bStarted Then 'If the macro started Outlook, stop it again.
    oOutlookApp.Quit
    End If
    'Clean up
    Set oItem = Nothing
    Set oOutlookApp = Nothing
    End Sub

    -------------------------

    Can anyone help me to write coding to do what I need it to do with the list box names?

    Thank you.

    Zane

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    here i just wrote this for should point down the right track

    alt+F11 to view code

    then click once on the UserForm1 and press F5


    looking at you code off top of head I think you will outlook to resolve the email addresses it been a long I did something like this
    Attached Files Attached Files
    Last edited by myle; 11-27-12 at 06:33. Reason: spelling
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Nov 2012
    Posts
    2
    Thank you!

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    all good

    let us know how you get on.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •