|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Create macro program to automatically email outlook with a visible cell range
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. |
#2
|
|||
|
|||
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.
|
#3
|
|||
|
|||
emailing data from slected range of cells
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. |
#4
|
|||
|
|||
|
#5
|
|||
|
|||
Thank you, I have briefly looked at the site, but presently I am still a little confused, maybe because I am tired.
|
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
emailing selcected ranges
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 |
#8
|
|||
|
|||
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
|
#9
|
|||
|
|||
Hi,
I think you want the data in column G. with out the empty rows Correct? |
#10
|
|||
|
|||
Yes, that is correct.
|
#11
|
|||
|
|||
Hi,
You can set a filter for "Non" blank cells and then have that for the data you want to email. |
#12
|
|||
|
|||
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 |
#13
|
|||
|
|||
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 |
#14
|
|||
|
|||
email and only shows what I want.
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. |
#15
|
|||
|
|||
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 |