Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-07-2018, 04:31 PM
nevin27 nevin27 is offline Email Merge With Delay Windows 7 64bit Email Merge With Delay Office 2010 64bit
Novice
Email Merge With Delay
 
Join Date: Feb 2018
Posts: 4
nevin27 is on a distinguished road
Default Email Merge With Delay

Hi Paul,



I am trying to use the Macro in http://www.msofficeforums.com/mail-m...-messages.html with MS Word for adding delays between Mail merge messages sent from Outlook. My apologies in advance for my knowledge level in VBA.

I tried pasting the code from this thread in the VBA editor and I get an Complie error: Sub or Function not defined. This error is for the function Call EmailMergeTableMaker(Doc2)

How do I define this function? Is the code in this thread complete or am I missing something?

Thanks for your help and patience.

Nevin.

Last edited by macropod; 02-21-2018 at 04:28 AM. Reason: Split from www.msofficeforums.com/mail-merge/34696-mail-merge-delay-between-messages.html
Reply With Quote
  #2  
Old 02-07-2018, 06:47 PM
macropod's Avatar
macropod macropod is offline Email Merge With Delay Windows 7 64bit Email Merge With Delay 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

The EmailMergeTableMaker macro is part of a larger process in the Merging by Catalog/Directory to E-Mail topic in my Microsoft Word Catalogue/Directory Mailmerge Tutorial. See:
https://www.msofficeforums.com/mail-...-tutorial.html
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 02-08-2018, 07:47 AM
nevin27 nevin27 is offline Email Merge With Delay Windows 7 64bit Email Merge With Delay Office 2010 64bit
Novice
Email Merge With Delay
 
Join Date: Feb 2018
Posts: 4
nevin27 is on a distinguished road
Default

Thanks a lot Paul. Will try.

Nevin.
Reply With Quote
  #4  
Old 02-08-2018, 01:25 PM
macropod's Avatar
macropod macropod is offline Email Merge With Delay Windows 7 64bit Email Merge With Delay 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

Perhaps you could explain what you're trying to do, since the EmailMergeTableMaker macro is for a particular non-standard merge process that may have no relevance to your needs.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 02-20-2018, 07:37 AM
nevin27 nevin27 is offline Email Merge With Delay Windows 7 64bit Email Merge With Delay Office 2010 64bit
Novice
Email Merge With Delay
 
Join Date: Feb 2018
Posts: 4
nevin27 is on a distinguished road
Default

Hey Paul,

My apologies was out of office for a few days, hence could not reply earlier.
You are right, I may not need the EmailMergeTableMaker macro. All I am trying to do is send out a standard word document to around 300 users. I have an Excel file with 3 field setup; First, Last and EMail. The only merge field that I am using in my MailMerge Word doc is "First". Rest of the word doc is standard.

I also want to insert a delay timer between each email message that is sent out, say like 90 seconds. Given my limited knowledge, I tried amalgamating the above code with the Catalogue Mail Merge code, per below. I did this because i need the timer. Per the catalogue tutorial, all my data is in the "Email Merge Main Document.doc" and data source is in Catalogue Merge Data.xls, all saved in the same folder along with other documents that were in the catalogue mail merge zip.

The below macro fails in the EmailMergeTableMaker function on the ".Paragraphs(1).Range.Delete" line; saying "Object variable or with block variable not set".

Thanks a ton for helping out Paul.

Regards,

Nevin.

Code:
Sub RunMerge()
Application.ScreenUpdating = False
Dim Doc1 As Document, Doc2 As Document, Doc3 As Document, StrDoc As String
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
    For i = 1 To j
      With .DataSource
        .FirstRecord = i
        .LastRecord = i
        .ActiveRecord = i
      End With
      .Execute Pause:=False
      Call Pause(90)
    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

Sub EmailMergeTableMaker(DocName As Document)
Dim oTbl As Table, i As Integer, j As Integer, oRow As Row, oRng As Range, strTxt As String
With DocName
  .Paragraphs(1).Range.Delete
  Call TableJoiner
  For Each oTbl In .Tables
  j = 2
    With oTbl
      i = .Columns.Count - j
      For Each oRow In .Rows
        Set oRng = oRow.Cells(j).Range
        With oRng
.MoveEnd Unit:=wdCell, Count:=i
          .Cells.Merge
          strTxt = Replace(.Text, vbCr, vbTab)
          On Error Resume Next
          If Len(strTxt) > 1 Then .Text = Left(strTxt, Len(strTxt) - 2)
        End With
      Next
    End With
  Next
  For Each oTbl In .Tables
    For i = 1 To j
      oTbl.Columns(i).Cells.Merge
    Next
  Next
  With .Tables(1)
    .Rows.Add BeforeRow:=.Rows(1)
    .Cell(1, 1).Range.Text = "Recipient"
    .Cell(1, 2).Range.Text = "Data"
  End With
  .Paragraphs(1).Range.Delete
  Call TableJoiner
End With
Set oRng = Nothing
End Sub
Private Sub TableJoiner()
Dim oTbl As Table
For Each oTbl In ActiveDocument.Tables
  With oTbl.Range.Next
    If .Information(wdWithInTable) = False Then .Delete
  End With
Next
End Sub

Last edited by macropod; 02-20-2018 at 01:55 PM. Reason: Added code tags
Reply With Quote
  #6  
Old 02-20-2018, 02:14 PM
macropod's Avatar
macropod macropod is offline Email Merge With Delay Windows 7 64bit Email Merge With Delay 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 not doing a catalogue/directory merge - which seems to be the case - the procedures in my Microsoft Word Catalogue/Directory Mailmerge Tutorial are not applicable to what you're trying to achieve. And, as the macro in post #4 relates to that tutorial, neither is it applicable to what you're trying to achieve. Although the procedures in my Microsoft Word Catalogue/Directory Mailmerge Tutorial could be used for outputting single records and emailed periodically using the macro in post #4, it's complete overkill for that. As for the error message you're getting, that suggests you haven't done the setup for your mailmerge main document as described in the tutorial.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 02-20-2018, 02:20 PM
nevin27 nevin27 is offline Email Merge With Delay Windows 7 64bit Email Merge With Delay Office 2010 64bit
Novice
Email Merge With Delay
 
Join Date: Feb 2018
Posts: 4
nevin27 is on a distinguished road
Default

Thanks Paul. I came across this thread based on the title: "Mail Merge with Delay Between Messages". Is there any post / thread you recommend on this forum that could help me simply parse through a list of contact email addresses in Excel and send those emails periodically through Outlook?
Reply With Quote
  #8  
Old 02-21-2018, 04:41 AM
macropod's Avatar
macropod macropod is offline Email Merge With Delay Windows 7 64bit Email Merge With Delay 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

I believe the following should meet your needs:
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-merge/38282-email-merge-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
Note that you need to edit the lines:
.MailAddressFieldName = "Email"
and:
.MailSubject = "Subject"
to ensure the correct field for the email addresses and the desired email subject line, respectively, are employed.

You can vary the delay by changing the value in:
Call Pause(10)
Presently, it's set for 10 seconds. At that rate, your 300 emails would take 50 minutes to send. With your suggested 90-second intervals, it would take 7.5 hours...

For PC macro installation & usage instructions, see: Installing Macros
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #9  
Old 11-22-2018, 03:29 AM
DerRichtige DerRichtige is offline Email Merge With Delay Windows 10 Email Merge With Delay Office 2010
Novice
 
Join Date: Nov 2018
Posts: 1
DerRichtige is on a distinguished road
Default

Dear macropod


Just Registered to Thank you 4 that Code..

so..
Thank you very much!
Reply With Quote
  #10  
Old 09-18-2019, 02:46 AM
Seko55 Seko55 is offline Email Merge With Delay Windows 10 Email Merge With Delay Office 2013
Novice
 
Join Date: Sep 2019
Posts: 1
Seko55 is on a distinguished road
Default

Hey Macropod,

thanks for the code.
I have the same problem as Nevin27.

I use Office 2013 and must send an html email to around 230 people.

My question is, how can i start this macro? When I create a new macro with your code in Word und press execute, it doesn't do anything.

What am I doing wrong?
Reply With Quote
  #11  
Old 09-18-2019, 04:01 PM
macropod's Avatar
macropod macropod is offline Email Merge With Delay Windows 7 64bit Email Merge With Delay 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

For PC macro installation & usage instructions, see: Installing Macros
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #12  
Old 04-20-2020, 10:35 AM
billyqureshi billyqureshi is offline Email Merge With Delay Mac OS X Email Merge With Delay Office 2019
Novice
 
Join Date: Apr 2020
Posts: 1
billyqureshi is on a distinguished road
Default

hi @macropod
ive come across this and it is very close to a script i require.
Would you be kind enough to tell me how to amend this so that all the emails from my merge to mail are delayed by x hours? they can be sent in one batch - just need a delay.
thank you in advance!
Reply With Quote
  #13  
Old 04-20-2020, 02:46 PM
macropod's Avatar
macropod macropod is offline Email Merge With Delay Windows 7 64bit Email Merge With Delay 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

What you're asking is to effectively delay the mailmerge's execution, not to space out the sending of those emails. That is quite a different proposition, for which you might use something like:
Code:
Sub Delayed_Email_Merge()
' Delays a merge to email by a pre-defined period.
' Sourced from: https://www.msofficeforums.com/mail-merge/38282-email-merge-delay.html
Call Pause(3600 * 1)
With ActiveDocument.MailMerge
  .Destination = wdSendToEmail
  .MailAddressFieldName = "Email"
  .MailSubject = "Subject"
  .SuppressBlankLines = True
  .Execute Pause:=False
End With
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
In the above code, '3600 * 1' represents 1 hour (i.e. 3600 seconds). Change the '1' to whatever number of hours you require.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #14  
Old 06-12-2020, 10:38 AM
hannafreeiz hannafreeiz is offline Email Merge With Delay Windows 8 Email Merge With Delay Office 2016
Novice
 
Join Date: Jun 2020
Posts: 1
hannafreeiz is on a distinguished road
Default

Please can the code in post 8 be applied on any word document for mailmerge. I tried and it did not work. The messages were all sent without any delay.

What i did was prepare my Mail added recipients in csv. Then pressed ctrl F8, Create and then i paste your code and save document as vba doc. then restart it run the macro and send the mail.

Thanks for your help. Am new to this please what am i missing
Reply With Quote
  #15  
Old 06-12-2020, 02:45 PM
macropod's Avatar
macropod macropod is offline Email Merge With Delay Windows 7 64bit Email Merge With Delay 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

The code works as described - there is a 10-second delay between emails, which start being sent immediately you run the macro.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
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 12:50 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