Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-10-2017, 01:57 PM
Berryblue67 Berryblue67 is offline Catalogue Email Merge with Delay Between Messages Windows 10 Catalogue Email Merge with Delay Between Messages Office 2010 64bit
Novice
Catalogue Email Merge with Delay Between Messages
 
Join Date: Mar 2017
Posts: 6
Berryblue67 is on a distinguished road
Default 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!


Reply With Quote
  #2  
Old 03-10-2017, 11:39 PM
macropod's Avatar
macropod macropod is offline Catalogue Email Merge with Delay Between Messages Windows 7 64bit Catalogue Email Merge with Delay Between Messages Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #3  
Old 03-11-2017, 11:49 AM
Berryblue67 Berryblue67 is offline Catalogue Email Merge with Delay Between Messages Windows 10 Catalogue Email Merge with Delay Between Messages Office 2010 64bit
Novice
Catalogue Email Merge with Delay Between Messages
 
Join Date: Mar 2017
Posts: 6
Berryblue67 is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
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.
Hi yes, this part works great. And I agree it is using the existing process of mail merge. It works extremely well. The problem is how fast the email are released. I use it to generate 200 to 300 emails but even if I attempt to work offline, they are still quickly leaving the outbox. I need a way to slow it down to maybe 1 every 10 to 20 seconds. I know nothing about VBA so that is the challenge.
Reply With Quote
  #4  
Old 03-12-2017, 06:09 AM
macropod's Avatar
macropod macropod is offline Catalogue Email Merge with Delay Between Messages Windows 7 64bit Catalogue Email Merge with Delay Between Messages Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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
You can vary the delay by changing the value in:
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]
Reply With Quote
  #5  
Old 03-12-2017, 09:52 AM
Berryblue67 Berryblue67 is offline Catalogue Email Merge with Delay Between Messages Windows 10 Catalogue Email Merge with Delay Between Messages Office 2010 64bit
Novice
Catalogue Email Merge with Delay Between Messages
 
Join Date: Mar 2017
Posts: 6
Berryblue67 is on a distinguished road
Thumbs up

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.
Reply With Quote
  #6  
Old 03-13-2017, 09:38 PM
Berryblue67 Berryblue67 is offline Catalogue Email Merge with Delay Between Messages Windows 10 Catalogue Email Merge with Delay Between Messages Office 2010 64bit
Novice
Catalogue Email Merge with Delay Between Messages
 
Join Date: Mar 2017
Posts: 6
Berryblue67 is on a distinguished road
Default

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
Reply With Quote
  #7  
Old 03-14-2017, 04:54 AM
macropod's Avatar
macropod macropod is offline Catalogue Email Merge with Delay Between Messages Windows 7 64bit Catalogue Email Merge with Delay Between Messages Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #8  
Old 03-14-2017, 05:21 PM
Berryblue67 Berryblue67 is offline Catalogue Email Merge with Delay Between Messages Windows 10 Catalogue Email Merge with Delay Between Messages Office 2010 64bit
Novice
Catalogue Email Merge with Delay Between Messages
 
Join Date: Mar 2017
Posts: 6
Berryblue67 is on a distinguished road
Default

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.
Reply With Quote
  #9  
Old 03-14-2017, 07:28 PM
macropod's Avatar
macropod macropod is offline Catalogue Email Merge with Delay Between Messages Windows 7 64bit Catalogue Email Merge with Delay Between Messages Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Experiment with sending the output to a new document before trying to send to email.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #10  
Old 03-14-2017, 08:35 PM
Berryblue67 Berryblue67 is offline Catalogue Email Merge with Delay Between Messages Windows 10 Catalogue Email Merge with Delay Between Messages Office 2010 64bit
Novice
Catalogue Email Merge with Delay Between Messages
 
Join Date: Mar 2017
Posts: 6
Berryblue67 is on a distinguished road
Default

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
Reply With Quote
  #11  
Old 02-23-2018, 06:09 AM
immani2 immani2 is offline Catalogue Email Merge with Delay Between Messages Windows 10 Catalogue Email Merge with Delay Between Messages Office 2010 64bit
Novice
 
Join Date: Feb 2018
Posts: 1
immani2 is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
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...
We have tried using your code but we felt some part of the code is missing, kindly can you help us in solving the issues.

we are unable to do the delayed mail merge using the above quote.

kindly write a mail to "nandina.manikumar@rediffmail.com"
Reply With Quote
  #12  
Old 02-23-2018, 01:28 PM
macropod's Avatar
macropod macropod is offline Catalogue Email Merge with Delay Between Messages Windows 7 64bit Catalogue Email Merge with Delay Between Messages Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Catalogue Email Merge with Delay Between Messages Email Merge With Delay nevin27 Mail Merge 29 02-17-2023 09:26 AM
Catalogue Email Merge with Delay Between Messages 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

Other Forums: Access Forums

All times are GMT -7. The time now is 04:47 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft