SOLVED Help needed insert stock reply from template or Word

[Labrat407]

Our work area has 2 different workstreams, for my current area I have created a button on the Ribon that opens a User form with several tabs and a few buttons to populate our replies to the client with the Subject and email address customized and all attachments. The Body of the replies are hard coded in the macro as strings. Since the replies are standard and do not change this works well.

For the other group the number and variety of replies makes the hard coding in a macro not the best possible option, as the replies change frequently.
We looked at using Quick Parts, but again it is unwieldy with so many options, text updating is also time consuming. I did not have very good sucess importing Quick Parts.
The best options that I have thought about is either have the data pulled from our master Word document or from a oft template file for each option. this would make the updating easier.
The ability to keep the format of imported text is important.

I am able to do most of the coding for the basics, I am looking for suggestions on how to best accomplish the import. I would also consider options to build a macro on Word to export a section of text (possibly by table of contents heading) into the currently active email.

Thanks in advance for any assistance.

[HansV]

Welcome to Eileen's Lounge!

Your question is rather broad. I think you'll have a better chance of a helpful reply if you could narrow the focus and start with a smaller, more specific question.

[Labrat407]

I am looking to create a macro that calls up a User form that is on the open emails ribbon. from the User form the operator will be able to select different options like reply language, and the type of generic reply based on the review of the email by the client.

The current replies are saved as a word document that has a Table of Contents dividing each reply. Our current process is to cut and paste from this document to reply to a client. We also have an initial ticket creation email saved as a template file for Outlook that we open fill out and then send.

Every step of the process has an email that is either replied with attachments to the client with more detail, or is forwarded to another party with attachments. information is added either by order numbers or reference ID to help coordinate the work flow for all sections impacted.

What I have done for my current section is to hard code the replies into the macro code, this works as we do not change it that often. Our other section would like something similar but due to the sheer number and how often the changes occur it is not the best option to hard code.

As long as I have an idea how to get the project started I can do most of the coding based on my current macros.

The issue I have is how to call the formatted blocks of text into the email. They must be editable outside of the macro, (either word or email template). They must be from a single shared source not locally saved. There should be an option to insert variables into the text (not a must have but nice to have)

I have tried using excel but it does not have the formatting options.
I have tried using the Quick parts but it was unwieldy with so many options.
Hard coding is too labour intensive and if I am not here it will not get done.

At this point I can not be as specific as I wish as I do not know the best method to meet the needs. Any suggestions would be helpful on the overall method. As I work to get the script together I will be able to ask or find specific code for the macros.
I hope this helps.

[HansV]

The following is not a complete answer. It is only intended to give you an idea; it will have to be expanded. It uses a Word document in which each 'reply' has been assigned a bookmark.

The macro, to be run from Outlook, automates Word using late binding. It doesn't need a reference to the Microsoft Word object library.
The code opens a document \\Server\Share\Folder\Temp.docx and copies the contents of a bookmark named Par2 into the active e-mail message. You should modify this to suit your situation, of course, and make the name of the bookmark a variable depending on the choices in your userform.

Code: Select all

Sub InsertSomeText()
    Dim app As Object
    Dim docCur As Object
    Dim docExt As Object
    Dim blnStart As Boolean

    If TypeName(ActiveWindow) <> "Inspector" Then
        MsgBox "This code only works from an open item.", vbExclamation
        Exit Sub
    End If
    If ActiveInspector.CurrentItem.Class <> olMail Then
        MsgBox "This code only works for e-mail items.", vbExclamation
        Exit Sub
    End If

    On Error Resume Next
    Set app = GetObject(Class:="Word.Application")
    If app Is Nothing Then
        Set app = CreateObject(Class:="Word.Application")
        If app Is Nothing Then
            MsgBox "Can't start Word!", vbCritical
            Exit Sub
        End If
        blnStart = True
    End If

    On Error GoTo ErrHandler
    Set docExt = app.Documents.Open("\\Server\Share\Folder\Temp.docx")
    Set docCur = ActiveInspector.WordEditor
    docExt.Bookmarks("Par2").Range.Copy
    docCur.Content.Paste

ExitHandler:
    On Error Resume Next
    docExt.Close SaveChanges:=False
    Set docExt = Nothing
    Set docCur = Nothing
    If blnStart Then
        app.Quit SaveChanges:=False
    End If
    Set app = Nothing
    Exit Sub

ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
End Sub

I tested this code, and it copied the text of the bookmark into the e-mail message, preserving character formatting.

[Labrat407]

Thanks, I will test and see if this solution works. I have not heard of using bookmarks, something new to learn.

[HansV]

See Add or delete bookmarks if you want to learn about bookmarks.

[Labrat407]

The Macro works great , almost perfectly. The only issue is that the Bookmark pastes into the email (Rich Text and HTML) ir pastes over the entire email instead of inserting it to the start of the email. Is there a change I can make to the code to insert instead of paste?

Code: Select all

docCur.Content.Paste

Thanks for your help.

[HansV]

Try this version:

Code: Select all

Sub InsertSomeText()
    Dim app As Object
    Dim docCur As Object
    Dim docExt As Object
    Dim blnStart As Boolean
    Dim rng As Object

    If TypeName(ActiveWindow) <> "Inspector" Then
        MsgBox "This code only works from an open item.", vbExclamation
        Exit Sub
    End If
    If ActiveInspector.CurrentItem.Class <> olMail Then
        MsgBox "This code only works for e-mail items.", vbExclamation
        Exit Sub
    End If

    On Error Resume Next
    Set app = GetObject(Class:="Word.Application")
    If app Is Nothing Then
        Set app = CreateObject(Class:="Word.Application")
        If app Is Nothing Then
            MsgBox "Can't start Word!", vbCritical
            Exit Sub
        End If
        blnStart = True
    End If

    On Error GoTo ErrHandler
    Set docExt = app.Documents.Open("\\Server\Share\Folder\Temp.docx")
    Set docCur = ActiveInspector.WordEditor
    docExt.Bookmarks("Par2").Range.Copy
    ' *** Modified code starts here ***
    docCur.Content.InsertParagraphAfter
    Set rng = docCur.Content
    rng.Collapse Direction:=0
    rng.Paste
    ' *** End of modified code ***

ExitHandler:
    On Error Resume Next
    docExt.Close SaveChanges:=False
    Set docExt = Nothing
    Set docCur = Nothing
    If blnStart Then
        app.Quit SaveChanges:=False
    End If
    Set app = Nothing
    Exit Sub

ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
End Sub

[Labrat407]

That works better, it inserts at the end of the email not the top.

[HansV]

If you want the text to be inserted at the top:

Code: Select all

Sub InsertSomeText()
    Dim app As Object
    Dim docCur As Object
    Dim docExt As Object
    Dim blnStart As Boolean
    Dim rng As Object

    If TypeName(ActiveWindow) <> "Inspector" Then
        MsgBox "This code only works from an open item.", vbExclamation
        Exit Sub
    End If
    If ActiveInspector.CurrentItem.Class <> olMail Then
        MsgBox "This code only works for e-mail items.", vbExclamation
        Exit Sub
    End If

    On Error Resume Next
    Set app = GetObject(Class:="Word.Application")
    If app Is Nothing Then
        Set app = CreateObject(Class:="Word.Application")
        If app Is Nothing Then
            MsgBox "Can't start Word!", vbCritical
            Exit Sub
        End If
        blnStart = True
    End If

    On Error GoTo ErrHandler
    Set docExt = app.Documents.Open("\\Server\Share\Folder\Temp.docx")
    Set docCur = ActiveInspector.WordEditor
    docExt.Bookmarks("Par2").Range.Copy
    ' *** Modified code starts here ***
    Set rng = docCur.Content
    rng.Collapse Direction:=1
    rng.Paste
    rng.InsertParagraphAfter
    ' *** End of modified code ***

ExitHandler:
    On Error Resume Next
    docExt.Close SaveChanges:=False
    Set docExt = Nothing
    Set docCur = Nothing
    If blnStart Then
        app.Quit SaveChanges:=False
    End If
    Set app = Nothing
    Exit Sub

ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
End Sub

[Labrat407]

Brilliant! Thank so much for your help. I will be able to bang out the User form now with no issues (crosses fingers)