?
Solved

Command Button NOT Working after a Mail Merge

Posted on 2012-09-05
7
Medium Priority
?
668 Views
Last Modified: 2012-09-13
I have a user who has inserted a command button in a mail merge document in Word 2010.  The command button is simply opening up an email and attaching the Word document.  The command button works fine in the main document before the merge is done.  But as soon as you perform the mail merge, the command button no longer works.  Why?  Is this a limitation of Word?  Below is the code of the command button.


Private Sub Email_Me_Click()
ActiveDocument.SendMail
End Sub
0
Comment
Question by:esu4236
  • 4
  • 3
7 Comments
 

Author Comment

by:esu4236
ID: 38369520
For some reason after you do a mail merge, the system STRIPS the one code line out of the macro.  WHY?????  Any way to avoid this?
0
 
LVL 23

Expert Comment

by:irudyk
ID: 38370136
Maybe try adding code to the existing button such that it will perform the mail merge and then send the resulting mail merged document?  Something like:
Private Sub Email_Me_Click()
    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With
    ActiveDocument.SendMail
End Sub

Open in new window

0
 

Author Comment

by:esu4236
ID: 38372064
We cannot put the mail merge process into the macro, because the mail merge process is being done by one person, and then the macro command button is used by other users who click the button to send an email.  So there's no way to include this in the command button.  Thanks for the idea though.

Anyone else have any other ideas?????
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 23

Expert Comment

by:irudyk
ID: 38372305
I see, well, as far as I'm aware in Word 2010, a final mail merge document doesn't carry over any vba code from where it was orignated from, since the resulting document is in a DOCX format whcih doesn't support VBA code.

Given this fact, another suggestion would be to train/give instructions to the user who is e-mailing the final merged document as to how this can be done by them via the menu in Word - i.e. File | Save & Send | Send Using E-mail | Send as Attachment
0
 
LVL 23

Accepted Solution

by:
irudyk earned 2000 total points
ID: 38372572
Another option would be to add another command button with a macro that would:
meger the document
save the merged file as a macro enable document
add the code for the Email_Me command button
re-save the merge document after the code has been added
So, as an example,
add a command button before/to the left of the Email_Me command button and give it the name Merge_Me (i.e. under the properties of the button)
in the VBA project window add the Microsoft Visual Basic for Applications Extensibility 5.3 reference (under Tools | References)
add the following code:
Private Sub Merge_Me_Click()
    Dim strPath As String
    strPath = ActiveDocument.Path
    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With
    With ActiveDocument
        .SaveAs strPath & "\" & ActiveDocument.Name & "-" & Format(Now(), "YYYMMDDHHNNSS"), FileFormat:=wdFormatXMLDocumentMacroEnabled
        .InlineShapes(1).ConvertToShape
        .Shapes(1).Delete
        .VBProject.VBComponents("ThisDocument").CodeModule.AddFromString ("Private Sub Email_Me_Click()" + Chr(13) + "ActiveDocument.SendMail" + Chr(13) + "End Sub")
        .Save
    End With
End Sub

Open in new window

0
 
LVL 23

Expert Comment

by:irudyk
ID: 38388069
Just wondering if tried implementing the above suggestions or if you are still having issues?
0
 

Author Closing Comment

by:esu4236
ID: 38396792
Thank you for your help.  Got it to work.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nice table. Huge mess. Maybe this was something you created way back before you figured out tabs or a document you received from someone else. Either way, using the spacebar to separate the columns resulted in a mess. Trying to convert text to t…
Introduction This tutorial provides instructions on how to properly format your Word document using the inbuilt tools provided. The benefits of using these tools means your documents are more accessible and easily portable to other applications an…
In a previous video Micro Tutorial here at Experts Exchange (http://www.experts-exchange.com/videos/1358/How-to-get-a-free-trial-of-Office-365-with-the-Office-2016-desktop-applications.html), I explained how to get a free, one-month trial of Office …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses
Course of the Month14 days, 9 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question