Consulting

Results 1 to 6 of 6

Thread: Word doc with user form, create/send email from specific account

  1. #1
    VBAX Regular
    Joined
    Mar 2012
    Posts
    12
    Location

    Word doc with user form, create/send email from specific account

    Hello all,
    I have a MS Word 2013 document/template with vba that is displaying a user form at start up. Based on user selections, data from the user form is pushed to the Word doc via bookmarks. The user form is basically asking delivery preference. If postal mail is selected the user form displays fields for mailing information, if email is selected, the user form displays email information. Once the submit button is clicked the code populates the applicable bookmarks and if email deletes unwanted bookmarks, populates other bookmarks and deletes a table (only table on form), user form disappears. Word doc is locked so there is also code that is unlocking to update the bookmarks then re-locking the doc. I have this working.

    When postal mail was the selection, the user just prints the doc to PDF and routes to an established process for mailing. My problem and questions are when the selection was email. I need to generate an email (not as attachment) with the contents that are now displaying in the Word doc. I was thinking of use the Word option of mail recipient. I have added that "button" to my tool bar.

    Problem 1: How to make the mail recipient button always available to this form. Right now the button shows since I added it but if someone else opens the doc it will not display.
    Problem 2: I need the email to send from another email account and NOT from the users email account, both are Outlook accounts.
    Problem 3: I want to pre-fill the subject line with specific text. Currently it adds the Word doc file name.

    Glad to show my code if needed. Thank you so much for any direction.

  2. #2
    You cannot make a button in an document that you are using as the body of an e-mail message work as you cannot include the code to make it do so. You need instead a hyperlink, linked back to you.

    The other two items are covered in the following code - note that you will need to download and implement the code indicated at the top of the macro or it won't work. Remove the apostrophe from the Send line when you are happy it works

    Sub Send_As_HTML_EMail()
    'Graham Mayor - http://www.gmayor.com - Last updated - 14 Jul 2017
    'Requires the code from http://www.rondebruin.nl/win/s1/outlook/openclose.htm
    'to either retrieve an open instance of Outlook or open Outlook if it is closed.
    Dim bStarted As Boolean
    Dim olApp As Object
    Dim oItem As Object
    Dim oAccount As Object
    Dim objDoc As Object
    Dim objSel As Selection
    Dim strRecipient As String
    Dim strSubject As String
    Dim strAccount As String
    
        strRecipient = "someone@somewhere.com"
        strSubject = "This is the message subject"
        strAccount = "account display name"
    
        On Error Resume Next
        ActiveDocument.Range.Copy
        Set olApp = OutlookApp()
    
        For Each oAccount In olApp.Session.Accounts
            If oAccount.DisplayName = strAccount Then
                Set oItem = olApp.CreateItem(0)
                With oItem
                    Set .SendUsingAccount = oAccount
                    .BodyFormat = 2
                    .Display
                    Set objDoc = .GetInspector.WordEditor
                    Set objSel = objDoc.Windows(1).Selection
                    objSel.PasteAndFormat Type:=wdFormatOriginalFormatting
                    .To = strRecipient
                    .Subject = strSubject
                    '.Send
                End With
                Exit For
            End If
        Next oAccount
    lbl_Exit:
        Set oItem = Nothing
        Set oAccount = Nothing
        Set olApp = Nothing
        Set objDoc = Nothing
        Set objSel = Nothing
        Exit Sub
    End Sub
    Graham Mayor - MS MVP (Word)
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  3. #3
    VBAX Regular
    Joined
    Mar 2012
    Posts
    12
    Location
    Thanks Graham for the response. I will review your code for my problems 2&3.
    I want to make sure I didn't word my problem #1 poorly.

    "Problem 1: How to make the mail recipient button always available to this form. Right now the button shows since I added it but if someone else opens the doc it will not display. "

    The button I am referring to is one I added to the ribbon in Word. The button is available in the Commands not in the ribbon. Can't I through VBA code add that to the ribbon anytime this form is opened?
    Thanks very much.

  4. #4
    You appear to be adding the button to the ribbon in the normal template. To get it to be present while you have the document open in Word, you need to add it to the document's ribbon - http://gregmaxey.mvps.org/word_tip_p...bbon_main.html
    Graham Mayor - MS MVP (Word)
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  5. #5
    VBAX Regular
    Joined
    Mar 2012
    Posts
    12
    Location
    Graham, I have been reviewing the information you provided to edit the document's ribbon and if I understand correctly I would still have to add a file to each users startup folder. I have hundreds of users and that is just not practical, can you offer another solution? Thanks.

  6. #6
    You don't need to add anything to the startup folder(s). The modification goes to the DOCUMENT's ribbon. If a user opens that document they get the ribbon. However the document you sent me privately is Word 97-2003 format and that is not XML compatible. You would need it to be a DOTM format template that contains both the ribbon changes AND the macro. Users would create new documents from the template - and in any case a button is not necessary. You can create the e-mail directly from your userform.
    Graham Mayor - MS MVP (Word)
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

Posting Permissions

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