Modifying Code to Send E-mail

[Justinian]

I have a daily report I need to send to my manager. Right now, my report has code to copy A1:F30 so I can paste the data into an e-mail. How do I modify this code so that I only copy rows with data instead of A1:F30?

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub Send_Email()
On Error Resume Next
Dim OutApp As Object
Dim OutMail As Object
Dim objNsp As Object
Dim colSyc As Object
Dim objSyc As Object
Dim i As Integer[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif] Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
Set objNsp = appOL.Application.GetNamespace("MAPI")
Set colSyc = objNsp.SyncObjects[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif] On Error Resume Next
With OutMail
.To = "john.smith@aol.com"
.Subject = "Daily Report - " & Format(Date, "mm/dd/yy")
.Display
' .Display ' to open a mail window with a normal 'SEND' icon available'
' .Send ' to send without displaying mail

Range("A1:F30").Select
Selection.Copy

End With[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif] For i = 1 To colSyc.Count
Set objSyc = colSyc.Item(i)
objSyc.Start
Next[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif] On Error GoTo 0[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
Set OutMail = Nothing
Set objNsp = Nothing
Set colSyc = Nothing
Set objSyc = Nothing
Set OutApp = Nothing[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]aa:
End Sub[/FONT][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
[/FONT]

 

[Worf]

Re: Help Modifying Code to Send E-mail

Here is one way to get the desired rows:

Sub Macro14()
MsgBox "Rows with data were moved to " & foo([b2:f15])  ' your range here
End Sub


Function foo$(r As Range)   ' receives the original range
Dim col%
col = r.Columns(1).Column + r.Columns.count
Range(Cells(r.Rows(1).Row, col), Cells(r.Rows(r.Rows.count).Row, col)).FormulaR1C1 = _
"=COUNTA(RC[-" & r.Columns.count & "]:RC[-1])"
Set r = r.Resize(r.Rows.count, r.Columns.count + 1)
r.AutoFilter r.Columns.count, ">0"
r.SpecialCells(xlCellTypeVisible).Copy Cells(1, col + 5)
ActiveSheet.AutoFilterMode = 0
Set r = Cells(1, col + 5).CurrentRegion
foo = r.Resize(, r.Columns.count - 1).Address
End Function

 

[Justinian]

Re: Help Modifying Code to Send E-mail

Thank you for the reply but I am not moving rows of data.

 

[Justinian]

Re: Help Modifying Code to Send E-mail

Anyone else have any ideas?

 

[Worf]

Re: Help Modifying Code to Send E-mail

That code writes the desired data to a new worksheet location, from where it can be copied to the email.
Would you prefer to copy it to memory, and then to Outlook?

 

[Justinian]

Re: Help Modifying Code to Send E-mail

My mistakle - I was not as clear as I should have been. I want to change the code below so that Excel does not copy a static range, but instead copies only rows with data:

Range("A1:F30").Select
Selection.Copy​

 

[Worf]

Re: Help Modifying Code to Send E-mail

I got that, but I need to know if I can write to the worksheet or not.
If writing is allowed, it is ready.

 

[Justinian]

Re: Help Modifying Code to Send E-mail

What do you mean by writing to the worksheet?

 

[Worf]

Re: Help Modifying Code to Send E-mail

I see two ways of doing this:

1. Extracting rows with data to another worksheet or another position on the same sheet, and then pasting this data into Outlook. This writes to the worksheet.
2. Extracting rows with data to memory, and then pasting it into the email. No worksheet will be altered.

Please pick a method.

 

[Justinian]

Re: Help Modifying Code to Send E-mail

#2 sounds good, please.