Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #16  
Old 01-29-2021, 04:33 AM
Justaguyinpa Justaguyinpa is offline Email Merge With Delay Windows 10 Email Merge With Delay Office 2016
Novice
 
Join Date: Jan 2021
Posts: 1
Justaguyinpa is on a distinguished road
Default 10-second delay between emails

I just came across this thread and it's exactly what I'm looking for, but please excuse my ignorance, I'm not a coder. Do I only copy and paste the Timed_Email_Merge code OR do I have to copy the whole string everything else you also listed. The reason I ask is that when I copy the whole string that includes the "Public Function Pause(Delay As Long)" , it actually bulk delays the emails instead of spacing them out by 10 seconds. When I only copy and paste what I included above, I receive Compile error. Please let me know your thoughts.
Reply With Quote
  #17  
Old 01-29-2021, 02:22 PM
macropod's Avatar
macropod macropod is offline Email Merge With Delay Windows 10 Email Merge With Delay Office 2016
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

You need both the Timed_Email_Merge macro and the Pause function. The bulk delay you refer to suggests you ran the Delayed_Email_Merge macro from post #13 instead.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #18  
Old 02-06-2021, 03:31 PM
RNanfro RNanfro is offline Email Merge With Delay Windows 10 Email Merge With Delay Office 2019
Novice
 
Join Date: Feb 2021
Posts: 3
RNanfro is on a distinguished road
Exclamation

Hi Paul,
I feel like an idiot because you have placed everything above in Post #8 and I feel that I am missing something in the execution. Using Office 365.

After I mail merge the info from an Excel file into the Word Doc, I press the tool bar icon to run that specific marcro in #8 at the last step (#6) of the Mail Merge Wizard and I get a Run-time error "5630"
Word cannot merge documents that can be distributed by mail or fax without a valid email address. Chose the Setup button to select a mail address data field.

When I look at the debug
.Execute Pause:=False line is highlighted.

Any thoughts?
Reply With Quote
  #19  
Old 02-06-2021, 10:06 PM
macropod's Avatar
macropod macropod is offline Email Merge With Delay Windows 10 Email Merge With Delay Office 2016
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 the error message itself clearly indicates, your mailmerge data don't include valid email addresses for all records.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #20  
Old 02-07-2021, 08:11 AM
RNanfro RNanfro is offline Email Merge With Delay Windows 10 Email Merge With Delay Office 2019
Novice
 
Join Date: Feb 2021
Posts: 3
RNanfro is on a distinguished road
Default

Sorry if I was not clear. I am a novice with macros.

This is a test with just five records and the email addresses are okay because I receive the emails without a delay when I execute the Merge Electronic mail command in the Wizard in Step 6.

I changed the value for the delay in the macro to 120 seconds instead of 10 to see if the macro would work but it fails with the above message every time so nothing is going through the macro to create a delayed sending.
Reply With Quote
  #21  
Old 02-07-2021, 02:48 PM
macropod's Avatar
macropod macropod is offline Email Merge With Delay Windows 10 Email Merge With Delay Office 2016
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

Open you workbook and press Ctrl-End. If the row selected is below your last data row, your mailmerge data don't include valid email addresses for all records. You need to explicitly delete the unused rows and re-save the workbook.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #22  
Old 02-07-2021, 03:00 PM
RNanfro RNanfro is offline Email Merge With Delay Windows 10 Email Merge With Delay Office 2019
Novice
 
Join Date: Feb 2021
Posts: 3
RNanfro is on a distinguished road
Default

Hi Paul,
Tried the CTRL-End and it took me to the last record of the five. Still the same problem of the error message and the macro stopping before the Call Pause delay. Debug shows the line highlighted below.
Code:
            .Execute Pause:=False
        End With
        Call Pause(10)
Thanks,
Rob
Attached Files
File Type: xlsx Test_Delayed_Email.xlsx (9.5 KB, 5 views)
Reply With Quote
  #23  
Old 02-07-2021, 03:19 PM
macropod's Avatar
macropod macropod is offline Email Merge With Delay Windows 10 Email Merge With Delay Office 2016
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 per post #8, did you
Quote:
go to Finish & Merge>Send Email Messages, ensure the correct field for the emails is selected and insert the email subject line, then send at least a single record manually to 'lock in' the settings.
PS: I've now edited the code so that those steps aren't needed; instead, you edit the code the reference the data field to be used for the emails, plus add the subject line.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #24  
Old 07-19-2022, 12:30 AM
irbaaz irbaaz is offline Email Merge With Delay Windows 10 Email Merge With Delay Office 2019
Novice
 
Join Date: Jul 2022
Posts: 1
irbaaz is on a distinguished road
Default

I confirm still works in 2022 with ms office windows (not able to run successfully in macosx).
Reply With Quote
  #25  
Old 09-29-2022, 02:34 AM
LouiseK LouiseK is offline Email Merge With Delay Windows 10 Email Merge With Delay Office 2021
Novice
 
Join Date: Sep 2022
Posts: 2
LouiseK is on a distinguished road
Default Trying to merge the delay macro with a dynamic subject line macro

Hi, Thank you for this macro, it works perfectly, however I would also like to add a dynamic subject line to the email, pulling from my email subject set in my excel file. I found this macro, that seems to be able to do that. My programming however is very rusty! and I can't work out where I would need to add this so it also works with the delay macro.

This is the subject macro

Code:
Dim WithEvents wdapp As Application
Dim EMAIL_SUBJECT As String
Dim FIRST_RECORD As Boolean


Private Sub Document_Open()
    Set wdapp = Application
    ThisDocument.MailMerge.ShowWizard 1   
End Sub

Private Sub Document_Close()
    Set wdapp = Nothing    
End Sub


Private Sub wdapp_MailMergeBeforeRecordMerge(ByVal Doc As Document, Cancel As Boolean)
    Dim i As Integer    
    With ActiveDocument.MailMerge    
        If FIRST_RECORD = True Then 
            EMAIL_SUBJECT = .MailSubject
            FIRST_RECORD = False
        Else .MailSubject = EMAIL_SUBJECT
        End If
        i = .DataSource.DataFields.Count        
        Do While i > 0
            .MailSubject = Replace(.MailSubject, "<" & .DataSource.DataFields(i).Name & ">", .DataSource.DataFields(i).Value, , , vbTextCompare)
            i = i - 1
        Loop
    End With      
End Sub


Private Sub wdapp_MailMergeBeforeMerge(ByVal Doc As Document, ByVal StartRecord As Long, ByVal EndRecord As Long, Cancel As Boolean)
    FIRST_RECORD = True    
End Sub

Private Sub wdapp_MailMergeAfterMerge(ByVal Doc As Document, ByVal DocResult As Document)
    ActiveDocument.MailMerge.MailSubject = EMAIL_SUBJECT     
End Sub
This is the delay macro I'm using - thank you Macropod
Code:
Sub Timed_Email_Merge()
' Merges one record at a time to email with a pre-defined delay between messages.
' Sourced from: https://www.msofficeforums.com/mail-...rge-delay.html
Application.ScreenUpdating = False
Dim i As Long
With ActiveDocument.MailMerge
  .Destination = wdSendToEmail
  .MailAddressFieldName = "Email"
  .MailSubject = "Subject"
  .SuppressBlankLines = True
  For i = 1 To .DataSource.RecordCount
    With .DataSource
      .FirstRecord = i
      .LastRecord = i
      .ActiveRecord = i
    End With
  .Execute Pause:=False
  Call Pause(10)
  Next i
End With
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

How do I use both so that the subject is pulled as a field from my spreadsheet. Any help greatly appreciated.

Last edited by macropod; 09-30-2022 at 07:59 AM. Reason: Added code tags
Reply With Quote
  #26  
Old 09-30-2022, 08:00 AM
macropod's Avatar
macropod macropod is offline Email Merge With Delay Windows 10 Email Merge With Delay Office 2016
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

The link in the second macro you posted tells you how to do that.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #27  
Old 10-03-2022, 02:23 AM
LouiseK LouiseK is offline Email Merge With Delay Windows 10 Email Merge With Delay Office 2021
Novice
 
Join Date: Sep 2022
Posts: 2
LouiseK is on a distinguished road
Default

Hi Paul,

Thank you for your reply. My apologies if I'm being stupid, I can't work out what to write after subject to call the field from the excel database rather than be the same subject line.

Any help greatly appreciated.

Best regards,
Louise
Reply With Quote
  #28  
Old 10-03-2022, 05:23 AM
macropod's Avatar
macropod macropod is offline Email Merge With Delay Windows 10 Email Merge With Delay Office 2016
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

For example:
.MailSubject = .DataFields("Subject")
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #29  
Old 02-17-2023, 09:19 AM
MurrayM MurrayM is offline Email Merge With Delay Windows 11 Email Merge With Delay Office 2021
Novice
 
Join Date: Feb 2023
Posts: 2
MurrayM is on a distinguished road
Default Adding both options together.

Sorry, moved this reply, it was supposed to be on another thread.
Reply With Quote
  #30  
Old 02-17-2023, 09:26 AM
MurrayM MurrayM is offline Email Merge With Delay Windows 11 Email Merge With Delay Office 2021
Novice
 
Join Date: Feb 2023
Posts: 2
MurrayM is on a distinguished road
Default

Paul,

I love what you have done here but is there a way to merge these two macros together to request a start date or delay the macro for x hours and then throttle the emails by sending 1 every x seconds.

Also, I was thinking it would be great if I could have the "Subject" "Date to start" or "hour delay" as well as the "seconds between emails" in the same xls document that I have the merge list of names in on a separate sheet.

Can you tell me how I might do that?
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Email Merge With Delay Catalogue Email Merge with Delay Between Messages Berryblue67 Mail Merge 11 02-23-2018 01:28 PM
Email Merge With Delay Email alert delay (driving me mad) alexb123 Outlook 1 07-18-2014 02:35 AM
Delay email to one person in a distribution list dixiesstar Outlook 0 09-13-2012 01:56 PM
delay sending email and follow up on all emails lefteris Outlook 1 05-17-2011 05:11 PM
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 02:54 PM.


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