Solved

MailMerge cc with VBA table issue

Posted on 2016-11-15
12
18 Views
Last Modified: 2016-11-26
Dear Experts,
My user requested to use cc fields in his mail merge, so I refereed to the solution by GrahamSkan(2011-08-14) and tried to run it.
After having issues, I started to debug, and realized that I am not really accessing the recipients' list in mail merge.

Here is what I did.
1. Run the mail merge to create the list, save and close it.  I saved the merged documents as Test.docx, verified that it has 3 sections.
2. Created a module containing the code from GrahamSkan's post.  Compiled.
3. Ran the code.  It does not send.

If I strip the Table portion of the code and hard code in the recipients names, the code will work and it will send out 3 emails.
When I debug, the variable "DataRange" is set to Nothing.  That is why I think I am not accessing the recipients list.
Am I saving the merged documents in the wrong format?  I do not think I have a clear understanding of how the recipients are stored in "ActiveDocument.Table" object.

Please advise.

Code.txt
0
Comment
Question by:yballan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
12 Comments
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 41889176
Hi,

Could you send a dummy ?

Regards
0
 

Author Comment

by:yballan
ID: 41889657
Dear Rgonzo1971,

I am not understanding your question, do you mean a dummy email without mail merge?
I can send a mail if I hard code the TO: address.  It is not fetching from the recipient list, which I am assuming I can access by

 With ActiveDocument.Tables(1).Range,  or some type of commands like that.
I cannot seem to be able to get to the info that were merged into Word from Excel during mail merge.
0
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 41889661
like  Test.docx
0
[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

 

Author Comment

by:yballan
ID: 41889707
Oh, OK.  Here it is.  This is after the merge.
-test1.docx
0
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 41889715
Don't see the to nor the CC and there is no table
0
 

Author Comment

by:yballan
ID: 41889794
No, It is already merged with an Excel file with all of the info in them.  So it is the end results from Mail Merge, just as the code creator GrahamSkan's post describes.  So I was assuming that the recipients were included in the ActiveDocument.Table.
0
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 41890089
could you send a dummy of the  ActiveDocument used
0
 

Author Comment

by:yballan
ID: 41892022
I use MailMergeCC.docx then merge EmailAddress.xlsx, save the merged documents as test1.docx, then run my code.  Please see attached.
-MailMergeCC.docx
-EmailAddresses.xlsx
0
 

Author Comment

by:yballan
ID: 41893638
Dear Experts,

After researching for a while, I got the half of the code working.  I was executing the code after the merge was completed, but now I execute before "Edit indivigual Letters" step and I can at least see the DataFields attached to DataSource.  However, I still cannot access all of the records in the DataSource.  

How can I get the pointer to advance to the next record in DataFields?  If I run this code:
ActiveDocument.MailMerge.DataSource.DataFields("CCAddress").Value
It only points to the first record.

In the code I referred to earlier, this is accomplished by using Table(), as in this piece of code, but when I run this, my Table points to "Nothing".  Please advise.

Set Maillist = ActiveDocument
 For j = 1 To NumPages
    Set oItem = oOutlookApp.CreateItem(olMailItem)
    With oItem
        .Subject = mysubject
        .Body = Source.Sections(j).Range.Text
        Set Datarange = Maillist.Tables(1).Cell(j, 1).Range
        Datarange.End = Datarange.End - 1
        .To = Datarange
       
        'code for adding a CC recipient
         Set Datarange = Maillist.Tables(1).Cell(j, 4).Range
         Datarange.End = Datarange.End - 1
        .CC = Datarange
        'end of code for adding a CC recipient
     
           .Send
    End With
    Set oItem = Nothing
Next j
0
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 41894600
I need to know whre is graham's solution
0
 

Accepted Solution

by:
yballan earned 0 total points
ID: 41896046
I figured it out, I was reading the instructions wrong, and referring to a wrong document for e-mail addresses.
0
 

Author Closing Comment

by:yballan
ID: 41902212
I realized my misunderstanding, and was able to get the original instruction to work.
0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

This is an Add-On procedure to be used in conjunction with the code provided in Reducing EE Email Clutter using Outlook (http://www.experts-exchange.com/Software/Office_Productivity/Groupware/Outlook/A_3146-Outlook-Processing-EE-emails-on-Receive.…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

737 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