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...
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.
VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
I dont answer coding questions via PM. Please post a thread in the appropriate forum.
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.
VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
I dont answer coding questions via PM. Please post a thread in the appropriate forum.
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.
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.
VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
I dont answer coding questions via PM. Please post a thread in the appropriate forum.
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.
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.
VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
I dont answer coding questions via PM. Please post a thread in the appropriate forum.
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
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?
VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
I dont answer coding questions via PM. Please post a thread in the appropriate forum.
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.
VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
I dont answer coding questions via PM. Please post a thread in the appropriate forum.
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.
VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
I dont answer coding questions via PM. Please post a thread in the appropriate forum.
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.
VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
I dont answer coding questions via PM. Please post a thread in the appropriate forum.
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
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.
VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
I dont answer coding questions via PM. Please post a thread in the appropriate forum.
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.
That will solve all issues.
HTH.
VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
I dont answer coding questions via PM. Please post a thread in the appropriate forum.
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.
VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
I dont answer coding questions via PM. Please post a thread in the appropriate forum.
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.
VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
I dont answer coding questions via PM. Please post a thread in the appropriate forum.
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.
VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
I dont answer coding questions via PM. Please post a thread in the appropriate forum.
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.
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.
VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
I dont answer coding questions via PM. Please post a thread in the appropriate forum.
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.
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".
VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
I dont answer coding questions via PM. Please post a thread in the appropriate forum.
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.
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?
VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
I dont answer coding questions via PM. Please post a thread in the appropriate forum.
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.
VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
I dont answer coding questions via PM. Please post a thread in the appropriate forum.
Reply With Quote
