[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

MailMerge cc with VBA table issue

Posted on 2016-11-15
12
Medium Priority
?
23 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 53

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 53

Expert Comment

by:Rgonzo1971
ID: 41889661
like  Test.docx
0
Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

 

Author Comment

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

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 53

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 53

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

What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

Question has a verified solution.

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

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.
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

650 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