Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA Code help (Word)

    Hi all,
    I have created a form template in Word 2000, it is saved as Travel
    Request Form.dot (note the .dot). At the bottom of the form I have created
    four buttons whose names and functions appear below:

    CommandButton1 - This button is labled Save Form and Send by E-mail. What
    I want this button to do is to save the current form into a folder called
    C:TravelRequests with the name of the saved document being the contents of
    two fields called FirstName and LastName. I then want the code to open
    Outlook and create an new e-mail with the To: field to remain blank and the
    Subject: field to read Travel Request Form. I want the saved document to
    be an attachment to the e-mail so that all the user has to do is click Send
    and the form will go. I want the options in Outlook set to send a read
    receipt. The end result should be a document saved in the
    C:TravelRequests as BillSmith.doc (for example) and an e-mail created with
    the document as an attachment with the To: field blank and the Subject:
    field containing the words Travel Request Form.

    CommandButton2 - This button is labled E-mail and Print. I want to do the
    above AND have the document print out to my default printer.

    CommandButton3 - This button is labled Save and Print. I want to Save the
    form as described above AND have the document print out to my default
    printer.

    CommandButton4 - This button is labled Print. I want to have the document
    print out to my default printer.

    My VBA skills to achieve the above are basic at best. Does anyone know
    if it is possible to achieve the above, and how?
    Thanks in advance to all.

    Bill

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: VBA Code help (Word)

    Hi, Bill. How much time do you have to invest in learning some VBA? I guess this is two questions: (1) how soon is this due, and (2) how many hours can you put into it? This is a medium-sized project as automation projects go, but even though you could collect samples for all the parts from the Lounge in about an hour, without some basic background on how Office automation works, you'll be in the dark.

    There are recommendations for both books and <img src=/S/free.gif border=0 alt=free width=30 height=15> online resources on this board (<!post=and here,224994>and here<!/post>) and on our Books forum.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code help (Word)

    Thanks for the fast response. As for your questions:
    1. This is a fairly important project for me and I have to get it done quickly.
    2. In saying the above, I can devote a significant amount of time to it.
    I was hoping for some examples to be made available on the Lounge for me to cut and paste and modify where necessary. This will enable me to complete the project fairly quickly and learn a lot more about VBA in the process.
    I am actually in the process of doing some self learning on VBA but as I am currently located in China, the resources available for me to learn (books and courses) are not available in English.
    Any help that can be provided would be appreciated. Thanks again for being so quick.

    Bill

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: VBA Code help (Word)

    If you search for the following phrases, you will find sample code that might help. Note that the links will return more than you really want, but by browsing several posts, you will get ideas for refining the search.

    ActiveDocument.PrintOut

    Dialogs(wdDialogFilePrint)

    ActiveDocument.SaveAs

    Dialogs(wdDialogFileSaveAs)

    Outlook.Application

    Also, follow the link I gave above to those two sites. If you can; I don't know if Visual Basic is considered a threat to the revolution. <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

  5. #5
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: VBA Code help (Word)

    Bill

    The first thing to resolve is - what are you calling a 'form'. There are multiple possibilities:
    1. A document which looks like a printed form but is really an ordinary word document.
    2. A document which looks like a printed form and has Form Fields embedded within it and is meant to be completed with the other content locked.
    3. A user form (dialog box) which is presented to the user at some prompt which takes the user inputs and then processes that information.

    Option three is the trickiest to code but potentially the best solution. The basics of creating one of these is contained in this post MVPS Create a User Form .

    You can get a wealth of information here for the VBA parts of the job but you can easily record sections of the code to give you a great start as well.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  6. #6
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code help (Word)

    Andrew - thanks for the response. I am actually creating the form as you described in 2. A document which looks like a printed form and has Form Fields embedded within it and is meant to be completed with the other content locked.
    My form is designed and created, fields inserted and named and control buttons created. The form is ready for me to insert the correct code into VB when I can work it out.
    Any help appreciated.

    Bill

    P.S. I am a Melbournian (Richmond) living in China at the moment. You post has made me feel homesick!

  7. #7
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: VBA Code help (Word)

    Homesick ehh. Well I can't give you the answers tonight as I am off to Lygon Street for some pasta. Whoops there it goes again. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    Jefferson's posted links should give you just about all that you need then. If you cant work out any particular part, let us know and we can fill in the gaps.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  8. #8
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code help (Word)

    Andrew/Jefferson,
    Thanks for all of your help. The links you provided have greatly assisted me. I have one problem that I have been unable to resolve. I have partially written code that when a button in Word is clicked saves a .dot file to a .doc file in a certain directory. The code I have so far is:

    ActiveDocument.SaveAs FileName:="C:TravelRequest" & _
    ActiveDocument.FormFields("LastName").Result & _
    (ActiveDocument.FormFields("FirstName").Result) & Format _
    (Date, "yyyyMMdd") & Format(Time, "hhmm")

    This bit I have got right but what I want the code to do next is to open a new Outlook e-mail with the names in the field's FirstName and LastName in the Subject after the words "Travel Request - ". I want the saved .doc file to be an attachment to the e-mail and the To: field to be blank. The only Outlook option I want set to True is Read Receipt.
    Is what I am trying to achieve possible?
    Thanks heaps for your help.

    Bill

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Code help (Word)

    Here is a slightly modified version of a function I use. You need to add a reference to the Microsoft Outlook n Object Library (where n = 8.0 for Outlook 97, 8.5 for Outlook 98, 9.0 for Outlook 2000 and 10.0 for Outlook 2002). You can do this in the Visual Basic Editor by selecting Tools/References...

    The function takes 3 string arguments:
    Subject will become the subject of the e-mail
    Message will become the body of the e-mail
    Attachment is the full path and file name of the file you want to attach.
    All arguments can be a literal value or a variable or an expression.

    The function returns True if it succeeds, False if it fails.

    Function CreateMail( _
    Subject As String, _
    Message As String, _
    Attachment As String) As Boolean

    Dim objOL As Outlook.Application
    Dim objMI As Outlook.MailItem
    Dim blnNotActive As Boolean

    ' Check whether Outlook is active
    On Error Resume Next
    Set objOL = GetObject(, "Outlook.Application")
    blnNotActive = (Err <> 0)

    If blnNotActive Then
    ' If not, we start Outlook
    Err.Clear
    Set objOL = CreateObject("Outlook.Application")
    End If

    On Error GoTo Err_Mail

    ' Create e-mail message
    Set objMI = objOL.CreateItem(olMailItem)
    With objMI
    .Subject = Subject
    .Body = Message
    .Attachments.Add Attachment, olByValue
    .ReadReceiptRequested = True
    .Display
    End With
    CreateMail = True

    Exit_Mail:
    ' Release object memory
    Set objMI = Nothing
    Set objOL = Nothing
    Exit Function

    Err_Mail:
    CreateMail = False
    Resume Exit_Mail
    End Function

  10. #10
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: VBA Code help (Word)

    Bill

    I would recommend using some variables and functions as there is going to be a lot of duplication in your code without them.

    Hans has posted the basic code for your email attachment part as a function and another function will save the file.

    Do you know how to call a function (ie make it run)?
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  11. #11
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code help (Word)

    Andrew, no I don't know how to call a function. Could you post and example please. Hopefully I could work it out.

    Bill

  12. #12
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: VBA Code help (Word)

    Bill

    Try combining something like this with the code that Hans has provided. I have called two functions. One returns a value, the second doesn't return a value.

    By structuring your code in this way you can cut down on the repetition that you otherwise would have for your four buttons. The Subs should be hooked to the buttons and they set some parameters and then call the relevant functions which do the real work.
    <pre>Sub Button1Click()
    Dim strSubject As String
    Dim strMessage As String
    Dim strFile As String
    Dim strLastName As String
    Dim strFirstName As String
    Dim strFullPath as String

    strLastName = ActiveDocument.FormFields("LastName").Result
    strFirstName = ActiveDocument.FormFields("FirstName").Result
    strFile = strLastName & " " & strFirstName & Format(Time, "yyyyMMdd-hhmm")
    strSubject = "Your subject here"
    strMessage = "Your message here"

    strFullPath = funSave(strFile) 'Call function to save the file & return full path
    CreateMail strSubject, strMessage, strFullPath 'Call the function to create email
    End Sub
    Function funSave(strFile As String) As String
    funSave = "C:TravelRequest" & strFile
    ActiveDocument.SaveAs FileName:=funSave
    End Function</pre>

    Andrew Lockton, Chrysalis Design, Melbourne Australia

  13. #13
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code help (Word)

    Andrew - Thanks heaps for your help. However I fear that I have bitten off for more than I can chew! I am starting to get a little confused. When you say that I should combine the code you provided with the code that Hans provided, where to I combine the two?
    My VBA skills are very basic at best unfortunately. Sorry.

    Bill

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Code help (Word)

    Bill,

    Here are step-by-step instructions. To write or read them actually takes longer than doing it...

    With your .dot open, switch to the Visual Basic Editor (Alt+F11)
    Insert a module (Insert/Module)
    Switch back to Word and create a blank new document.
    Switch to the browser with this thread open, preferably in flat mode, so that you can see all replies.
    Select Andrew's code and copy it to the clipboard (Ctrl+C)
    Switch to Word.
    Paste into the blank document (Ctrl+V)
    With the code selected, copy it to the clipboard (Ctrl+C) This may seem superfluous, but it is necessary because of the way Andrew has formatted his code.
    Switch to the Visual Basic Editor (Alt+F11)
    Paste into the module (Ctrl+V)
    Switch to the browser.
    Copy my code (the CreateMail function) to the clipboard (Ctrl+C)
    Switch to the Visual Basic Editor.
    Position the insertion point below Andrew's code.
    Paste (Ctrl+V). I have formatted my code differently from Andrew's; you should be able to paste it directly into the Visual Basic Editor.
    Switch back to Word.
    Make sure that you are in design mode (there is a Design Mode button on the Visual Basic toolbar and also on the Control Toolbox toolbar)
    Right-click the first of your command buttons.
    Select View Program Code.
    Insert Button1Click.
    Switch back to Word.
    Turn Design Mode off.

  15. #15
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code help (Word)

    Hans/Andrew - Thanks heaps for your help on this one. I have finally got things working OK thanks to you. I am now getting the VBA bug! Have to start another (smaller) project which I am about to post another question on. Thanks again.

    Bill

Posting Permissions

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