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.
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.
Code:
[/COLOR][COLOR=#333333]Sub active()[/COLOR]
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 [COLOR=#333333]End Sub[/COLOR][COLOR=#333333]
