Outlook Mailmerge using VBA

Hi

I have tried posting this at various other outlook forums, but never had a single reply (maybe that says more about my Q than the people in the forums.

I am trying to produce a macro with VBA in Outlook (to assign to an outlook toolbar) to do the following: -

when outlook is in the contacts view, to mailmerge the highlighted
contact only, with a known word mailmerge document. At the moment I do this manually and it obviously works fine. Just wanted some VBA code to do
this automatically so I can assign it to a toolbar button. Have bought a couple of books that have been no good, and searched and searched for an answer.

I have done a little VBA before, but having searched around I am a
bit lost on this. Or maybe it cant be done...

Any help very greatfully received.

There are a couple of things that you will come up against, but this is do-able.

You could write some VBA to start the mailmerge wizard and try
to populate the parameters. Although this may bring up the
Outlook Security box informing you that a program is trying to
access your Outlook.

Give me some time to write an example.

Thanks for replying at last a response, to correct the above I ahve now had 2 other responses, both saying "when you find out can you let me know":rolleyes:

VBA help for Outlook is really scant out there, unless you want to mass email.

Thanks

Almost done!
I just need to know what type of merge you want to do...

1.) Form Letters
2.) Mailing Labels
3.) Envelopes
4.) Catalog

Also, what version of Outlook are you running?

There is no direct access to the mailmerge process, so I hope
what I have done so far is ok. I added a new menu item that is
only enabled if there is a Contact Item selected. it then takes the
selection and automates the mailmerge wizard. One requirement
is that your Outlook and Word versions must be the same.

I also need to know before I can finish is if this is going into an
existing Word doc or a new one and the type of merge as I
posted in the preceding post.

Let me know when you get the change.

Everything is done but this part.

Quote:

---

1.) Form Letters
2.) Mailing Labels
3.) Envelopes
4.) Catalog

Also, what version of Outlook are you running?

---

and merge to new document or existing one?

???

Hi Rob

Sorry for not getting back earlier, I have been out of the loop for a few days. Answering your posts in order: -

The type of Merge is form letter, BTW the Contact form I merge from has loads of custom fields on it, dont know whether this will make a difference with your code.

I am using Outlook 2003 11.5608.5703.

This is going into an existing merge ( 1 of 5 standard files we can use depending on the contacts requirements)

I cant believe you have moved so fast on this, I have had 3 months of returning back to forums seeing ) Replies to this.

Many Many Thanks

The first thing I see it the need for determining which mailmerge
document to use. What are the conditions that determine which
mailmerge document to use?

I don't know off hand if the custom contact form will cause an
issue, but what I am doing is automating the Mail Merge wizard.
So, if it works with your custom form manually then it should be
ok.

BTW, there is no programatic access to the mail merge wizard,
but this could be simulated using the Word Object Model. It is
just more time consuming writting more code. This is why I opted
to automate the wizard.

The version of Word, I assume, is the same as your Outlook?
If its different then it needs to be upgraded. We are running the
same version of Outlook so that is a plus.

The last thing is that I forgot to send my updated VBA code back
to work last night, so I will try to complete it here at work from
memory.

If using different mailmerge documents becomes an issue to
determine, then I will write the code to manually duplicate the
wizard, but I would like to avoid that. There is a saying that - if it
aint broke, then don't fix it. So if the wizard can be used then I
will use it.

PS. don't worry about the delay, it just gave me more time to
finish this. It actually works great on new blank mailmerge
documents. I have it so the menu item is disabled unless a
contact item is selected. And if more than one item is selected
then it is disabled too. There is only a problem when it comes to
entering the existing word document into the wizard because
Outlook actually opens it for verification. I am still looking into
working around this. Probably not too hard.

:)

Hi Rob

Thanks for the update.

Firstly it is difficult to programatically work out which of the standard letters should be used, would it be possible to show a menu / toolbar button for all of the letters when a contact is highlighted ?

The version of word is the same 11.0 (2003).

I see what you say about verification of existing mailmerge document, because it does that when we do it manually.

Many months ago I thought this would be so easy to do with VBA. Many postings and no replies later I now realise that it is not as easy as I thought. Many of the top gurus out there who will remain nameless shyed away from it.

Many thanks for your continued enthusiasm.

Would it help you if I emailed the Custom Form we use and the 5 mailmerge docs to you?

One last thought the pst we use is legacy 97-2002 not 2003 format, does this present a problem. I guess if it does I can do an Export Import to update this.

All the best for Easter to you and yours.

Yes it would help to have the form and the 5 mm docs. I was
thinking of adding to the SelectionChange event to read the
custom field that contained the mm document name, I assume
that it is in your custom form. Then when the menu item is clicked
I have all the info I need to populate the wizard.

Your email is dissabled at this forum if you want to keep it hidden send me a mail at [email protected] and I will forward you the MM dics and custom form.

The Mailmerge doc is not encoded into the custom form anywere as there can be a number of different instances of letters sent to the same contact

It is basically a booking system we run for Pilates Classes, taking initial a contacts details and then sending either a booking confirmation letter, a beginners workshop letter, a One to One session letter, a request for a completed health questionnaire....etc

I never make things simple.

So basically you make a cognative decision of what document to
send the contact? So, if that is true then maybe I can add menu
items for each type of document? main menu of "Mail Merge
Single Contact" and have sub menu items of the five different
document types? This way the user will select the contact and
then go to the menu and select the document type to use?
Sounds ok to me. How about you?

Sounds Bloody Brilliant to me ...

Ok, menus are all done. I made it so each menu item passes a
parameter to a .exe watcher program I wrote to watch for the
Mail Merge Wizard. This needs to be this way because the wizard
is modal and once the wizard came up the code would be halted.
When the wizard appears the watcher automates it and places
the document name in the appropriate textbox. I also added a
menu item if you want it to go to a new blank word document.
I have some more testing to do on verifiying the existing word
document, but I know I can get it to work.

Can't wait for you to try it out!

I will probably be posting the completed projects tomorrow morning.

Don't forget that when I get those files I will still have to see if it
works with the custom form and docs.

Later.

Hi Rob

I sent you the files yesterday did you not receive them.?

Cheers

I just got the new attachment.
I get this error message when I try to open the form in Outlook.

http://www.vbforums.com/attachment.p...postid=1667322

And then my Outlook crashes. :confused:

Also, there are now 7 mm documents, so I assume that I will
make adjustments to the vba to add the seventh option.

Ok, I was able to get the form published and installed, but if I go
into design mode the form looks corrupted or something?

Also, I don't know if its because some of the code has been
disabled from running, I don't know if the issues are from that, but
it looks like it is not picking up the custome fields in the mail
merge. I think its because of the custom fields are bould to the
form and not defined in the folder. Again this may not be an issue
for yours since it is running all code.

I will be posting the code in a few after I make a couple of changes.

Aha, I just remembered it looks corrupted coz there is an ie6 object sitting over the top which in run mode is invisible untill I want to show a map to the contacts address.

Just delete the ie6 object which should fill the whole of the form on the upmost layer.

Sorry bout there being 7 ..... and sorry bout crashing your outlook, there is no malicious code in there, just my badly coded VBscript...LOL

Here is the code to add the menu items and detect the selection
type and count. It will disable the menu items if the selection is
not a contact item type or if more than one item is selected. I
added one main menu item for the single mailmerge with a
description of the item selected. Then I add eight sub menu
items. One for merge to a new document and seven more for
each pre-existing mailmerge document.

The menu items shell out a watcher program I wrote to automate
the Mail merge wizard with the predefined settings you
requested. The shell passes a file path and document name
parameter to the watcher, thus giving it all the info itneeds to
automate the wizard. the reason I have to use a watcher
program is that when the wizard gets invoked it is modal and all
code execution is halted until it is closed. The watcher program is
shelled out one line of code before the wizard is invoked.

I think there is an issue with the custom fields you have on your
custom form because yoyu have created the fileds to be saved in
the item and not the folder. So the mailmerge doesn't see the
custom fields. I couldn't get your form to run correctly so I can not
verify that this will be the case.

Let me know what you think.

Happy mailmerging!

Code on following posts.

Change the document paths to reflect your paths.
Copy and paste into your "ThisOutlookSession" class.

Outlook VBA Code...

VB Code:

Option Explicit
 
Public WithEvents SyncSMM1 As Office.CommandBarButton
Public WithEvents SyncSMM2 As Office.CommandBarButton
Public WithEvents SyncSMM3 As Office.CommandBarButton
Public WithEvents SyncSMM4 As Office.CommandBarButton
Public WithEvents SyncSMM5 As Office.CommandBarButton
Public WithEvents SyncSMM6 As Office.CommandBarButton
Public WithEvents SyncSMM7 As Office.CommandBarButton
Public WithEvents SyncSMMNew As Office.CommandBarButton
 
Public gcbTools As Office.CommandBarPopup
Public gcbMM As Office.CommandBarButton
Public gcbSMM As Office.CommandBarPopup
Public gcbSMM1 As Office.CommandBarButton
Public gcbSMM2 As Office.CommandBarButton
Public gcbSMM3 As Office.CommandBarButton
Public gcbSMM4 As Office.CommandBarButton
Public gcbSMM5 As Office.CommandBarButton
Public gcbSMM6 As Office.CommandBarButton
Public gcbSMM7 As Office.CommandBarButton
Public gcbSMMNew As Office.CommandBarButton
 
Public WithEvents goExp As Outlook.Explorer
 
'SETUP THE MENUS
Private Sub Application_MAPILogonComplete()
'<RR VB/OUTLOOK GURU 04/05/04>
    Set gcbTools = Application.ActiveExplorer.CommandBars("Menu Bar").Controls("Tools")
'    gcbTools.Reset
    'ADD THE MAIN MENU ITEM FOR THE NEW MAIL MERGE
    Set gcbSMM = gcbTools.Controls.Add(msoControlPopup, 1, "SMM", , True)
    With gcbSMM
        .BeginGroup = True
        .Caption = "Single Mail Merge of Contact Item"
        .Visible = True
        .DescriptionText = "Execute a Mail Merge on a Contact Item"
        .TooltipText = "Execute a Mail Merge on a Contact Item"
        .Tag = "888"
        .Enabled = True
        .Style = msoControlCustom
    End With
    'ADD THE NEW SUB MENU ITEM
    Set gcbSMMNew = gcbSMM.Controls.Add(msoControlButton, 1, "SMMNew", , True)
    With gcbSMMNew
        .Caption = "Mail Merge Using a New Document"
        .Visible = True
        .DescriptionText = "Execute a Mail Merge on a Contact Item"
        .TooltipText = "Execute a Mail Merge on a Contact Item"
        .Tag = "880"
        .Enabled = True
        .Style = msoControlCustom
    End With
    Call SyncSMMNewButton(gcbSMMNew)
    'ADD THE FIRST SUB MENU ITEM
    Set gcbSMM1 = gcbSMM.Controls.Add(msoControlButton, 1, "SMM1", , True)
    With gcbSMM1
        .BeginGroup = True
        .Caption = "Mail Merge Using Doc1.doc"
        .Visible = True
        .DescriptionText = "Execute a Mail Merge on a Contact Item"
        .TooltipText = "Execute a Mail Merge on a Contact Item"
        .Tag = "881"
        .Enabled = True
        .Style = msoControlCustom
    End With
    Call SyncSMM1Button(gcbSMM1)
    'ADD THE SECOND SUB MENU ITEM
    Set gcbSMM2 = gcbSMM.Controls.Add(msoControlButton, 1, "SMM2", , True)
    With gcbSMM2
        .Caption = "Mail Merge Using Doc2.doc"
        .Visible = True
        .DescriptionText = "Execute a Mail Merge on a Contact Item"
        .TooltipText = "Execute a Mail Merge on a Contact Item"
        .Tag = "882"
        .Enabled = True
        .Style = msoControlCustom
    End With
    Call SyncSMM2Button(gcbSMM2)
    'ADD THE THIRD SUB MENU ITEM
    Set gcbSMM3 = gcbSMM.Controls.Add(msoControlButton, 1, "SMM3", , True)
    With gcbSMM3
        .Caption = "Mail Merge Using Doc3.doc"
        .Visible = True
        .DescriptionText = "Execute a Mail Merge on a Contact Item"
        .TooltipText = "Execute a Mail Merge on a Contact Item"
        .Tag = "883"
        .Enabled = True
        .Style = msoControlCustom
    End With
    Call SyncSMM3Button(gcbSMM3)
    'ADD THE FOURTH SUB MENU ITEM
    Set gcbSMM4 = gcbSMM.Controls.Add(msoControlButton, 1, "SMM4", , True)
    With gcbSMM4
        .Caption = "Mail Merge Using Doc4.doc"
        .Visible = True
        .DescriptionText = "Execute a Mail Merge on a Contact Item"
        .TooltipText = "Execute a Mail Merge on a Contact Item"
        .Tag = "884"
        .Enabled = True
        .Style = msoControlCustom
    End With
    Call SyncSMM4Button(gcbSMM4)
    'ADD THE FIFTH SUB MENU ITEM
    Set gcbSMM5 = gcbSMM.Controls.Add(msoControlButton, 1, "SMM5", , True)
    With gcbSMM5
        .Caption = "Mail Merge Using Doc5.doc"
        .Visible = True
        .DescriptionText = "Execute a Mail Merge on a Contact Item"
        .TooltipText = "Execute a Mail Merge on a Contact Item"
        .Tag = "885"
        .Enabled = True
        .Style = msoControlCustom
    End With
    Call SyncSMM5Button(gcbSMM5)
    'ADD THE SIXTH SUB MENU ITEM
    Set gcbSMM6 = gcbSMM.Controls.Add(msoControlButton, 1, "SMM6", , True)
    With gcbSMM6
        .Caption = "Mail Merge Using Doc6.doc"
        .Visible = True
        .DescriptionText = "Execute a Mail Merge on a Contact Item"
        .TooltipText = "Execute a Mail Merge on a Contact Item"
        .Tag = "886"
        .Enabled = True
        .Style = msoControlCustom
    End With
    Call SyncSMM6Button(gcbSMM6)
    'ADD THE SEVENTH SUB MENU ITEM
    Set gcbSMM7 = gcbSMM.Controls.Add(msoControlButton, 1, "SMM7", , True)
    With gcbSMM7
        .Caption = "Mail Merge Using Doc7.doc"
        .Visible = True
        .DescriptionText = "Execute a Mail Merge on a Contact Item"
        .TooltipText = "Execute a Mail Merge on a Contact Item"
        .Tag = "887"
        .Enabled = True
        .Style = msoControlCustom
    End With
    Call SyncSMM7Button(gcbSMM7)
    
    Set goExp = Application.ActiveExplorer
    
End Sub
 
Private Sub goExp_SelectionChange()
 
    Dim oExp As Outlook.Explorer
    Dim oFolder As Outlook.MAPIFolder
    Dim oSel As Outlook.Selection
    Dim oItems As Outlook.Items
    Dim vType As Variant
    
    Set oExp = Outlook.ActiveExplorer
    Set oFolder = Outlook.ActiveExplorer.CurrentFolder
    If oFolder.DefaultItemType <> olContactItem Then
        gcbSMM.Enabled = False
        GoTo ClearAndExit
    Else
        If oFolder.DefaultMessageClass <> "IPM.Contact" Then
            gcbSMM.Enabled = False
            GoTo ClearAndExit
        End If
    End If
    Set oSel = oExp.Selection
    If oSel.Count > 0 Then
        Select Case oSel.Item(1).Class
            Case olTask
                vType = "Task"
            Case olMail
                vType = "Email"
            Case olContact
                vType = "Contact"
            Case olJournal
                vType = "Journal"
            Case olNote
                vType = "Note"
            Case olAppointment
                vType = "Calendar"
            Case Else
                vType = "Unknown Type!"
        End Select
    Else
        gcbSMM.Enabled = False
        GoTo ClearAndExit
    End If
    If oSel.Count > 1 Then
        gcbSMM.Enabled = False
        GoTo ClearAndExit
    ElseIf oSel.Item(1).Class = olContact And vType = "Contact" Then
        gcbSMM.Enabled = True
        gcbSMM.Caption = "Single Mail Merge of Contact Item '" & oSel.Item(1) & "'"
        Set gcbMM = gcbTools.CommandBar.FindControl(msoControlButton, gcbTools.Controls("Mail Mer&ge...").ID, , , True)
    Else
        gcbSMM.Enabled = False
        GoTo ClearAndExit
    End If
    
ClearAndExit:
    Set oExp = Nothing
    Set oFolder = Nothing
    Set oSel = Nothing
    
End Sub

More VBA Code...

VB Code:

'SETUP THE EVENTS FOR THE MENUITEMS
Private Sub SyncSMMNew_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
'<RR VB/OUTLOOK GURU 04/05/04>
    Select Case Ctrl.Tag
        Case "880"
            Call SelectedMailMerge("New")
        Case Else
            CancelDefault = True
    End Select
End Sub
 
Private Sub SyncSMMNewButton(btn As Office.CommandBarButton)
'<RR VB/OUTLOOK GURU 04/05/04>
    Set SyncSMMNew = btn
    If btn Is Nothing Then
        MsgBox "Sync. of '" & btn.Caption & "' button event failed!", vbCritical + vbOKOnly
    End If
End Sub
 
Private Sub SyncSMM1_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
'<RR VB/OUTLOOK GURU 04/05/04>
    Select Case Ctrl.Tag
        Case "881"
            Call SelectedMailMerge("D:\My Documents\Doc1.doc")
        Case Else
            CancelDefault = True
    End Select
End Sub
 
Private Sub SyncSMM1Button(btn As Office.CommandBarButton)
'<RR VB/OUTLOOK GURU 04/05/04>
    Set SyncSMM1 = btn
    If btn Is Nothing Then
        MsgBox "Sync. of '" & btn.Caption & "' button event failed!", vbCritical + vbOKOnly
    End If
End Sub
 
Private Sub SyncSMM2_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
'<RR VB/OUTLOOK GURU 04/05/04>
    Select Case Ctrl.Tag
        Case "882"
            Call SelectedMailMerge("D:\My Documents\Doc2.doc")
        Case Else
            CancelDefault = True
    End Select
End Sub
 
Private Sub SyncSMM2Button(btn As Office.CommandBarButton)
'<RR VB/OUTLOOK GURU 04/05/04>
    Set SyncSMM2 = btn
    If btn Is Nothing Then
        MsgBox "Sync. of '" & btn.Caption & "' button event failed!", vbCritical + vbOKOnly
    End If
End Sub
 
Private Sub SyncSMM3_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
'<RR VB/OUTLOOK GURU 04/05/04>
    Select Case Ctrl.Tag
        Case "883"
            Call SelectedMailMerge("D:\My Documents\Doc3.doc")
        Case Else
            CancelDefault = True
    End Select
End Sub
 
Private Sub SyncSMM3Button(btn As Office.CommandBarButton)
'<RR VB/OUTLOOK GURU 04/05/04>
    Set SyncSMM3 = btn
    If btn Is Nothing Then
        MsgBox "Sync. of '" & btn.Caption & "' button event failed!", vbCritical + vbOKOnly
    End If
End Sub
 
Private Sub SyncSMM4_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
'<RR VB/OUTLOOK GURU 04/05/04>
    Select Case Ctrl.Tag
        Case "884"
            Call SelectedMailMerge("D:\My Documents\Doc4.doc")
        Case Else
            CancelDefault = True
    End Select
End Sub
 
Private Sub SyncSMM4Button(btn As Office.CommandBarButton)
'<RR VB/OUTLOOK GURU 04/05/04>
    Set SyncSMM4 = btn
    If btn Is Nothing Then
        MsgBox "Sync. of '" & btn.Caption & "' button event failed!", vbCritical + vbOKOnly
    End If
End Sub
 
Private Sub SyncSMM5_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
'<RR VB/OUTLOOK GURU 04/05/04>
    Select Case Ctrl.Tag
        Case "885"
            Call SelectedMailMerge("D:\My Documents\Doc5.doc")
        Case Else
            CancelDefault = True
    End Select
End Sub
 
Private Sub SyncSMM5Button(btn As Office.CommandBarButton)
'<RR VB/OUTLOOK GURU 04/05/04>
    Set SyncSMM5 = btn
    If btn Is Nothing Then
        MsgBox "Sync. of '" & btn.Caption & "' button event failed!", vbCritical + vbOKOnly
    End If
End Sub
 
Private Sub SyncSMM6_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
'<RR VB/OUTLOOK GURU 04/05/04>
    Select Case Ctrl.Tag
        Case "886"
            Call SelectedMailMerge("D:\My Documents\Doc6.doc")
        Case Else
            CancelDefault = True
    End Select
End Sub
 
Private Sub SyncSMM6Button(btn As Office.CommandBarButton)
'<RR VB/OUTLOOK GURU 04/05/04>
    Set SyncSMM6 = btn
    If btn Is Nothing Then
        MsgBox "Sync. of '" & btn.Caption & "' button event failed!", vbCritical + vbOKOnly
    End If
End Sub
 
Private Sub SyncSMM7_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
'<RR VB/OUTLOOK GURU 04/05/04>
    Select Case Ctrl.Tag
        Case "887"
            Call SelectedMailMerge("D:\My Documents\Doc7.doc")
        Case Else
            CancelDefault = True
    End Select
End Sub
 
Private Sub SyncSMM7Button(btn As Office.CommandBarButton)
'<RR VB/OUTLOOK GURU 04/05/04>
    Set SyncSMM7 = btn
    If btn Is Nothing Then
        MsgBox "Sync. of '" & btn.Caption & "' button event failed!", vbCritical + vbOKOnly
    End If
End Sub
 
Private Function SelectedMailMerge(ByVal sDocument As String)
'<RR VB/OUTLOOK GURU 04/05/04>
    gcbSMM.Enabled = True
    Set gcbMM = gcbTools.CommandBar.FindControl(msoControlButton, gcbTools.Controls("Mail Mer&ge...").ID, , , True)
    'CHANGE THIS PATH TO REFLECT WHERE YOU SAVED IT
    Shell "D:\Development\Outlook\Mail Merge\MMWatcher.exe /" & sDocument, vbHide
    gcbMM.Execute
End Function

This is the VB Code for the Watcher program that automates the
Mailmerge wizard in Outlook. Compile it and copy it over to the
corresponding path you enter in the "SelectedMailMerge"
procedure in the VBA Code.

You can easily make changes to the code by uncommenting
thelines for things like merge to, etc.

VB Code...

VB Code:

Option Explicit
 
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long
 
Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
 
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
(ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
 
Private Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" _
(ByVal hwnd As Long, ByVal lpString As String, ByVal cch As Long) As Long
 
Private Declare Function GetWindowTextLength Lib "user32" Alias "GetWindowTextLengthA" _
(ByVal hwnd As Long) As Long
 
Private Declare Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, ByVal lParam As Long) As Long
 
Private Declare Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hwnd As Long, ByVal lpClassName As String, _
ByVal nMaxCount As Long) As Long
 
Private Declare Function GetWindow Lib "user32" (ByVal hwnd As Long, ByVal wCmd As Long) As Long
 
Private Declare Function GetDlgCtrlID Lib "user32" (ByVal hwnd As Long) As Long
 
Private Declare Function CheckRadioButton Lib "user32" (ByVal hDlg As Long, ByVal nIDFirstButton As Long, _
ByVal nIDLastButton As Long, ByVal nIDCheckButton As Long) As Long
 
Private Declare Function PutFocus Lib "user32" Alias "SetFocus" (ByVal hwnd As Long) As Long
 
Private Const GW_HWNDNEXT = 2
Private Const GW_OWNER = 4
 
Private Const BM_CLICK = &HF5
 
Private Const WM_CLOSE = &H10
Private Const WM_COMMAND = &H111
Private Const WM_GETTEXT = &HD
Private Const WM_GETTEXTLENGTH = &HE
Private Const WM_KEYDOWN = &H100
Private Const WM_KEYUP = &H101
Private Const WM_SETTEXT As Long = &HC
Private Const WM_LBUTTONDOWN = &H201
Private Const WM_LBUTTONUP = &H202
Private Const WM_LBUTTONDBLCLK As Long = &H203
Private Const WM_PASTE As Long = &H302
Private Const WM_COPY As Long = &H301
Private Const WM_COPYDATA As Long = &H4A
Private Const WM_CUT As Long = &H300
 
Private Const CB_FINDSTRINGEXACT = &H158
Private Const CB_ERR = (-1)
Private Const CB_FINDSTRING = &H14C
Private Const CB_GETCOUNT = &H146
Private Const CB_GETCURSEL = &H147
Private Const CB_GETTOPINDEX = &H15B
Private Const CB_SETCURSEL = &H14E
Private Const CB_SHOWDROPDOWN = &H14F
 
Private Const VK_DOWN = &H28
Private Const VK_UP = &H26
Private Const VK_SELECT = &H29
Private Const VK_SPACE = &H20
Private Const VK_TAB = &H9
 
Private mlHwnd As Long
Private mlHwndSel As Long
Private mlHwndFields As Long
Private mlHwndExistDoc As Long
Private mlHwndExistDocPath As Long
Private mlHwndDocType As Long
Private mlHwndMergeTo As Long
Private mlHwndBrowse1 As Long
Private mlHwndCD As Long
Private mlHwndCDPath As Long
Private mlHwndCDOK As Long
Private mlHwndOK As Long
 
Public Sub Main()
 
    Dim rtn As Long
    Dim arSwitches() As String
    Dim i As Integer
    
    Do While FindWindow("#32770", "Mail Merge Contacts") = 0
        DoEvents
    Loop
    mlHwnd = FindWindow("#32770", "Mail Merge Contacts")
    
    arSwitches = Split(Command, "/")
    If UBound(arSwitches) = 0 Then
        MsgBox "Missing startup parameter!", vbOKOnly + vbCritical
        End
    End If
    
    'SELECT ONLY SELECTED CONTACTS
    mlHwndSel = FindWindowEx(mlHwnd, 0&, "Button", "&Only selected contacts")
    rtn = SendMessage(mlHwndSel, WM_LBUTTONDOWN, ByVal 0&, ByVal 0& )
    rtn = SendMessage(mlHwndSel, WM_LBUTTONUP, ByVal 0&, ByVal 0& )
    
    'SELECT ALL CONTACT FIELDS
    mlHwndFields = FindWindowEx(mlHwnd, 0&, "Button", "All contact &fields")
    rtn = SendMessage(mlHwndFields, WM_LBUTTONDOWN, ByVal 0&, ByVal 0& )
    rtn = SendMessage(mlHwndFields, WM_LBUTTONUP, ByVal 0&, ByVal 0& )
    
    'SELECT PATH TO NEW/EXISTING DOCUMENT
    If arSwitches(1) = "New" Then
        mlHwndExistDoc = FindWindowEx(mlHwnd, 0&, "Button", "&New document")
    Else
        mlHwndExistDoc = FindWindowEx(mlHwnd, 0&, "Button", "&Existing document:")
    End If
    rtn = SendMessage(mlHwndExistDoc, WM_LBUTTONDOWN, ByVal 0&, ByVal 0& )
    rtn = SendMessage(mlHwndExistDoc, WM_LBUTTONUP, ByVal 0&, ByVal 0& )
    
    'SELECT THE DOCUMENT TYPE
    mlHwndDocType = FindWindowEx(mlHwnd, 0&, "REComboBox20W", vbNullString)
    rtn = SendMessage(mlHwndDocType, CB_SHOWDROPDOWN, ByVal 1, ByVal 0& ) 'DROPDOWN
    If rtn = 1 Then
        'ALREADY SELECTED BY DEFAULT. IF NOT CHANGE SO ITS SYNCHED
        'FORM LETTERS
        rtn = SendMessage(mlHwndDocType, CB_SETCURSEL, 0&, 0& ) 'SET TO FIRST OPTION
        'MAILING LABELS
''        rtn = SendMessage(mlHwndDocType, WM_KEYDOWN, VK_DOWN, 0& ) 'MOVE DOWN 1
''        rtn = SendMessage(mlHwndDocType, WM_KEYUP, VK_DOWN, 0& )
        'ENVELOPES
''        rtn = SendMessage(mlHwndDocType, WM_KEYDOWN, VK_DOWN, 0& ) 'MOVE DOWN 1
''        rtn = SendMessage(mlHwndDocType, WM_KEYUP, VK_DOWN, 0& )
        'CATALOGS
''        rtn = SendMessage(mlHwndDocType, WM_KEYDOWN, VK_DOWN, 0& ) 'MOVE DOWN 1
''        rtn = SendMessage(mlHwndDocType, WM_KEYUP, VK_DOWN, 0& )
        'MAKE THE SELECTION
        rtn = SendMessage(mlHwndDocType, CB_SETCURSEL, ByVal 0&, ByVal 0& ) 'MAKE SELECTION
        rtn = SendMessage(mlHwndDocType, WM_LBUTTONDOWN, ByVal 0&, ByVal 0& )
        rtn = SendMessage(mlHwndDocType, WM_LBUTTONUP, ByVal 0&, ByVal 0& )
        rtn = SendMessage(mlHwndDocType, CB_SHOWDROPDOWN, ByVal 0, ByVal 0& ) 'CLOSE UP
    End If
    
    'MERGE TO:
    mlHwndMergeTo = GetWindow(mlHwndDocType, GW_HWNDNEXT)
    mlHwndMergeTo = GetWindow(mlHwndMergeTo, GW_HWNDNEXT)
    rtn = SendMessage(mlHwndMergeTo, CB_SHOWDROPDOWN, ByVal 1, ByVal 0& ) 'DROPDOWN
    If rtn = 1 Then
        'NEW DOCUMENT
        rtn = SendMessage(mlHwndMergeTo, CB_SETCURSEL, 0&, 0& ) 'SET TO FIRST OPTION
        'PRINTER
'        rtn = SendMessage(mlHwndMergeTo, WM_KEYDOWN, VK_DOWN, 0& ) 'MOVE DOWN 1
'        rtn = SendMessage(mlHwndMergeTo, WM_KEYUP, VK_DOWN, 0& )
        'EMAIL ADDRESS
'        rtn = SendMessage(mlHwndMergeTo, WM_KEYDOWN, VK_DOWN, 0& ) 'MOVE DOWN 1
'        rtn = SendMessage(mlHwndMergeTo, WM_KEYUP, VK_DOWN, 0& )
        'MAKE THE SELECTION
        rtn = SendMessage(mlHwndMergeTo, CB_SETCURSEL, ByVal 0&, ByVal 0& ) 'MAKE SELECTION - MAILING LABELS
        rtn = SendMessage(mlHwndMergeTo, WM_LBUTTONDOWN, ByVal 0&, ByVal 0& )
        rtn = SendMessage(mlHwndMergeTo, WM_LBUTTONUP, ByVal 0&, ByVal 0& )
        rtn = SendMessage(mlHwndMergeTo, CB_SHOWDROPDOWN, ByVal 0, ByVal 0& ) 'CLOSE UP
    End If
    
    'ENTER THE PATH IF PARAMETER IS A EXISTING DOCUMENT TYPE
    If arSwitches(1) <> "New" Then
        'NEED TO PASTE THE FILE APTH IN SO IT TRIGGERS THE VERIFICATION OF DOCUMENT BY OUTLOOK
        Clipboard.Clear
        Clipboard.SetText arSwitches(1) 'FILE PATH AND NAME
        mlHwndExistDocPath = FindWindowEx(mlHwnd, 0&, "RichEdit20WPT", vbNullString)
'        rtn = SendMessage(mlHwndExistDocPath, WM_SETTEXT, 0&, ByVal "D:\My Documents\Doc1.doc")
        rtn = SendMessage(mlHwndExistDocPath, WM_LBUTTONDOWN, ByVal 0&, ByVal 0& )
        rtn = SendMessage(mlHwndExistDocPath, WM_LBUTTONUP, ByVal 0&, ByVal 0& )
        rtn = PutFocus(mlHwndExistDocPath)
        rtn = SendMessage(mlHwndExistDocPath, WM_PASTE, ByVal 0&, ByVal 0& )
        rtn = PutFocus(mlHwndExistDocPath)
        rtn = SendMessage(mlHwndExistDocPath, WM_LBUTTONDBLCLK, ByVal 0&, ByVal 0& )
    End If
    
    'CLICK OK
    mlHwndOK = FindWindowEx(mlHwnd, 0&, "Button", "OK")
    PostMessage mlHwndOK, BM_CLICK, 0&, 0&
    
End Sub

One more thing. If you want to merge to a email address then we
will need to change the code a bit since when the option is
selected it make a textbox become visible for the email address
entry. We would need to get the handel to this textbox and enter
the address in.

Later.

Hi Robb

Pasted the code in to "This outlook Session" and got the following error message when running the macro

Compile Error:

Only Valid in Object Module

This is at the first Public WithEvents Call line 4

I guess this is something I am doing ......

At this stage I have not compiled and saved the watcher.

Any Ideas

BTW I have sent you a screen shot via email, and if i comment out each of the Public WithEvents it fails at the next.

According to your screenshot, you pasted the code into a module.
This is not allowed for a Public WithEvents. It is supossed to be
pasted into the "thisOutlookSession" class in the node above the
module named "Microsoft Office Outlook Objects". The code
needs to be in theat class so it can be instanciated as a class
object.

http://www.vbforums.com/attachment.p...postid=1673038

That will solve all issues.

HTH.

Sorry, my mistake. When I was copying the code over I added
the .Style property after in the vbforums reply box manually and I
added it to the CommandBarPopup menuitem when its only for
the CommandBarButton only. Take out that line only and that
should do it.

http://www.vbforums.com/attachment.p...postid=1675330

Hi Robb

Sorry for deviating to email, but for some reason I can not get a screen capture onto this forum.

OK deleted the line in question, and the whole code runs without error.

Maybe showing my ass here, but am I meant to paste the watcher code into one of the modules (1 or 2) next to the This outlook session to compile ?

I have tried this but all I get is an undefined error on the clipboard command in Line 163. Commenting this out errors at the next clipboard line.

Also what do I do with the mmwatcher zip file that has a number of files in it?

Bet your wishing you never started this now :)

Cheers

Not at all. Dont worry about it.

The .zip file is the watcher visual basic project - same as I posted.
You need to compile the watcher program in VB6 not VBA. The
watcher needs to be a separate program (not another module in
VBA) because when the wizard comes up its modal which will
stop all code execution. The watcher being separate will be able
to continue to automate the wizard. Do you have VB6? If not I
can compile it for you and post the exe file.

We are almost there. Home stretch.

:)

Hi Rob

No I do not have VB6, I would be ever so greatfull if you could compile and post the file.

All The Best

Here is the compiled exe for you with the settings of :
Mail Merge Watcher™ for Outlook 2003 and Word 2003.
Settings: "Only selected contacts", "All contact fields", "Existing"
or "New" determined by commandline parameter, "Document
Type = Form Letters", "Merge to" determined by commandline
parameter.

You only need the basic vb runtime files which should already be
installed if you have Office w/VBA installed.

You can view the settings under the comments property of the
summary tab when you right click > properties on the
unzipped .exe file.

Hi Robb

Things is starting to happen now, I get a menu option up when a contact is highlighted, I will do the final config on it tomorrow.

Unfortunatelly I have not had any time over the weekend to play.

I will look forward to fully testing it tomorrow

All the best !

Cool, I will keep an eye out for your next post.

Good things to report !!!!

You have earned your title of Guru...

Essentially the code works, only bug I have to report is that if I try to do a manual mailmerge, the mmwatcher errors with: -

Run time error 9
Subscript out of range

Dont know whether this means "jack" to you, coz it sure doent to me ....LOL

Would it be cheaky to attach a bit of a wish list ?, i'l chance my arm anyway as you seem a decent guy.

Would it be possible to have the menu options as toolbar buttons ?

I have the office assistant running, so I get a popup asking me to firstly confirm a merge from the wizard, and then another one from outlook asking the same, would it be possible to "hush these"?

and lastly would it be poss to autosave these, I have hiddent text at the top of the doc which creates the save bame automatically anyway, and could it also auto print.

Now is that taking a mile...or what??

Thank you so much for the effort you have put in, it is really appreciated.


Cheers

Thanks for the props.

The only issue I could see is if there is no commandline parameter
of either "New" or the existing document path/filename. I was
trapping for that by checking for a 0 element in the array
arSwitches. I tested it out and if no commandline switch then it
actually returns a -1 not 0. I fixed that and attached a new
compile. Check out the new exe icon!

The only thing is that you say a "manual mailmerge". If that is the
case then wouldnt you not need the watcher since you are doing
it manually?

Yes, we could make the toolbar, but then we would probably
want to make this whole thing an official Outlook Add-In (maybe
too much work, not that I can't do it or havent done one. More of
a time issue). To make a toolbar we would need another class or
two to add to the VBA.

With the office assistant prompting you for acknowledgement
I think there is a property we can change to turn it off temporarly.
Let me look for it.

"Autosave", if you mean to save the word mailmerge document
after its created then yes and we can turn off the assistant
prompts too.

I'll be back.

Hi Robb

Tried the updated watcher, it triggers a Missing Startup Parameter when attempting to use the manual mailmerge, I agree with you that why would I be doing it manually as I now have an automatic method. Customers want the earth ...LOL

Hushing the 2 confirmations to merge and autosaving would be a hell of a good icing on the cake though.

If I disable the office assistant you still have to select the merge to new doc from the toolbar in word, and than click merge on the following dialog.

Would it also be possible to print (with manual duplex selected) to the default printer at the same time in code ? if not too time consuming.

The toolbar is a whim of mine and not so important, I dont want you to spend all your time on this, and am sure you have already spent a great deal of time already.

Cheers

No, I really haven't spent too much time on this, just some
lunches and "breaks" when I get bored with my current project at
work.
;)

How are you trying to do a manual MM? Running the
MMWatcher.exe directly? The MMWatcher.exe needs to be run
from the Outlook menuitems only. That is why it tells you that it
is missing a startup parameter. The Outlook menuitems are
passing the parameter of the document path & filename or "New".

:)

When doing a manual mail merge I am selecting tools > mailmerge and then populating the dialog box manually.

I execute the watcher by clicking on it manually, running from the outlook menu items, I dont understand this....why all of a sudden to I feel I have missed the point totally somewhere back down the line.

Sorry to be such a clown....:blush:

Program flow and details:

Automated mailmerge to a new word document...
1.) Select a single contact item.
2.) Click Tools > Mail merge using 'John doe' > Mail Merge Using a New Document.
3.) The watcher is executed automatically, passing the "New" parameter to the watcher.
4.) Word is opened with a mail merge recordset of a single record of the selected 'John Doe' contact.
5.) Document is ready to be modified/printed.

Automated mailmerge to an existing word document...
1.) Select a single contact item.
2.) Click Tools > Mail merge using 'John doe' > Mail Merge Using 'Debtor Chase up 2.doc'. (For example)
3.) The watcher is executed automatically, passing the "Debtor Chase up 2.doc" parameter to the watcher.
4.) Word is opened with a mail merge recordset of a single record of the selected 'John Doe' contact.
5.) Document is ready to be modified/printed.

Is this what you wanted to accomplish or are we mis-communicating?

:)

Aha

I thought I had to manually start the watcher, or put it in startup folder.

It all works OK if I start the watcher manually before outlook.

However when I reboot from scratch, start outlook run the macro in outlook (my outlook installation sometimes want to reinstall part of outlook when I run a macro, christ knows why ???) and then select a contact it does not start the watcher automatically.

Maybe this is a problem with my OL inst rather than your coding?

Under no circumstances should there be a need to manually run
the MMWatcher.exe program. That is why I added the code to
make sure it was being launched from an Outlook menuitem.

The watcher should only be started by the menuitems.

Are you saying that in your Private Sub Application_Startup()
procedure you have code to invoke the watcher? Do not do this.

During the Outlook re-installion process the initialization of our
custom menu items is not happening. You need to fix the issue
with Outlook so everytime you start Outlook our menu items will
get initialized. Basically, the Outlook macro issue is keeping the
click event of our menu items from processing. They are just dead
menu items when this happens.

:)

Sorry no I meant program startup group, OK im Gonna do a reload of my PC and Office coz ever since I installed a BT adapter on my PC office keeps forgetting it is fully installed.

Just one last question before I go into the ether of reinst tonight, is the VBA code you have written embeded in the PST file of outlook, or will I need to re paste it with the new inst ?

No, its not embedded into your .pst file. It is a separate file
located in your profile path with the file name of VbaProject.OTM.
My filepath for Windows 2000 Pro =
"C:\Documents and Settings\RobDog888\Application Data\Microsoft\Outlook\VbaProject.OTM"

Save this file and replace after you re-install. I'm not 100%
certain that you can just paste the file over the new installation's
file, but give it a try and before you overwrite the new
installation's file save the new one too. Just in case.

Good luck tonight and hope to hear things are working better for
you tomorrow. I will work on the suppression of the assistant
prompts and autosaving.

:)

Edit: I can't believe that I am almost at 2000 posts!
Also, that this thread is still only one page LONG.

Hi Robert

Set up a new VMware version with OL2003, and it all works fine and loads the watcher itself, so It was obviously my OL inst that was the problem and not your code.

Should I continue to use your updated watcher or revert back ?

Cheers

The updated watcher would be fine. The updated watcher only
contains code to check for previous instance and that the
comandline parameter is being passed. Also, the watcher
contains the outlook 2003 icon.

Glad to hear that the installation went well and the code is
working well.

I just got out of a meeting. So, I will start on the two other issues.

Dude, I broke 2000 post last night!
Coming up on "Ultra-Member" level @ 2048.

:)

OK look forward to the next installment, I can smell the finishing line :)

Hey Matt, the finishing line is still out of reach depending on what
you think about this answer.

If you really need more control over the Word MM Documents like
the printing and suppression of prompts, we may need to bypass
the automation of the wizard and create our own mail merge
recordset or add some VBA code to the Word MM Documents. I am
not too sure which will be the best route to take, but I am going
to try to just automate word using some VBA word macros.

Will keep you up to date.

Hi Robb

I am guessing (being the operative word) that some vba in word would be the easiest solution.

As OL is the hub of our work and we push data to word rather than sitting in word and pulling data in from OL I think that some automated code in the MM document might do the job.

But you are the expert.....

I think this may be a solution I am willing to try.

In Word we will need something that will invoke the code to
suppres the messages and start the merge or print process.
I can not do this on the open event because the mail merge
recordset is not populated yet. So, how about a new button to
invoke this code to do that? One click and all is automated in
Word?

Hi Robb

That sounds good to me, could this be in the form of a Dialog Box, or would it have to be bound to a menu or toolbar ?

Cheers

Menu, toolbar, or addin.

So, actually two clicks. One in Outlook and another in Word to
finish up.

I look forward to the next section of code, I am now using your code fully "live" on our system and it appears to be 100% solid at the moment.


Cheers

Cool, glad to hear it (do I get an honerable mention in the About
form?) JK!

I will be postponing my work until later in the week. Too much to
do right now and out of the office on an upgrade/installation.

I've been searching for about a week for solution to the exact same problem that silenttalk was having (in 4 years, it seems no-one else required a solution to the same problem!).

I've done minor modifications to the code to get it to work for myself and it is FANTASTIC, AWESOME, AMAZING and I am incredibly appreciative of all the work that RobDog did here.

One question though:

RobDog, did you ever find a way to invoke the code in Word to suppress the messages and start the merge or print (see post #48 above)?

Hi Rob,

I have been trying your code in Office 2007 to no avail.

Is there a chance that you can offer some assistance...

Thanks

Jim P.

Oh this is old code I wrote lol. For 2007 Outlook some things will work and others definatley wont. What errors are you getting?

I'm about to head out the door for home so will look for a reply later.


Ps, Thanks Nunjafush and no I havent looked at this code for a few years.

Hi.

Thanks for thinking about this "old code" ... lol ... I am amazed by it ... I had to write a custom ribbon for Word 2007 and it took a long time and was over my pay grade ... I wish that I had found you for that...

I started a new thread "Oulook 2007 Mailmerge Public Folder Contacts"

not sure how to put a direct like to that thread here.... sry

I have got it to function fully with my own contacts but when I select and highlight a Public Folder Contact the menu option under "Tools" is not available.

Ok i will search for it. Usualy you can just sopy the url of your thread and paste into the post in this thread or a PM. ;)


Edit: Found it - http://www.vbforums.com/showthread.php?t=560715