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
View: 393|Reply: 7

Excel to Word eMail Merge: Possible to suspend Send and force Preview? [SOLVED]

[Copy link]

14

Topics

50

Posts

146

Integral

Conqueror

Rank: 3Rank: 3

Integral
146
Post on 9-30-2016 08:49:39 | All posts |Read mode
After a tremendous amount of help from macropod and cytop we got the following code working great. I am wondering now if it is possible to stop just prior to sending out the emails so the user can preview the group in Word as if it were originally run from Word-
Sub eMailMergeExperimental(vrtSelectedItem As Variant, strSubject As String)
    Dim intCtr As Long
    Dim wdApp As Object
    Dim wdDoc As Object
    Dim strWorkbookName As String
    Dim strUserDocument As String
    Dim strTblName As String
    Const strQry As String = SELECT * FROM `MailMerge$`
    strUserDocument = CStr(vrtSelectedItem)
    strWorkbookName = ThisWorkbook.FullName
    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 = wdAlertsNone
        'Open the mailmerge main document
        Set wdDoc = .Documents.Open(strUserDocument, _
                                    ConfirmConversions:=False, ReadOnly:=False, AddToRecentfiles:=False)
        With wdDoc
        
   
            With .MailMerge
                'Define the mailmerge type
                .MainDocumentType = wdEMail
                'Define the output
                .Destination = wdSendToEmail
                .SuppressBlankLines = True
                .OpenDataSource Name:=strWorkbookName, ReadOnly:=True, _
                                LinkToSource:=False, AddToRecentfiles:=False, _
                                Format:=wdOpenFormatAuto, _
                                Connection:=Provider=Microsoft.ACE.OLEDB.12.0;  _
                                            User ID=Admin;Data Source=strWorkbookName;  _
                                            Mode=Read;Extended Properties=HDR=YES;IMEX=1;, _
                                SQLStatement:=strQry, SubType:=wdMergeSubTypeAccess
                For intCtr = 1 To .DataSource.RecordCount
                    With .DataSource
                        .FirstRecord = intCtr
                        .LastRecord = intCtr
                        .ActiveRecord = intCtr
                        If Trim(.DataFields(LastName)) =  Then Exit For
                    End With
                    .MailAddressFieldName = ContactEml
                    .MailSubject = strSubject
                    .MailFormat = wdMailFormatHTML
                    .Execute Pause:=False
                Next intCtr
                'Disconnect from the data source
                .MainDocumentType = wdNotAMergeDocument
            End With
            'Close the mailmerge main document
            .Close False
        End With
        'Restore the Word alerts
        .DisplayAlerts = wdAlertsAll
        'Display Word and the document
        .Visible = True
    End With
End SubIf it will take an entirely different approach then best forgotten, but if it isn't to involved I would like to incorporate the ability.
Thank-you!




Reply

Props Report

2

Topics

188

Posts

466

Integral

Conqueror

Rank: 3Rank: 3

Integral
466
Post on 9-30-2016 09:12:27 | All posts



If you want to preview the output, could send it to a document, not to email. Then, having previewed it and been satisfied that it is correct, execute the merge a second time and send it to email. the alternative is to disconnect your email app from the server, so all the emails go to your outbox so you can review them there...











Reply Support Opposition

Props Report

14

Topics

50

Posts

146

Integral

Conqueror

Rank: 3Rank: 3

Integral
146
 Author| Post on 9-30-2016 10:04:57 | All posts



Thank-you!
Reply Support Opposition

Props Report

14

Topics

50

Posts

146

Integral

Conqueror

Rank: 3Rank: 3

Integral
146
 Author| Post on 10-2-2016 12:47:46 | All posts



Thank-you!
Reply Support Opposition

Props Report

7

Topics

551

Posts

1283

Integral

King

Rank: 6Rank: 6

Integral
1283
Post on 2-11-2017 09:19:11 | 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 10:25:45 | 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 11:50:50 | 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

2

Topics

188

Posts

466

Integral

Conqueror

Rank: 3Rank: 3

Integral
466
Post on 2-11-2017 12:34:11 | All posts



+1
Besides which, since mailmerges are usually run from Word (regardless of the datasource), the question would probably better have been asked in Word Formatting  General: http://www.msofficeforums.com/mail-m...ps-tricks.html
or:
http://windowssecrets.com/forums/sho...ips-amp-Tricks
Reply Support Opposition

Props Report

Points policy of this forum

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

GMT-5, 11-20-2017 00:36 , Processed in 0.204939 second(s), 20 queries .

Powered by Discuz! X3

© 2001-2013 Comsenz Inc.

!fastreply! Top !return_list!