Solved

MailMerge cc with VBA table issue

Posted on 2016-11-15
12
19 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
On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

 

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 June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

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.…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
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…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

691 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