Email Range which are = 0, Excel to Outlook

[Legacy 185509]

Hi
I need to email the cells which are = 0 to user using Outlook
please help me how can I go by
I have about more than 125 cells, some are = 0, some are > 0
please help
Thank you

 

[Trevor G]

Which version of Excel are you using?

First stage would be to look at recording a macro to filter the column that has the values in them and set the filter to 0 (Zero), then look to copy the range to a separate workbook and then use the Send Email option from there.

This can all be recorded but you would then need to edit the Code to extend the range as necessary.

If you can show some sample data and state which column and who it has to be sent to (A made up email address will be fine).

Here is some sample code which I have just recorded.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Macro1()<br><SPAN style="color:#007F00">'</SPAN><br><SPAN style="color:#007F00">' Macro1 Macro</SPAN><br><SPAN style="color:#007F00">'</SPAN><br><br><SPAN style="color:#007F00">'</SPAN><br>    Selection.AutoFilter<br>    ActiveSheet.Range("$A$1:$B$5").AutoFilter Field:=2, Criteria1:="0"<br>    Range("A1:B5").Select<br>    Selection.Copy<br>    Workbooks.Add<br>    ActiveSheet.Paste<br>    Application.CutCopyMode = <SPAN style="color:#00007F">False</SPAN><br>    Application.Dialogs(xlDialogSendMail).Show<br>    ActiveWindow.ActivateNext<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Legacy 185509]

Hey Trevor
Thank you for reply, and idea, I think I am getting somewhere here,
but the problem is, everytime I will run data might be different, & I need it to do this by itself, & I am trying to make process automatic.

I need to create another filter where it will add in same email if Value is greater than 2 than it will send email.

it will be all in one email.
I need some more idea how this process can be done automatically.
Thank you again


sorry forgot to add which version I am using,
I am using 2007 Excel

 

[Legacy 185509]

and I don't want to attach the file, I need to email the data as a text, so if J3 is 0 than email whole row 3
Here is Img of my data

image removed, sensitive data

 

[Trevor G]

Try this code you will have to change the options I have coloured in red

Sub EmailRange()
'*******************************************************************************
'This code requires you to Set the References. GoTo Tools > Reference >Search
'For Microsoft Outlook (A number) .Object Library and tick the box
'The following sample will look to email a filtered list of data with todays date
'Created by Trevor G
'May 2011
'*******************************************************************************
Dim OutlookApp As Outlook.Application
Dim MailSelection As Object
Dim cell As Range
Dim Subject As String
Dim EmailAddress As String
ThisWorkbook.Sheets("Karl To Elsie").Select
Range("A3").Select
Selection.AutoFilter
ActiveSheet.Range("$A$3:$j$35").AutoFilter Field:=4, Criteria1:= _
xlFilterToday, Operator:=xlFilterDynamic 'Adjust the range of cells
Range("A3").Select
ActiveCell.CurrentRegion.Copy
Set OutlookApp = CreateObject("Outlook.Application")
Set MailSelection = OutlookApp.CreateItem(0)
With MailSelection
.To = "trevor_gl@somewhere.co.uk" 'Change to email address
.Subject = "Invoice"
.Display 'Change to send once checked.
SendKeys "^({v})", True 'This is the same as using Paste
End With
' End If
'Next
End Sub