#1
|
|||
|
|||
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 |
#2
|
||||
|
||||
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] |
#3
|
|||
|
|||
Thanks a lot Paul. Will try.
Nevin. |
#4
|
||||
|
||||
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] |
#5
|
|||
|
|||
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 |
#6
|
||||
|
||||
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] |
#7
|
|||
|
|||
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?
|
#8
|
||||
|
||||
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 .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] |
#9
|
|||
|
|||
Dear macropod
Just Registered to Thank you 4 that Code.. so.. Thank you very much! |
#10
|
|||
|
|||
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? |
#11
|
||||
|
||||
For PC macro installation & usage instructions, see: Installing Macros
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#12
|
|||
|
|||
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! |
#13
|
||||
|
||||
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#14
|
|||
|
|||
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 |
#15
|
||||
|
||||
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] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Catalogue Email Merge with Delay Between Messages | Berryblue67 | Mail Merge | 11 | 02-23-2018 01:28 PM |
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 |