VBA code to send auto emails from Outlook App based conditions set in Excel

Sonu Singh 21 Reputation points


I am looking for VBA cod to automate a reminder sending emails. For Ex, I sent 5 emails consisting of XYZ details & i got response from 2 and rest 3 didn't responded so i want to create a VBA so that a reminder should be sent to those recipients who have not responded yet.

I maintain a tracker in spreadsheet which has 13 columns & 13th column has the recipients email ID and 12th column has the status as " Send Reminder" for which i need to send the reminder.

while sending this reminder i want all the rows from 1 to 10 should there in the body of the email with with "XYZ" comment and all the rows from 1 to 10 from the spreadsheet.

0 comments No comments
{count} votes

Accepted answer
  1. Doug Robbins - MVP 716 Reputation points

    See "Mail from Excel with Outlook on the following page of Ron de Bruin's web site:


4 additional answers

Sort by: Most helpful
  1. Doug Robbins - MVP 716 Reputation points


    Option Explicit
    Dim oOutlookApp As Object
    Dim oItem As Object
    Dim bStartApp As Boolean
    Dim strSignature As String
    Dim i As Long
    Sub SendReminders()
    Set oOutlookApp = GetObject(, "Outlook.Application")
    If Err <> 0 Then
    Set oOutlookApp = CreateObject("Outlook.Application")
    bStartApp = True
    End If
    Set oItem = oOutlookApp.CreateItem(0) 'olMailItem
    With oItem
    .BodyFormat = 1 'olFormatPlain
    strSignature = .Body
    End With
    With Sheets(1).Range("A1")
    For i = 1 To .CurrentRegion.Rows.Count - 1
    If .Offset(i, 10) = "Send Reminder" Then
    Set oItem = oOutlookApp.CreateItem(0)
    oItem.HTMLBody = "Hi " & .Offset(i, 3) & "," & vbCr & vbCr & "This is a reminder" & vbCr & vbCr & strSignature
    oItem.To = .Offset(i, 11)
    oItem.Subject = "Reminder"
    End If
    Next i
    End With
    If bStartApp = True Then
    End If
    Set oOutlookApp = Nothing
    Set oItem = Nothing
    End Sub

  2. Doug Robbins - MVP 716 Reputation points

    Did you check the Sent Items folder?

    What is the actual data that you were using? The data in the test-file.txt is very suspect.

  3. Doug Robbins - MVP 716 Reputation points

    The add-in uses VBA to do exactly what you want. However, due to the hundreds of hours spent developing it, I am not about to reveal the code that it uses.

    Have you tried saving it in the Word Startup Folder, which, by default, being a trusted location, should allow you to run it.

  4. Doug Robbins - MVP 716 Reputation points

    The add-in is based on starting with a Letters type mail merge main document to which you attached your Excel Data Source.

    To set the mail merge main document as a Letters type, access the Start Mail Merge dropdown in the Start Mail Merge section of the Mailings tab of the ribbon in Word and click on Letters. Then, use the Select Recipients facility to attach your Excel data source to the mail merge main document.

    0 comments No comments