1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Email reminder from excel to outlook

Discussion in 'Business Applications' started by Hoppy1958, Jul 22, 2018.

Thread Status:
Not open for further replies.
  1. Hoppy1958

    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.
     

    Attached Files:

  2. DataBase

    DataBase

    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: Jul 25, 2018
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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1213338

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice