Email reminder from excel to outlook

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Hoppy1958

Thread Starter
Joined
Jul 22, 2018
Messages
1
I have been given a macro to send reminder emails from Excel to Outlook.
The macro looks OK and appears to work, however, it only sends one email.
Can anyone help solve my problem as I need to be able to send multiple emails from the worksheet.
 

Attachments

Joined
Jan 15, 2004
Messages
770
Here is an alternative way to achieve this, I have tested it on your workbook and it works. Unfortunately I cannot upload a working workbook, just replace your code with this..


Code:
Dim iTo, iSubject, iBody As String
Dim Bcell As Range

Sub Workbook_Open()
   
    iBody = Empty
   
    For Each Bcell In Sheet1.Range("A2", Sheet1.Range("A" & Rows.Count).End(xlUp))
                   
        If LCase(Bcell.Offset(0, 9)) <> "y" Then
            If Bcell.Offset(0, 3) - 7 <= Date Then
                iTo = Bcell.Offset(0, 15)
                iSubject = "Registration " & Bcell.Offset(0, 2) & " is due on Due date " & Bcell.Offset(0, 3)
                iBody = "Dear " & Bcell.Offset(0, 14) & vbNewLine & vbNewLine & "Please send a reminder to the person responsible for the vehicle"
               
                SendEmail
               
            End If
        End If

    Next Bcell
     
End Sub

Private Sub SendEmail()

    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
       
    With OutMail
        .To = iTo
        .CC = iCC
        .BCC = ""
        .Subject = iSubject
        .Body = iBody
        'You can add a file like this
        '.Attachments.Add ("C:\test.txt")
        .Display 'or use .Send to automatically send without displaying
    End With

    iTo = vbNullString
    iBody = vbnullsting
    iSubject = vbNullString
   
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing

End Sub
 
Last edited:
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Top