Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Word 2007 mail merge print and email

Status
Not open for further replies.
Bluejay07

Bluejay07

Programmer
Mar 9, 2007
780
CA
Hello,

I've been given the task to modify some mail merge code by adding email functionality.
Here is my problem:
The company wants to print the documents that do not have an email address and email those that do.
I'm not sure how to do this since there is no loop to verify
"Application.ActiveDocument.MailMerge.DataSource.DataFields("Email").value"

Code: 
    'Do mailmerge.
    With ActiveDocument.MailMerge
        .OpenDataSource Name:="SourceName"    'Specify the data source here
        .Destination = wdSendToNewDocument
        .Execute
    End With
    
    'Close mailmerge document
    Windows(DocName).Close wdDoNotSaveChanges

    'Print and quit (Print only documents that do not have an email address)
    Application.PrintOut
    ActiveDocument.SaveAs "C:\Letters1.doc"
    Application.Quit
    
    ' How do I add this.  Only send if an email address exists.
    With ActiveDocument.MailMerge
        .Destination = wdSendToEmail
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With

Any help would be greatly appreciated.

If at first you don't succeed, then sky diving wasn't meant for you!
 
Sort by date Sort by votes
Does anyone have any suggestions on how I can accomplish sending either a mail merge item to the printer or email?

If at first you don't succeed, then sky diving wasn't meant for you!
 
Upvote 0 Downvote
hi,

Just query your list and return either eMail addresses or otherwise and process each of these lists appropriately.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Thanks for the response Skip.
As this is my first attempt with this type of programming, do you have a simple example I can follow?

If at first you don't succeed, then sky diving wasn't meant for you!
 
Upvote 0 Downvote
Would it be something like this or am I completely wrong?

Code: 
    If Trim$(Application.ActiveDocument.MailMerge.DataSource.DataFields("Email").Value) = vbNullString Then
        With ActiveDocument.MailMerge
            .OpenDataSource Name:="SourceName"    'Specify the data source here
            .Destination = wdSendToNewDocument
            .Execute
        End With
        
        'Close mailmerge document
        Windows(DocName).Close wdDoNotSaveChanges
    
        'Print and quit (Print only documents that do not have an email address)
        Application.PrintOut
        ActiveDocument.SaveAs "C:\Letters1.doc"
    End If
    
    If Trim$(Application.ActiveDocument.MailMerge.DataSource.DataFields("Email").Value) <> vbNullString Then
        ' Only send if an email address exists.
        With ActiveDocument.MailMerge
            .Destination = wdSendToEmail
            .SuppressBlankLines = True
            With .DataSource
                .FirstRecord = wdDefaultFirstRecord
                .LastRecord = wdDefaultLastRecord
            End With
            .Execute Pause:=False
        End With
    End If

If at first you don't succeed, then sky diving wasn't meant for you!
 
Upvote 0 Downvote
Here is another attempt.
Can anyone provide feedback whether this is a proper way to do this or is there a better way?
I'm not even sure if the syntax is correct.

Code: 
 With ActiveDocument.MailMerge
     .OpenDataSource Name:="SourceName"    'Specify the data source here
     .Destination = wdSendToNewDocument
     .Execute
 End With
    
 For i = 1 To ActiveDocument.Sections.Count - 1
     If Trim$(Application.ActiveDocument.MailMerge.DataSource.DataFields("Email").Value) = vbNullString Then
         ' Only print those that do not have an email address.
         ActiveDocument.Sections(i).Application.PrintOut
     Else
         If Trim$(Application.ActiveDocument.MailMerge.DataSource.DataFields("Email").Value) <> vbNullString Then
             ' Only send if an email address exists.
                
             With ActiveDocument.MailMerge
                 .Destination = wdSendToEmail
                 .SuppressBlankLines = True
                 With .DataSource
                     .FirstRecord = .ActiveRecord
                     .LastRecord = .ActiveRecord
                 End With
                 .Execute Pause:=False
             End With
            
         End If
     End If
 Next i
           
 'Close mailmerge document
 Windows(DocName).Close wdDoNotSaveChanges
 ActiveDocument.SaveAs "C:\Letters1.doc"
        
 Application.Quit

Thank you.

If at first you don't succeed, then sky diving wasn't meant for you!
 
Upvote 0 Downvote
what is the issue with your last posting? Are you getting the results you intend?

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
It is untested as I don't know if I am referencing the correct fields or properly?

If at first you don't succeed, then sky diving wasn't meant for you!
 
Upvote 0 Downvote
Why is it untested? You can certainly verify (test) that much more easily than I can at this distance.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Sorry Skip.

I am now getting an error on the first check for the email field.

Code: 
 If Trim$(Application.ActiveDocument.MailMerge.DataSource.DataFields("Email").Value) = vbNullString Then

Running the code I get: Runtime error '5852': Requested object is not available.

From the immediate window (without = vbnullstring) I get: Compile Error. Expression expected.

If at first you don't succeed, then sky diving wasn't meant for you!
 
Upvote 0 Downvote
Word_VBA_Help said:
vbNullString String having value 0 Not the same as a zero-length string (""); used for calling external procedures
Click to expand...
Is this what you intend?

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
The email field will either be blank (empty) or will have an email address.
vbnullstring should take care of this.

To me it seems like the datasource is opening but the index doesn't get moved to the first record. Maybe?

If at first you don't succeed, then sky diving wasn't meant for you!
 
Upvote 0 Downvote
I set up a test, added a MailMerge Email field, and ran you code sucessfully.

Can't understand why your code cratered on that statement!

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Hi Skip,

I think I might have a possible reason why I'm having problems.
My datasource is a .dbf file. This hasn't been an issue in the past to create the mail merge and print, although do you think it's a problem now because I am trying to access a field from the .dbf table?



If at first you don't succeed, then sky diving wasn't meant for you!
 
Upvote 0 Downvote
Can you use this .dbf file as a source for MailMerge MANUALLY in Word?

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Yes, I just tried it. I had no problems selecting the recipients from 'Use Existing List' and then sending email worked alright as well.

If at first you don't succeed, then sky diving wasn't meant for you!
 
Upvote 0 Downvote
I just recorded a new macro and it has the connection string added.
I will try a few more things and report back.

If at first you don't succeed, then sky diving wasn't meant for you!
 
Upvote 0 Downvote
I've made some progress however I'm now encountering another issue.
Manually sending an email, there is no issue although when sending an email from code, I now get runtime error '5630' "Word cannot merge documents that can be distributed by mail or fax without a valid mail address. Choose the Setup button to select a mail address data field."

I've verified the following returns 1 row with a valid email address
Code: 
ActivedDcument.MailMerge.DataSource.RecordCount

I've also tried mapping fields with no luck
Code: 
.MailAddressFieldName = .DataSource.DataFields("EMAIL")
.DataSource.MappedDataFields(wdAddress1).DataFieldIndex = .DataSource.DataFields("DADDR1").Index
.DataSource.MappedDataFields(wdCity).DataFieldIndex = .DataSource.DataFields("DCITY").Index
.DataSource.MappedDataFields(wdState).DataFieldIndex = .DataSource.DataFields("DPROV").Index
.DataSource.MappedDataFields(wdPostalCode).DataFieldIndex = .DataSource.DataFields("DPCODE").Index
.DataSource.MappedDataFields(wdEmailAddress).DataFieldIndex = .DataSource.DataFields("EMAIL").Index

I've even removed all mapped fields from the document so that it is only text and I am still not able to send an email.

Any suggestions on what could be blocking the sending of the message?

If at first you don't succeed, then sky diving wasn't meant for you!
 
Upvote 0 Downvote
I changed the line mailaddressfieldname to
Code: 
.MailAddressFieldName = "EMAIL"
and it worked. I currently have the sections separated since that is how I got it to work.

Now I need to know how to change the 'From' name and email address.
Here is the code so far
Code: 
    'Construct filename
    Dim DocName$
    DocName = ActiveDocument.Name
    
    Dim DocNamePos As Integer
    DocNamePos = InStr(1, DocName, ".")
    DocNamePos = DocNamePos - 1
    
    Dim DocNameCut As String
    If (DocNamePos > -1) Then
        DocNameCut = Left(DocName, DocNamePos)
    Else
        DocNameCut = DocName
    End If

    ' Only print those that do not have an email address.  
    ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
    ActiveDocument.MailMerge.OpenDataSource Name:="U:\outsource\" & DocNameCut & ".dbf", _
        ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
        AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
        WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
        Format:=wdOpenFormatAuto, Connection:= _
        "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=U:\outsource;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=18;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=" _
        , SQLStatement:="SELECT * FROM `" & DocNameCut & "` WHERE EMAIL = ''", SQLStatement1:="", SubType:= _
        wdMergeSubTypeAccess


         Application.PrintOut
    
    ' Only send if an email address exists.
    ActiveDocument.MailMerge.MainDocumentType = wdEMail
    ActiveDocument.MailMerge.OpenDataSource Name:="U:\outsource\" & DocNameCut & ".dbf", _
        ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
        AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
        WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
        Format:=wdOpenFormatAuto, Connection:= _
        "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=U:\outsource;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=18;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=" _
        , SQLStatement:="SELECT * FROM `" & DocNameCut & "` WHERE EMAIL > ''", SQLStatement1:="", SubType:= _
        wdMergeSubTypeAccess             
                
       With ActiveDocument.MailMerge
          .MailSubject = "Test message"
          .MailFormat = wdMailFormatHTML
           .Destination = wdSendToEmail
           .MailAddressFieldName = "EMAIL"
           .SuppressBlankLines = True
           .DataSource.MappedDataFields(wdAddress1).DataFieldIndex = .DataSource.DataFields("DADDR1").Index
           .DataSource.MappedDataFields(wdCity).DataFieldIndex = .DataSource.DataFields("DCITY").Index
           .DataSource.MappedDataFields(wdState).DataFieldIndex = .DataSource.DataFields("DPROV").Index
           .DataSource.MappedDataFields(wdPostalCode).DataFieldIndex = .DataSource.DataFields("DPCODE").Index
           .DataSource.MappedDataFields(wdEmailAddress).DataFieldIndex = .DataSource.DataFields("EMAIL").Index
           With .DataSource
               .FirstRecord = wdDefaultFirstRecord
               .LastRecord = wdDefaultLastRecord
           End With
           .Execute Pause:=False
       End With
            
           
    'Close mailmerge document
    Windows(DocName).Close wdDoNotSaveChanges
    'Use below line for word 2010 and 2013 instead of above
    'Application.Documents(DocName).Close (Word.WdSaveOptions.wdDoNotSaveChanges)
 
   'ActiveDocument.SaveAs "C:\Letters1.doc"
   'Application.Quit

If at first you don't succeed, then sky diving wasn't meant for you!
 
Upvote 0 Downvote
Does anyone know how I can continue to use the mail merge email functionality with the option to change the sender (FROM) information?

Basically this mail merge will be available by anyone to process, although I want the sender information to be the same for everyone, regardless of who sends the message. I want to overwrite the actual sender's information to someone else. The exact sender's name and email address are fields in the datasource.

Thanks.

If at first you don't succeed, then sky diving wasn't meant for you!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Andrzejek
  • Question
Open Word as ReadOnly 1
Replies
4
Views
160
Andrzejek
Andrzejek
Andrzejek
  • Question
Hyperlinks in Word cut at #
Replies
2
Views
172
Andrzejek
Andrzejek
gussy81
  • Locked
  • Question
mail merge with attachments 2
2
Replies
20
Views
647
Andrzejek
Andrzejek
remeng
  • Locked
  • Solved
Combine two working macros into one for MS Word 1
Replies
7
Views
852
strongm
strongm
remeng
  • Locked
  • Helpful tip
Microsoft Office 365 Word Macro Causing a Project is unviewable error
Replies
1
Views
900
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top