Modifying Code to Send E-mail

Justinian

Well-known Member
Joined
Aug 9, 2009
Messages
1,551
Office Version
  1. 365
Platform
  1. Windows
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]
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Re: Help Modifying Code to Send E-mail

Here is one way to get the desired rows:

Code:
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
 
Upvote 0
Re: Help Modifying Code to Send E-mail

Thank you for the reply but I am not moving rows of data.
 
Upvote 0
Re: Help Modifying Code to Send E-mail

Anyone else have any ideas?
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Re: Help Modifying Code to Send E-mail

What do you mean by writing to the worksheet?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top