Thread: [Solved] Email Merge With Delay
View Single Post
 
Old 09-29-2022, 02:34 AM
LouiseK LouiseK is offline Windows 10 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