Adding 2 key-clicks to a macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob S - 16 Mar 2005 06:06 GMT

I have a macro that I use during the process of mail-merging a table using
addresses from an Outlook contacts folder. To do the mail-merge, I begin in
the contacts folder of OL (2003). I click Tools | Mail-merge | Browse |
Double-click the Existing Document file to use | Select Catalog as the
Document Type | OK which takes me to Word with a form
file on the screen. At that point I click "Merge to a New Document" or
Alt-Shift-N and click OK which takes me to the new document with a large
table in it. It is at that point I have been activating my macro to
reformat that table a bit.

My Question is: Can someone tell me how I can insert the two keystrokes I
mentioned above (I click "Merge to a New Document" or Alt-Shift-N and then
click OK) into the first of the existing macro? Since it is just 2
keystrokes, it seems that it should be simple; but I do not know how to do
it. Unfortunately, recording the two keystrokes into a macro and
copying those lines of code into the beginning of the other macro did NOT
work.

Can anyone here help?
Thank you very much.

Reply to this Message

Cindy M -WordMVP- - 27 Mar 2005 17:05 GMT

Hi Bob,

I take it the macro is in Word, and you're wanting to use the "key clicks" in
Outlook? No, there's really no way this is going to work as you envision it.

Better way to start would probably be to ask in an Outlook group if there's any
way to automate that part of it. Then have the macro in Word IN the document
(or its template), to execute automatically when the mail merge document is
opened (AutoOpen) (or created - AutoNew).

> I have a macro that I use during the process of mail-merging a table using
> addresses from an Outlook contacts folder. To do the mail-merge, I begin in
[quoted text clipped - 13 lines]
> copying those lines of code into the beginning of the other macro did NOT
> work.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail :-)

Reply to this Message

Bob S - 28 Mar 2005 03:47 GMT

Could I describe it a bit more precisely than I did before?

Yes, the macro is in Word. The macro basically takes a table I have created
with the mail merge and formats it in columns with a header etc.

No, the key clicks are also within Word. In the process of the mail-merge
OL opens one of my files (mosupt.doc) within Word and shows the mail-merge
tool bar. At that point, just as (or just after) Mosupt.doc is opened I do
2 mouse clicks. The first is to click the icon "Merge to a New Document"
(or alternatively to press Alt-Shift-N). The second click is to click OK.

When I do that manually, the program takes me to the new document with a
large table in it. Then, in that new document, I have been manually
activating my macro to reformat that table a bit.

I would like to automate that as much as possible. One way would be to have
those two mouse-clicks take place each time Mosupt.doc is opened. The other
would be to have be the first part of the macro I alread have (which
reformats the table). The ideal would be for the opening of Mosupt.doc to
automatically stimulate those two mouse clicks and then call the
reformatting macro I mentioned.

Is there a way to automate some of this or preferably all of it by tying
these two succsssive mouseclicks and then this macro to the opening of
Mosupt.doc?

Thanks for your help.

> Hi Bob,
>
[quoted text clipped - 42 lines]
> reply
> in the newsgroup and not by e-mail :-)

Reply to this Message

Cindy M -WordMVP- - 29 Mar 2005 20:19 GMT

Hi Bob,

> No, the key clicks are also within Word. In the process of the mail-merge
> OL opens one of my files (mosupt.doc) within Word and shows the mail-merge
> tool bar. At that point, just as (or just after) Mosupt.doc is opened I do
> 2 mouse clicks. The first is to click the icon "Merge to a New Document"
> (or alternatively to press Alt-Shift-N). The second click is to click OK.

Aha. In VBA, the equivalent would be

Sub AutoOpen() 'Executes when doc is opened
Dim doc as Word.Document

Set doc = ActiveDocument
with doc.MailMerge
.Destination = wdSendToNewDocument
.Execute
End with

And then would follow your code for manipulating the merge result. Actually,
since this is probably written using ActiveDocument, if you don't need the
main merge document any more, I'd stick a doc.Close in there after End With so
that it's very clear that ActiveDocument refers to the mail merge result.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail :-)

Reply to this Message

Bob S - 30 Mar 2005 00:46 GMT

Thanks Cindy.
I think I made a mistake in following your instructions. I copied the code
you put below and added doc.Close after the End With , as you suggested.

Then I inserted it within my other macro after the
Sub MoSupt ()
` comments

So, I have a
Sub MoSupt () followed by a Sub AutoOpen () before there is any End
Sub.

Is that a problem?

I recorded this macro in my MoSup.doc file. When I open it from OL2003
during the mailmerge, I get the following Run Time Error 5852
"Requested object is not available."
When I run debugger, it shows a yellow arrow next to
.Destination=wdSendToNewDocument

How can I fix my error?
Thanks again.

> Hi Bob,
>
[quoted text clipped - 33 lines]
> This reply is posted in the Newsgroup; please post any follow question or
> reply in the newsgroup and not by e-mail :-)

Reply to this Message

Cindy M -WordMVP- - 30 Mar 2005 11:07 GMT

Hi Bob,

> Sub MoSupt () followed by a Sub AutoOpen () before there is any End
> Sub.
>
> Is that a problem?

That won't work. You can have AutoOpen call MoSupt. Roughly

Sub AutoOpen()
'Do things
MoSupt
'Do other things
End Sub

Sub MoSupt
'Do things
End Sub

After you do this, see if things behave any differently? If you're still getting
errors, please copy/paste the entire set of code into your reply.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail :-)

Reply to this Message

Bob S - 31 Mar 2005 07:03 GMT

Thank you Cindy for your help. I am very new to VBA programming. Nothing
happened. In other words, when I opened MoSupt.doc with OL2003, I could see
not evidence that any macro had operated or was operating. I have included
the text of the two macros below, as you requested. What did I do wrong?
Bob

Sub AutoOpen() 'Executes when doc is opened
Dim doc As Word.Document

Set doc = ActiveDocument
With doc.MailMerge
.Destination = wdSendToNewDocument
.Execute
End With
doc.Close
Title
End Sub
Sub Title()
' Title Macro
' Macro recorded 3/30/2005 by Robert Singleton
Selection.SplitTable
Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
Selection.Font.Bold = wdToggle
Selection.TypeText Text:="Monthly Pledges - "
Selection.InsertDateTime DateTimeFormat:="M/d/yyyy",
InsertAsField:=True, _
DateLanguage:=wdEnglishUS, CalendarType:=wdCalendarWestern, _
InsertAsFullWidth:=False
End Sub

> Hi Bob,
>
[quoted text clipped - 28 lines]
> reply
> in the newsgroup and not by e-mail :-)

Reply to this Message

Bob S - 01 Apr 2005 05:58 GMT

I may have been unclear below. I start the mail-merge process in OL which
itself opens MoSupt.doc within Word 03. But when it does, I see no evidence
that any macro has executed. What did I do wrong?
Bob

> Thank you Cindy for your help. I am very new to VBA programming. Nothing
> happened. In other words, when I opened MoSupt.doc with OL2003, I could
[quoted text clipped - 59 lines]
>> reply
>> in the newsgroup and not by e-mail :-)

Reply to this Message

Bob S - 03 Apr 2005 06:12 GMT

Perhaps my question was too vague or general . Let me ask 2 specific ones.

1. Did I make the call to my other macro correctly (namely simply with the
word "Title")?
2. Is putting these macros (AutoOpen and Title) in my global template
sufficient to get MoSupt.doc to fire this macro as it is opened?

Thanks for your help.
Bob

>I may have been unclear below. I start the mail-merge process in OL which
>itself opens MoSupt.doc within Word 03. But when it does, I see no
[quoted text clipped - 64 lines]
>>> or reply
>>> in the newsgroup and not by e-mail :-)

Reply to this Message