Word Macro - how to call an outlook template and merge with word document.

Hi I have a word macro which basically runs a batch scripts which creates a text data file which then merges with a word document on the network. Works great. The issue I now have is I need it to call an outlook .oft file which contains the word merge document as an attachment. I would like the macro to call the .oft file and then place that data in to the attachment ready for the email to be sent. Any help would be great.

 macros below

If MsgBox("Merge Bod Contracts?", vbYesNo) = vbYes Then
        Set doc = Documents.Open("P:\Letters\Start Letters\Paye new starter forms part 1.doc")
        With doc.MailMerge
            .OpenDataSource Name:="F:\INTECH\BODS\BSPlet.TXT"
            .Destination = wdSendToNewDocument
            .Execute
        End With
        doc.Close wdDoNotSaveChanges
    End If
End Sub
Sub Client Contract Extension ()
    Dim retval As Long
    Dim doc As Document
    
    retval = ExecCmd("F:\INTECH\BODS\BC S EXT.bat")
    
    If MsgBox("Merge Bod Contracts?", vbYesNo) = vbYes Then
        Set doc = Documents.Open("P:\Letters\Start Letters\client contract extension (ext).doc")
        With doc.MailMerge
            .OpenDataSource Name:="F:\INTECH\BODS\extlet.TXT"
            .Destination = wdSendToNewDocument
            .Execute
        End With
        doc.Close wdDoNotSaveChanges
    End If
End Sub
Sub Contractor Contract Extension ()
    Dim retval As Long
    Dim doc As Document
    
    retval = ExecCmd("F:\INTECH\BODS\BC S EXT.bat")
    
    If MsgBox("Merge Bod Contracts?", vbYesNo) = vbYes Then
        Set doc = Documents.Open("P:\Letters\Start Letters\contractor contract extension (ext).doc")
        With doc.MailMerge
            .OpenDataSource Name:="F:\INTECH\BODS\extlet.TXT"
            .Destination = wdSendToNewDocument
            .Execute
        End With
        doc.Close wdDoNotSaveChanges
    End If
End Sub

Open in new window

Ben CampbellIT User, Manager, DiplomatAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GrahamSkanRetiredCommented:
It's not clear what you are trying to achieve.
You show three macros, each of which will produce a document comprising data from each record. Do you want to attach each document to the mail message?
0
Ben CampbellIT User, Manager, DiplomatAuthor Commented:
Hi Graham

Sorry in my haste I pasted the wrong part of the macro. Updated below. The macro currently opens a word document and merges the word doc with txt from a file we create from our system which works absolutely fine.

However now the client wants the macro to call an outlook.oft file which already has the word form attached. The macro needs to open the .oft file and then use the attached word form to merge the data in to before sending the email

Does that make any more sense ?

Option Explicit

Private Type STARTUPINFO
    cb As Long
    lpReserved As String
    lpDesktop As String
    lpTitle As String
    dwX As Long
    dwY As Long
    dwXSize As Long
    dwYSize As Long
    dwXCountChars As Long
    dwYCountChars As Long
    dwFillAttribute As Long
    dwFlags As Long
    wShowWindow As Integer
    cbReserved2 As Integer
    lpReserved2 As Long
    hStdInput As Long
    hStdOutput As Long
    hStdError As Long
End Type

Private Type PROCESS_INFORMATION
    hProcess As Long
    hThread As Long
    dwProcessID As Long
    dwThreadID As Long
End Type

Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal _
   hHandle As Long, ByVal dwMilliseconds As Long) As Long

Private Declare Function CreateProcessA Lib "kernel32" (ByVal _
   lpApplicationName As String, ByVal lpCommandLine As String, ByVal _
   lpProcessAttributes As Long, ByVal lpThreadAttributes As Long, _
   ByVal bInheritHandles As Long, ByVal dwCreationFlags As Long, _
   ByVal lpEnvironment As Long, ByVal lpCurrentDirectory As String, _
   lpStartupInfo As STARTUPINFO, lpProcessInformation As _
   PROCESS_INFORMATION) As Long

Private Declare Function CloseHandle Lib "kernel32" _
   (ByVal hObject As Long) As Long

Private Declare Function GetExitCodeProcess Lib "kernel32" _
   (ByVal hProcess As Long, lpExitCode As Long) As Long

Private Const NORMAL_PRIORITY_CLASS = &H20&
Private Const INFINITE = -1&

Public Function ExecCmd(cmdline$) As Long
    Dim proc As PROCESS_INFORMATION
    Dim start As STARTUPINFO
    Dim ret&
   
    ' Initialize the STARTUPINFO structure:
    start.cb = Len(start)
   
    ' Start the shelled application:
    ret& = CreateProcessA(vbNullString, cmdline$, 0&, 0&, 1&, _
    NORMAL_PRIORITY_CLASS, 0&, vbNullString, start, proc)
   
    ' Wait for the shelled application to finish:
    ret& = WaitForSingleObject(proc.hProcess, INFINITE)
    Call GetExitCodeProcess(proc.hProcess, ret&)
    Call CloseHandle(proc.hThread)
    Call CloseHandle(proc.hProcess)
    ExecCmd = ret&
End Function

Sub LTD Client LTD OPT OUT()
    Dim retval As Long
    Dim doc As Document
   
    retval = ExecCmd("F:\INTECH\BODS\BC S BST.bat")
   
    If MsgBox("Merge Bod Contracts?", vbYesNo) = vbYes Then
        Set doc = Documents.Open("P:\Letters\Start Letters\client ltd OPT OUT letter and assignment details (3).doc")
        With doc.MailMerge
            .OpenDataSource Name:="F:\INTECH\BODS\BSTlet.TXT"
            .Destination = wdSendToNewDocument
            .Execute
        End With
        doc.Close wdDoNotSaveChanges
    End If
End Sub

thanks

Jon
0
GrahamSkanRetiredCommented:
I think it makes sense semantically, but not technically.

A mail merge main document acts like template in that it is unchanged during the mail excursion process. If the destination for the merge output is a document, it will be a separate one from the main document, so the fact that the main document is already attached to a message template is less than helpful.

Mail merge output can be directed to email, so the natural thing to do would be to move the text in the .oft to the main document and run the merge with the destination set to email.
1
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Ben CampbellIT User, Manager, DiplomatAuthor Commented:
Graham thanks for your help and apologies for the delay.

I have changed my approach and now the macro just needs a little tweaking and I wonder if you can help. Rather than save the merge data to a new document I would like to save it in the current one, so would need to amend the lines below  wouldn't i ? What do I need to change these  lines to, to save it to the current document and not a new one ? Thanks Jon

.Destination = wdSendToNewDocument
.Execute
        End With
        doc.Close wdDoNotSaveChanges


Full macro below -

Sub LTD Client LTD OPT OUT()
    Dim retval As Long
    Dim doc As Document
   
    retval = ExecCmd("F:\INTECH\BODS\BC S BST.bat")
   
    If MsgBox("Merge Bod Contracts?", vbYesNo) = vbYes Then
        Set doc = Documents.Open("P:\Letters\Start Letters\client ltd OPT OUT letter and assignment details (3).doc")
        With doc.MailMerge
            .OpenDataSource Name:="F:\INTECH\BODS\BSTlet.TXT"
            .Destination = wdSendToNewDocument
            .Execute
        End With
        doc.Close wdDoNotSaveChanges
    End If
End Sub
0
GrahamSkanRetiredCommented:
I'm not sure that I understand the question.
wdSendToNewDocument tell the merge to create a new document in memory with a section for each record in the Datasource. Other options are to print the output directly, to create and send emails, or to create faxes.

You could overwrite the main document, but that would be unusual and suggests a misunderstanding of the merge process.

Sub LTD_Client_LTD_OPT_OUT()
    Dim retval As Long
    Dim docMain As Document
    Dim docResult As Document
    Dim strMainDocName As String
    
    strMainDocName = "P:\Letters\Start Letters\client ltd OPT OUT letter and assignment details (3).doc"
    retval = ExecCmd("F:\INTECH\BODS\BC S BST.bat")
   
    If MsgBox("Merge Bod Contracts?", vbYesNo) = vbYes Then
        Set docMain = Documents.Open(strMainDocName)
        With docMain.MailMerge
            .OpenDataSource Name:="F:\INTECH\BODS\BSTlet.TXT"
            .Destination = wdSendToNewDocument
            .Execute
        End With
        Set docResult = ActiveDocument
        docMain.Close wdDoNotSaveChanges
        docResult.SaveAs strMainDocName
    End If
End Sub

Open in new window

1
Ben CampbellIT User, Manager, DiplomatAuthor Commented:
Thanks Graham

I'm trying to find a work around for my client. They open an outlook .oft file which is an email template which contains and attachment. When you open the attachment from the email we are now running the macro to populate the field contents of the word doc. So the macro runs fine and populates the fields but on a new document. We want it to populate the word document on the mail template so they can then literally hit save and then send the email. Perhaps it is my lack of understanding, perhaps I'm think of how I want it to work but its not possible. Appreciate your help.
0
GrahamSkanRetiredCommented:
The mail mere process treats the main document in a template-like manner in that the process doesn't change the main document, but produces an output of some kind. It can be to emails,  faxes, direct to the printer or to a new document.

In theory it would be possible to use VBA to replace the MailMerge fields with data from one of the records in the datasource, but it could get quite complicated.

I think that it should be easier to remove the attachment and the mail merge results document be attached instead.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Word

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.