MobileMobile | Continue

Excel Bar

Excel Bar

Your excel questions will be responsed by our excel experts within 24hrs.Our service is free.

 Forgot Pass?
 Register Now
Find
Hot Search: Vlookup Match VBA
123Next
Return Post new threads
Author: brucemc777

Where do I find the mail merge vba code? [SOLVED]

[Copy link]

14

Topics

50

Posts

146

Integral

Conqueror

Rank: 3Rank: 3

Integral
146
 Author| Post on 9-15-2016 02:24:40 | All posts



Running into a bit of an obstacle here.
As speculated, I copy all visible filtered records to another sheet that for better or worse I named MailMerge. To make it simple I filtered so only one record would show (one with my email address) along with the HeaderRowRange to Worksheets(MailMerge), which also is Sheet3.
I tried three variations on the line defining the SQL query:
    Const strQry As String = SELECT * FROM `MailMerge$`    Const strQry As String = SELECT * FROM `MailMerge`and
    Const strQry As String = SELECT * FROM `Sheet3$`in separate runs, and verified that MailMerge aka Sheet3 had the field names on row 01 and my record alone on row 02.
As none of the above worked I checked what I could. I found that wdDoc.name returned the name of the document correctly after being Set, but when I checked wdDoc.MailMerge.DataSource.RecordCount I came up with -1 every time.
In the event that I somehow messed another area of the code up, at the end of this I am posting the entire procedure as I have modified it. Are you able to advise where I have run off track?
(Note: In the code I use a comment with a mess of asterisks to flag my attention to areas that I will need to modify or will need some form of future attention)
Sub eMailMergeExperimental()
    Dim intCtr As Long
    Dim wdApp As Object
    Dim wdDoc As Object
    Dim strWorkbookName As String
    Const strQry As String = SELECT * FROM `Sheet3$`
   
    strWorkbookName = ThisWorkbook.FullName
   
    'For testing purposes have placed Hello.docx in this same directory and have hard encoded the document.
    On Error Resume Next
    Set wdApp = GetObject(, Word.Application)
    If wdApp Is Nothing Then
        Set wdApp = CreateObject(Word.Application)
    End If
    With wdApp
        'Disable alerts to prevent an SQL prompt
        .DisplayAlerts = 0 'wdAlertsNone
        
        'Open the mailmerge main document**************************************************************************************************
        Set wdDoc = .Documents.Open(ThisWorkbook.Path  \Hello.docx, _
                                    ConfirmConversions:=False, ReadOnly:=True, AddToRecentfiles:=False)
        With wdDoc
            With .MailMerge
                'Define the mailmerge type
                .MainDocumentType = 4 'wdEMail
                'Define the output
                .Destination = 2 'wdSendToEmail
                .SuppressBlankLines = True
               
                'Connect to the data source : wdOpenFormatAuto = 0; wdMergeSubTypeAccess = 1
                .OpenDataSource Name:=strWorkbookName, ReadOnly:=True, _
                                LinkToSource:=False, AddToRecentfiles:=False, _
                                Format:=0, _
                                Connection:=Provider=Microsoft.ACE.OLEDB.12.0;  _
                                            User ID=Admin;Data Source=strWorkbookName;  _
                                            Mode=Read;Extended Properties=HDR=YES;IMEX=1;, _
                                SQLStatement:=strQry, SubType:=1
               
                For intCtr = 1 To .DataSource.RecordCount
                  With .DataSource
                    .FirstRecord = intCtr
                    .LastRecord = intCtr
                    .ActiveRecord = intCtr
                    If Trim(.DataFields(Company)) =  Then Exit For
'                    If Trim(.DataFields(Last_Name)) =  Then Exit For '*********************************************************************
                  End With
                  .MailFormat = 1 'wdMailFormatHTML
                  .MailSubject = Subject Line '**********************************************************************************************
                  .MailAddressFieldName = .DataFields(ContactEml)
'                  .MailAddressFieldName = Email field
                  .Execute Pause:=False
                Next intCtr
               
                'Disconnect from the data source
                .MainDocumentType = -1 'wdNotAMergeDocument
            End With
            'Close the mailmerge main document
            .Close False
        End With
        'Restore the Word alerts
        .DisplayAlerts = -1 'wdAlertsAll
        'Display Word and the document
        .Visible = True
    End With
End Sub




Reply Support Opposition

Props Report

10

Topics

492

Posts

1102

Integral

King

Rank: 6Rank: 6

Integral
1102
Post on 9-15-2016 02:38:55 | All posts



Your post does not comply with Rule 1 of our Forum
RULES
. Your post title should accurately and concisely describe your problem, not your anticipated solution.
Use terms appropriate to a Google search. Poor thread titles, like
Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)











Reply Support Opposition

Props Report

14

Topics

50

Posts

146

Integral

Conqueror

Rank: 3Rank: 3

Integral
146
 Author| Post on 9-15-2016 02:45:58 | All posts



@ Norie - The Title was constructed at the time my first question in the thread was posed as best as I thought would correspond. Since that time through the continuous help of some very patient people, I have learned that I was barking up the wrong tree and they have continued to give me support and redirect my attention in a continuing learning process, building upon each prior work. Yes, the point we are now at does deviate from where I started; does this mean as a concept is built upon one is to change the initial title of the post? I am glad to comply; I simply am a bit confused for in the beginning the initial response was to the post I presented, correcting my confusion, and the title was commensurate with the initial post to the best of my confusion.
You yourself have personally helped me in a number of instances; I mean no disrespect, simply clarification.
edit: Interesting enough (to me), we are back to that same block from my original post seemingly having a problem in my implementation! I still truly wish I could create different instances of it with varying Word mailmerges so I could learn from them-





[b]
Last edited by brucemc777; 05-14-2016 at 09:08 PM.







Reply Support Opposition

Props Report

14

Topics

50

Posts

146

Integral

Conqueror

Rank: 3Rank: 3

Integral
146
 Author| Post on 9-15-2016 04:08:32 | All posts



I find that if I change the ReadOnly in:
        Set wdDoc = .Documents.Open(ThisWorkbook.Path  \Hello.docx, _
                                    ConfirmConversions:=False, ReadOnly:=False, AddToRecentfiles:=False)to True, it does show the recordcount = 1 and when I test wdDoc.MailMerge.DataSource.DataFields(Company) I get the correct result.
Now to figure out what else is going wrong for me as it didn't pump out the email!
@ Norie, ctd.... I attempted to change the title using edit: Advanced and did a change there, but it seemed to only affect the one post. When I returned to update findings, the title was the first one that was used upon starting this thread. Am I approaching this incorrectly? Can you assist?





[b]
Last edited by brucemc777; 05-14-2016 at 09:33 PM.







Reply Support Opposition

Props Report

2

Topics

188

Posts

466

Integral

Conqueror

Rank: 3Rank: 3

Integral
466
Post on 9-15-2016 05:17:05 | All posts



As I indicated in post #3, if you do the mailmerge setup in the normal manner using the document, you can then run the second macro I posted there to extract these details.
PS: I fail to see how the thread title was ever non-compliant.
Reply Support Opposition

Props Report

5

Topics

75

Posts

193

Integral

Conqueror

Rank: 3Rank: 3

Integral
193
Post on 11-11-2016 00:31:28 | All posts



I suggest deleting the background that you don't want to print.











Reply Support Opposition

Props Report

2

Topics

188

Posts

466

Integral

Conqueror

Rank: 3Rank: 3

Integral
466
Post on 11-11-2016 02:01:13 | All posts



Agreed. This really has nothing to do with mailmerge, per se. Once the setup is done, the unwanted background should be deleted from the mailmerge main document before executing the merge - otherwise you might end up having to delete it from every merged record.
Reply Support Opposition

Props Report

7

Topics

551

Posts

1283

Integral

King

Rank: 6Rank: 6

Integral
1283
Post on 2-11-2017 16:45:19 | All posts



Using the literal text from your post...
is there a tutorial on the proper way to do a mail merge




Reply Support Opposition

Props Report

2

Topics

23

Posts

66

Integral

Member

Rank: 2

Integral
66
Post on 2-11-2017 17:28:38 | All posts



Point taken, I just thought this site would have more detailed information not sarcasm




Reply Support Opposition

Props Report

7

Topics

551

Posts

1283

Integral

King

Rank: 6Rank: 6

Integral
1283
Post on 2-11-2017 18:23:47 | All posts



It's not sarcasm.
You posted a very general question best answered with a very general introduction. Otherwise you're really asking someone to take the time to lay out all the ins and outs of a mail merge without knowing your particular requirements so that is something best investigated by yourself in the first place.
If you had said I am trying to do 'x' or 'y' and my data looks like 'z' then comments on the pros and cons of each approach are possible.
This is sarcasm










Reply Support Opposition

Props Report

123Next
Return Post new threads

Points policy of this forum

Archiver|Mobile|Small dark house|Contact us|Excel Bar

GMT-5, 9-23-2017 13:10 , Processed in 0.178188 second(s), 17 queries .

Powered by Discuz! X3

© 2001-2013 Comsenz Inc.

!fastreply! Top !return_list!