Create macro program to automatically email outlook with a visible cell range

BillMaintenance · #1 02-26-2015, 12:39 PM

I am trying to email when my inventory gets to a minimum count when the visible cell gets data in it to a group to inform them that parts must be ordered. I will attach the entire program, if I can, to show the work I have done. I have studied a little from the internet, but cannot get it to work, I have not had any Excel courses for 6 years and I am afraid I forgot a lot about setting up Macros.

BillMaintenance · #2 03-02-2015, 01:38 PM

I have searched the internet, and have seen where it could be done, but when trying just the simplest forms that was listed it will not email the array, my thinking is that it is probably due to the new version of Microsoft excel.

BillMaintenance · #3 03-03-2015, 01:42 PM

I finally got part of my spreadsheet to email, my only problem is that I only want to email the active lines or rather re-order line when it comes up. I do not want to email the entire range. Can this be achieved? Can anyone lead me to a solution?

I will attached my latest file with the macro.

charlesdh · #4 03-03-2015, 01:51 PM

Hi,

Check this site. It may help.

http://www.rondebruin.nl/win/section1.htm

BillMaintenance · #5 03-03-2015, 04:28 PM

Thank you, I have briefly looked at the site, but presently I am still a little confused, maybe because I am tired.

charlesdh · #6 03-03-2015, 06:26 PM

Hi,

The "Active lines" that you mentioned. Is it the result of a filter?
Can you provide more info as to what you need?
Possibly an example of the data or what the data looks like that you need to email.
I'll try to help. But, will not be able to do it tonight. I'll try to look at and try to help within the next couple of days.

BillMaintenance · #7 03-04-2015, 12:11 PM

ORDER 9001KR1G

This is what it looks like when the range is emailed, I am attempting to see only ORDER 9001KR1G, I prefer not to see the empty cells that have no text in them, there may be multiple cells in this Colum that contain text I would like to see them in the email. The column I am setting the text up is G, the comparisons I am using is in the column for E and F, with E being the actual count, and F being a set minimum. My comparison statement in G is =IF (E4<F4, "order 9001ke5H2",") this statement goes down the entire G column with different Part numbers to be ordered.

Thanks,
For any and all help

BillMaintenance · #8 03-04-2015, 12:14 PM

In the previous email I copied the file exactly, but the borders failed to show up in the paste. So with this said there will be multiple borders of empty cells before 9001k35H2 and after 900K35H2

charlesdh · #9 03-04-2015, 01:09 PM

Hi,

I think you want the data in column G. with out the empty rows
Correct?

BillMaintenance · #10 03-04-2015, 01:23 PM

Yes, that is correct.

charlesdh · #11 03-04-2015, 01:29 PM

Hi,

You can set a filter for "Non" blank cells and then have that for the data you want to email.

charlesdh · #12 03-04-2015, 02:24 PM

Hi,

Here's a bit of code for filtering "Non Blank".
It will select the data in column G only.

Code:

Sub Filter_NonBank()
Application.ScreenUpdating = False
Dim Myval As Integer
Dim lrow As Long
lrow = Sheets("Bin inventory").Range("g65536").End(xlUp).Row
Selection.AutoFilter
Sheets("Bin inventory").Range("A1:h1").Select
Selection.AutoFilter
    With Selection
        .AutoFilter Field:=7, Criteria1:="<>" '' this set the filtered data for the value
    End With
    With Worksheets("Bin inventory").AutoFilter.Range
        Set VisRng = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
            .Cells.SpecialCells(xlCellTypeVisible)
            '' make sure you have more than 1 row  ''
        Myval = .Range("c2:c" & lrow).SpecialCells(xlCellTypeVisible).Count
        If Myval >= "2" Then
            Worksheets("Bin inventory").Range(Cells(VisRng.Offset(, 0).Row, 7), Cells(Range("g65536").End(xlUp).Row, 7)).Select
        End If
    End With
End Sub

charlesdh · #13 03-04-2015, 02:28 PM

H,
Heres the corrected code original did not show correctly.

Sub Filter_NonBank()
Application.ScreenUpdating = False
Dim Myval As Integer
Dim lrow As Long
lrow = Sheets("Bin inventory").Range("g65536").End(xlUp).Row
Selection.AutoFilter
Sheets("Bin inventory").Range("A1:h1").Select
Selection.AutoFilter
With Selection
.AutoFilter Field:=7, Criteria1:="<>" '' this set the filtered data for the value
End With
With Worksheets("Bin inventory").AutoFilter.Range
Set VisRng = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
'' make sure you have more than 1 row ''
Myval = .Range("c2:c" & lrow).SpecialCells(xlCellTypeVisible).Count
If Myval >= "2" Then
Worksheets("Bin inventory").Range(Cells(VisRng.Offset(, 0).Row, 7), Cells(Range("g65536").End(xlUp).Row, 7)).Select
End If
End With
End Sub

BillMaintenance · #14 03-05-2015, 05:11 PM

Thanks for you much needed help. I can engage my macro, then send out an email, maybe in time I will be able to send this information out when the workbook is opened automatically. I wonder if this can be done, anyway that will be a few weeks down the line.
Where can a person get some additional education on how macros work and basically programming them? I am finding this quite interesting.

Again thanks again, could not ask for better assistance.

charlesdh · #15 03-05-2015, 05:49 PM

Hi,

You can have a macro run when you use a workbook open event.
As for books look on amazon. They have a great selection of books.

Code:

Private Sub Workbook_Open()
''' This code needs to be in the "Thisworkbook module

Run "MyMacro"''' this will call the code you want to run

End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Automatically add firm name to visible contactname	spqr²	Outlook	0	12-29-2014 03:24 PM
Change formula cell range based on cell value	Scoth	Excel	4	10-25-2012 07:51 AM
Email macro to read range for .To field	MattMurdock	Excel Programming	2	08-07-2012 06:46 AM
Can't create new email or access email acounts Outlook 2003	onthebeaches	Outlook	1	02-20-2012 10:21 PM
Outlook Not Sending Automated Email Unless Visible	RMittelman	Outlook	0	12-10-2010 11:18 AM