#1
|
|||
|
|||
Catalogue Email Merge with Delay Between Messages
Hi
I'm hoping that there is a solution. I am using the Microsoft Word Catalogue file and it has worked great in the past to send out emails. However I have an added issues that was brought to my attention. The emails are going out to fast and emails are not going to their destination using mail merge. I want to add a 20 second delay between each email leaving the inbox after I run the macro however continue using the code that I am using. Is this possible and if so how do I implement? Thank you for the help! |
#2
|
||||
|
||||
If you're referring to the Merging by Catalog/Directory to E-Mail topic in my Microsoft Word Catalogue/Directory Mailmerge Tutorial, the part of the code that actually sends the emails is only using the same merge to email process than a normal email merge would use. Hence, if your system is having difficulties at that point, they're not likely to be due to anything related to the macro. Providing for a delay at that point in the code would require a significant re-write, as a timing loop would have to be added to the structure.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Quote:
|
#4
|
||||
|
||||
Being offline doesn't affect how quickly the emails will be sent - only when they'll be sent. Adding a delay loop to the RunMerge macro cannot affect that (i.e. they'll all go out together as soon as you go online or use Send/Receive for the relevant folder).
That said, if you're already online you could try replacing the existing RunMerge macro (only) with Code:
Sub RunMerge() Application.ScreenUpdating = False Dim Doc1 As Document, Doc2 As Document, Doc3 As Document, StrDoc As String, i As Long, j As Long Set Doc1 = ThisDocument StrDoc = ThisDocument.Path & "\EmailDataSource.doc" If Dir(StrDoc) <> "" Then Kill StrDoc With Doc1.MailMerge If .State = wdMainAndDataSource Then .Destination = wdSendToNewDocument .Execute Set Doc2 = ActiveDocument End If End With Call EmailMergeTableMaker(Doc2) With Doc2 j = .Tables(1).Rows.Count - 1 .SaveAs FileName:=StrDoc, AddToRecentFiles:=False, FileFormat:=wdFormatDocument StrDoc = .FullName .Close End With Set Doc2 = Nothing Set Doc3 = Documents.Open(FileName:=Doc1.Path & "\Email Merge Main Document.doc", _ AddToRecentFiles:=False) With Doc3.MailMerge .MainDocumentType = wdEMail .OpenDataSource Name:=StrDoc, ConfirmConversions:=False, ReadOnly:=False, _ LinkToSource:=True, AddToRecentFiles:=False, Connection:="", SQLStatement:="", _ SQLStatement1:="", SubType:=wdMergeSubTypeOther If .State = wdMainAndDataSource Then '.Destination = wdSendToNewDocument .Destination = wdSendToEmail .MailAddressFieldName = "Recipient" .MailSubject = "Monthly Sales Stats" '.MailFormat = wdMailFormatHTML .MailFormat = wdMailFormatPlainText For i = 1 To j With .DataSource .FirstRecord = i .LastRecord = i .ActiveRecord = i End With .Execute Pause:=False Call Pause(10) Next i End If End With Doc3.Close SaveChanges:=False Set Doc3 = Nothing Application.ScreenUpdating = True End Sub Public Function Pause(Delay As Long) Dim Start As Long Start = Timer If Start + Delay > 86399 Then Start = 0: Delay = (Start + Delay) Mod 86400 Do While Timer > 1 DoEvents ' Yield to other processes. Loop End If Do While Timer < Start + Delay DoEvents ' Yield to other processes. Loop End Function Call Pause(10) Presently, it's set for 10 seconds. At that rate, 300 emails would take 50 minutes to send...
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Thank you! This is perfect and will meet the exchange server requirements on sending out emails. I'm ok with the 5 hours as I can set it first thing in the morning and do other work.
|
#6
|
|||
|
|||
I made the change to the code but it got unexpected results that I had to stop; an endless loop. I created a test file to see how the code change would work and the mail merge went into an endless loop of adding blank rows into the word document it created. After force stopping the macro, I noticed that it had created 90 pages of blank rows in the table. I only made changes to the RunMerge provided above. Can it be because of this line in the code?
.Execute Pause:=False |
#7
|
||||
|
||||
Which document is getting these extra tables? The changes I made to the code have no effect on the 'Email Merge Main Document.doc' or the 'EmailDataSource.doc', so if the erroneous result affects either of those, it's because of something else you've done.
The '.Execute Pause:=False' line has nothing to do with the error you're getting.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#8
|
|||
|
|||
Sorry the EmailDataSource file that the mail merge creates. I figured out why the extra rows were being generated and why the endless loop. The Excel file somehow got corrupted and had thousands of blank rows that the merge was reading as actual data. I created a new file and that issue disappeared.
So I am able to run the file without changing anything except that the email would be in HTML format and the subject, as seen below. I wanted to see it run before I changed the timing. It ran but it never generated emails to my Outlook account. It does nothing but create the EmailDataSource file and the macro ends. I verified that the code read exactly the same and the file is created successfully. .MailSubject = "Terminate" .MailFormat = wdMailFormatHTML '.MailFormat = wdMailFormatPlainText Most importantly, thank you so much for helping me with this. |
#9
|
||||
|
||||
Experiment with sending the output to a new document before trying to send to email.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#10
|
|||
|
|||
Hi Paul,
Eureka! It worked! Thank you so much. I had manually typed it in since I cannot access this website from work. I accidentally missed a line. This one is definitely solved. You're the best! Last edited by Berryblue67; 03-15-2017 at 04:54 AM. Reason: Update |
#11
|
|||
|
|||
Quote:
we are unable to do the delayed mail merge using the above quote. kindly write a mail to "nandina.manikumar@rediffmail.com" |
#12
|
||||
|
||||
As indicated in posts #2 & #4, the macro discussed in this thread is for use with my Microsoft Word Catalogue/Directory Mailmerge Tutorial. It is not for wider use. For an equivalent macro for more general use, see: https://www.msofficeforums.com/mail-...tml#post124982
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Email Merge With Delay | nevin27 | Mail Merge | 29 | 02-17-2023 09:26 AM |
Activating Unique Links in Email Messages Using a Mail Merge | User02182016 | Mail Merge | 1 | 02-23-2016 06:02 PM |
Delay After Hours Messages Automatically | kskalwat | Outlook | 0 | 03-18-2015 02:59 PM |
Catalogue e-mail merge problem | lol22 | Mail Merge | 4 | 02-15-2013 02:59 AM |
Email send delay--where? | markg2 | Outlook | 8 | 02-14-2010 03:40 PM |