I am trying to look at dates in a column. If the date in column G is equal to today plus 7 days, I want to copy the entire row (Cells A thru J) and paste it into a email. After looking at the entire column and extracting the information I need, I then want to send the email.
Below is the code I have so far, the two problems I have are, I can not copy and paste the selected row, and I am unsure on how to loop thru the entire column looking at the dates before I send the email.
Sub Macro1()
Dim olApp As Outlook.Application
Dim olMail As MailItem
Range("G2").Select
Do Until ActiveCell.Value = "stop"
If ActiveCell.Value = Date + 7 Then
ActiveCell.Offset(0, -6).Range("A1:K1").Select
'Selection.Copy
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
With olMail
'Change email address here **********************************
.To = "myemailaddress.com here"
.Subject = ""
'Change name ****************
.Body = Range("A1:K1").Value
.Send 'Or use Display
Set olMail = Nothing
End With
Set olApp = Nothing
Application.ScreenUpdating = True
'Do Until ActiveCell.Value = "stop"
'ActiveCell.Offset(0, 0).Range("G2").Select
ActiveCell.Offset(1, 0).Select
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub
Below is the code I have so far, the two problems I have are, I can not copy and paste the selected row, and I am unsure on how to loop thru the entire column looking at the dates before I send the email.
Sub Macro1()
Dim olApp As Outlook.Application
Dim olMail As MailItem
Range("G2").Select
Do Until ActiveCell.Value = "stop"
If ActiveCell.Value = Date + 7 Then
ActiveCell.Offset(0, -6).Range("A1:K1").Select
'Selection.Copy
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
With olMail
'Change email address here **********************************
.To = "myemailaddress.com here"
.Subject = ""
'Change name ****************
.Body = Range("A1:K1").Value
.Send 'Or use Display
Set olMail = Nothing
End With
Set olApp = Nothing
Application.ScreenUpdating = True
'Do Until ActiveCell.Value = "stop"
'ActiveCell.Offset(0, 0).Range("G2").Select
ActiveCell.Offset(1, 0).Select
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub