![]() |
#16
|
|||
|
|||
![]()
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.
|
#17
|
||||
|
||||
![]()
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] |
#18
|
|||
|
|||
![]()
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? |
#19
|
||||
|
||||
![]()
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] |
#20
|
|||
|
|||
![]()
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. |
#21
|
||||
|
||||
![]()
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] |
#22
|
|||
|
|||
![]()
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) Rob |
#23
|
||||
|
||||
![]()
As per post #8, did you
Quote:
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#24
|
|||
|
|||
![]()
I confirm still works in 2022 with ms office windows (not able to run successfully in macosx).
|
#25
|
|||
|
|||
![]()
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 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 |
#26
|
||||
|
||||
![]()
The link in the second macro you posted tells you how to do that.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#27
|
|||
|
|||
![]()
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 |
#28
|
||||
|
||||
![]()
For example:
.MailSubject = .DataFields("Subject")
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#29
|
|||
|
|||
![]()
Sorry, moved this reply, it was supposed to be on another thread.
|
#30
|
|||
|
|||
![]()
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? |
![]() |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
![]() |
Berryblue67 | Mail Merge | 11 | 02-23-2018 01:28 PM |
![]() |
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 |