Word 2007 mail merge print and email

[Bluejay07]

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"

    '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!

 

[SkipVought]

I'd do that replacement in Excel using a formula, something like this...
[tt]
=If([IsAValidAddress],[ReturnTheAddress],[ReturnTheFakeAddress])
[/tt]
so that these new columns will be the Name, Address, City/State/Zip to include in the MailMerge.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Bluejay07]

Thanks again for the response Skip, although I'm not sure I understand it.
I'm already accessing a word document and now you are recommending opening an excel document to check the address. I'm not sure how that helps.
If I do understand what you mean, are you suggesting opening the database in excel, verify data and then use the excel file as the datasource instead of using the datasource directly from the dbf file?

The data mapping of the mail merge appears to be fine. It's the actual sending of the message that I'm trying to adjust.
Since the email mail merge uses the user's outlook settings, I want to change the 'from' (sender) values.
From what I can tell, Word's email mail merge feature doesn't provide this option.
It seems like I am only able to change the subject and recipient email information.


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

 

[SkipVought]

No. I'm suggesting that your Excel database (table) have a formula as one or more of the columns (for the calculated Name, Address, City/State/Zip)

For instance, suppose my table were

Mode  Address  NewAddress
EMAIL ''       =if(Mode="EMAIL","This is the fake address or whatever you like",Address)

So ever row that has EMAIL in the Mode, you would be the "This is a fake..." text or whatever you want there, otherwise the REAL ADDRESS is put there.

THEN instead of using Address in your MailMerge, use NewAddress.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Bluejay07]

Hi Skip,
The dbf file is actually a file created in FoxBASE+ for unix. Unfortunately I am not able to alter it.
When you are referring to address in the mail merge, what field are you referring to?

The only email address field I seem to have is .MailAddressFieldName = "EMAIL", where 'EMAIL' is the recipient's email address field in the table that I am using as part of the datasource.
As I understand it, this is only used to set the recipient's email information.

All other fields that I have (address, city, state, etc.) are for the mapping of the mail merge data (so that it is customized for each letter) that appears on the letter itself. These could very easily be omitted right now giving me the mail merge basics:

With ActiveDocument.MailMerge
   .MailSubject = "Test message"    ' Email subject.
   .MailFormat = wdMailFormatHTML
   .Destination = wdSendToEmail
   .MailAddressFieldName = "EMAIL"  ' Email To Field.
   .SuppressBlankLines = True
   With .DataSource
      .FirstRecord = wdDefaultFirstRecord
      .LastRecord = wdDefaultLastRecord
   End With
   .Execute Pause:=False
End With

I'm still not sure how your suggestion helps me. My database has all of the information in it - mapped data values, recipient's email address (called 'EMAIL') and sender's email address to be used (called 'COLLEMAIL'). At this time, it's not a matter of validating the email address, just overwriting the sender's email address. The current data I'm using has valid data. If needed, the validation can be implemented after I get the sender's email changed and working.

I know you are very knowledgeable, however, I'm sorry but I'm just not following your assistance that you are providing me.
Your responses are greatfully appreciated.

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

 

[SkipVought]

sorry, I lost track of the fact you are using a FoxBASE source.

Back to a twist on where I was going several posts back. Use one MM template for EMAIL and another for MAIL. Filter the source data for EMAIL or NOT accordingly.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Bluejay07]

My apologies as well. I hadn't specified exactly of what the data source was.
I had used your suggestion, although one template seems ok to use.

My last completed posted code has two sections: one for printing the document and one for emailing the document.
The table I used had only 2 entries: one had a recipient email address and one didn't.

When I ran the code, one letter was printed and one was successfully sent by email. It's the email portion I'm now working on.
The reason I want to change the sender's email is because if someone hits 'reply', the email should be directed to the proper person.
As an analogy, I'm thinking of it like this. If a manager runs this program, he doesn't want to get any of the responses. All responses should go to his subordinate (secretary, assistant, student, whoever) and not to him.

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

 

[Bluejay07]

From what I can tell, it is not possible to change the sender's email address from a word mail merge document.

Here is the general consensus of my research:
In order to change the sender's email address, you need to adjust Outlook properties. The solution is to set a spare email address as the default account in Outlook and set the name of that account as "Sent on behalf of **** (ie. a client's name)" as well as the email address and then to change the default account back to the normal email address afterwards.

This isn't an option for me as the sender's email address is dynamic and will not be the same for every email sent. Realistically, it will most likely change after every few mail merged documents (therefore each mail merge could have dozens of sender email addresses). It's not a viable solution to create a new email account, change it to the default account, send the message, delete the new account and ensure the default account is back to the original email account for every mail merge document being created.

If anyone knows of a workaround solution to change to sender's email address using Word 2007's mail merge program, it would be appreciated.

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