Delay sending of mail merge items?

[cmerch19602003]

This is a problem all over the web forums and the only supposed answer to this is in a VBA code book I don't really want to buy for one simple (yeah, right!) problem. I have found that hundreds of people love the mail merge feature in Word (I have office 2003 full blown) but they all seem to want the same feature I do that MS conveniently neglected to program in (go figure).

There is no built in way to throttle the sends of a large database. No way to delay or schedule the deliveries. I have tried writing rules based on properties in the word doc. but they don't work at all.

I did find the member DeferredDeliveryTime in the MailItem class of Outlook library, but virtualy all the VBA I have done to date has been in Excel and I have never dealt with passing commands or calling to other apps, so I don't know what to do with it.

Is there any way to cause Outlook to delay and/or schedule the sends of a Word mail merge by using VBA code?

I found a plug-in that looks like it will work but its packed with stuff that's already in the mail merge in Word and frankly, except for the delayed delivery problem Word is just hands down better.

Anybody? By the way be easy on me with the buzz words it's one thing about programming I never picked up on.

P.S. I put the rating on this post. I was trying to bring my bad mood up...
it helped a little...

[westconn1]

it should be possible to merge x number of records in a batch, then the next x number and so forth using

With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .MailAsAttachment = False
        .MailAddressFieldName = ""
        .MailSubject = ""
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = mystart
            .LastRecord = mystart + 10
        End With
        .Execute Pause:=True
    End With
    mystart = mystart +10
    Windows("Document1").Activate

if this is in a loop it will merge the next 10 records each time
any help??

pete

[cmerch19602003]

Thanks Westconn1

It is helpful except that I'm not sure where to put the code. I have found where the mail merge code is in word but I'm guessing this code needs to be in Outlook?

Also (I don't mean to be lazy about this but it has been a long time since I've needed to use this) it appears I will also need some code to time the sendings of each of the batches. Am I reading it correctly?

If so, I can't remember what the code is for either scheduling a particular time, or a time interval before executing the send. I haven't used it in a long time. I know I have it in one of my books but if you remember it right off it could save me about an hour. Thanks

[westconn1]

well you didn't say what you are merging to, but it sounds like emailing, which i have never done from vba, in vb i would just put the code into a timer, so as not to delay processing on the computer.

my version of word doesn't have an option to merge to email as i don't have a mapi program installed.

the easiest way is to record a macro, then edit the code as needed.
you should be able to set outlook to schedule the sending of the merged documents

[cmerch19602003]

Okay maybe I do need to clarify my explanation of what I'm doing.

Microsoft Word includes a merge to (fax, email, postal, whatever) feature. It's a great feature. I use merge to email. When you click on the final button in Word to send all your documents, they are converted to emails and placed in the Outlook outbox automatically.

There is also a feature in Outlook within the email document itself (an option button in the email document's toolbar that allows you to schedule that individual document's send time.

There is no option built into Word or the Word mail merge process that allows for scheduling of the documents being created. After the documents are processed and converted by word, they imediately show up in the outbox and begin to send. If I had superpowers of speed, it would be at this point that I could open each email in the outbox, click on the options button, and schedule the send times of each email.

There are mail merge programs that integrate with Outlook to link with a database and create, schedule, and send emails. The problem with these is that they don't work well with large databases. Here's why:

When you load a large database into one of these plug-ins, it processes each email entering personal info into each one, schedules the send, and puts the email into the out box. All good, right? Wrong! You see, while it is processing hundreds of emails and placing them in the inbox, Outlook's own processing is totally suspended! What this means is that by the time the plug-in is finished scheduling five hundred to a thousand emails and releases Outlook to process them, the scheduled times for sending all the emails has past, which means they all fly out at the same time, defeating the purpose of the plug-in! Yes, I could schedule the sends not to start until after the plug-in is finished processing. That would mean they would not send for about twelve hours (yes it takes that long for the plug-in to process them) and then it takes another ten to twelve to send them. (I have an average of 4600 per day to send). Now, I need to check responses to these emails, which I cannot do for twelve hours because Outlook is unavailable while the plug-in is processing. Conclusion? Plug-in is not a good solution. reality? It's all I have right now so I am using it while trying to find a better solution.

Why do this at all? Because every ISP has rules about how many emails can be processed per send and how many per hour or day. Mine allows 100 per send and 500 per hour. Very generous! I send 100 every 15 minutes. 4600 emails means 46 sends at 100 every 15 minutes or 11 and 1/2 hours straight that I cannot be more than 15 minutes from my computer. So I say,"Whew! I need a way to automate this!"

After all the experimenation and research I have done for the past week the answer seems to me to be that there either is no answer or the answer is VBA code that will automatically execute at the end of word mail merge process (or execute between the time the docs are converted to emails and the time they are placed in the outbox). execution of the code would place time of delivery info into each email before being placed in the outbox.

The questions (finally) are:

Is there any code that can do this?

If there is, what is the code?

Where does the code go? (I am aware that what the code is can be dependant on where it needs to go).

I've been accused in the past of always providing too much info and confusing my audience but when I cut back on the info, there isn't enough. Hope this doesn't overwhelm!

Also, I know a lot of people will be wondering why I don't just use a web based service already designed to do what I need. The answer is simple. I have tried them. All of the major email providers eventually flag the addresses of the bulk email servers as "potential spammers". It doesn't matter to the ISPs whether the server is a legit blulk mailer or not what matters to them is the potential for the bulk mailer at "whatever address" to piss off their customers. So all of them eventually get flagged and have to contact the ISP that has flagged them to reguest being put on their "white list". It's too much politics and the process seems to repeat itself every time a complaint is lodged (whether the complaint is well founded or not). So in the end, I have come to the conclusion that it is better to handle this from my own home computer where I can be in control of what is sent, where, how much and how often. I can also be in personal contact with any ISPs that may seem to be viewing me as a threat to their customers, and have already found that although there can be an occasional problem, solutions are no more than a phone call or two away. NOT so with web based bulk servers.

THERE! Now I'm SURE that was TMI! But I thought I better head off that suggestion before it came.

[cmerch19602003]

P.S. to westconn1:

Recording macros in Outlook is not possible. Outlook is the only Office program that does not allow this and also the only program that has the delay/scedule email feature. So recording the procedure to obtain the code is not possible.

Except for that it was a good idea.

Thanks.

[westconn1]

i believe it will be quite possible to do what you want, but it might have to be a stand alone program, that monitors the outbox and changes the properties of the outgoing emails, but i have never tried this, i have used cdo to create and put faxes straight into the outbox to be sent.

it would also be possible to use a timer in a vb program to send 100 emails merged from your word document every 15 minutes, but i am not sure about using a timer in vba macro, robdog might know about that one.

i don't currently have cdo, so i would have to try and find it again to test any code

pete

[cmerch19602003]

If this has piqued anyone's interest, I'm also looking at the other end of the mail merge process for an alternative solution here:

http://www.vbforums.com/showthread.php?t=413384