Results 1 to 3 of 3

Thread: Help: Send email to people where expiry date is missing in column H & I

  1. #1
    Neophyte A9kurs's Avatar
    Join Date
    Apr 2021
    Excel Version

    Help: Send email to people where expiry date is missing in column H & I

    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi Guys,

    I am looking for some help in this .
    I am trying to send emails using VBA, in attach worksheet there are some email address written in column F. When i am pressing button "send email" email will be send to email address as in column f.
    I don't want to send email to all email address . If expiry date is present in both columns H & I then person should not receive email & if there is no date in any column H & I should receive email.
    Sub active()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim sh As Worksheet
    Dim WS As Worksheet
    Dim cell As Range
    Dim FileCell As Range
    Dim rng As Range
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    Set sh = Sheets("active")
    Set WS = Sheets("Email Body")
    Set OutApp = CreateObject("Outlook.Application")
    For Each cell In sh.Columns("F").Cells.SpecialCells(xlCellTypeConstants)
        Set rng = sh.Cells(cell.Row, 6).Range("A1:J1")
        If cell.Value Like "?*@?*.?*" And _
        Application.WorksheetFunction.CountA(rng) > 0 Then
            Set OutMail = OutApp.CreateItem(0)
            With OutMail
                .to = sh.Cells(cell.Row, 6).Value
                .Subject = WS.Range("B1")
                .Body = WS.Range("B3")
                .Send 'Or use .Display/Send
            End With
            Set OutMail = Nothing
        End If
    Next cell
    Set OutApp = Nothing
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With End Sub

    Attached Files Attached Files

  2. #2
    Acolyte rollis13's Avatar
    Join Date
    Feb 2013
    Excel Version
    2016 32bit
    Just to add missing links for cross-post: LINK and LINK

  3. #3
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Ipswich, Suffolk, England
    Excel Version
    MS365 Sub (Insider) Win11
    There are two more cross-post links that are missing ...


    Please read this:

    You have posted this on many different forums, and so are potentially wasting a lot of people's time. I would not recommend that you continue to operate in this way.
    Enthusiastic self-taught user of MS Excel!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts