Copying a range and pasting into email

dude0216

New Member
Joined
Nov 28, 2005
Messages
11
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
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Watch MrExcel Video

Forum statistics

Threads
1,118,674
Messages
5,573,598
Members
412,537
Latest member
Mohamed_5966
Top