|
|
Thread Tools | Display Modes |
#16
|
|||
|
|||
Working fine the way it is. Thanks
My company wants additonal things which I have added. The 1st thing they want is to email selected ranges, not when work book opens but every week or so. The code I am presently using to email when sheet is open is below. ' Select range of cells on the active worksheet ActiveSheet.Range("G2:G1000 ").Select ' Show the envelope on the Activeworkbook. ActiveWorkbook.EnvelopeVisible = True 'Set the optional introduction field thats adds ' add header to email body. It also sets ' the to and Suject line. Finally the message ' is sent. With ActiveSheet.MailEnvelope .Introduction = "Inventory" .Item.To = "bill_runyan@onesolutionlogistics.com" .Item.Cc = "" .Item.Subject = "Please Order" End With I will attach the newest spreadsheet, it has changed a little bit from the original. Thanks for the help I have received so far |
#17
|
|||
|
|||
Hi,
If you use the "Selected" range it will include all of the "Blank" rows. Are you sure that is wise? |
#18
|
|||
|
|||
Is it wise
I really do not know, when I do a print of the email I do throw out a few extra sheets of paper, but I really do not know how this action could hurt this application.
Thanks, |
#19
|
|||
|
|||
Hi,
Not tested but check this. Copy both codes. to Code module. You will need to modify the email module to your data. Could not test. I'm using a "Mac" ''' Added"""" the filer is set to look at column 8 or "H". Code:
Sub Filter_NonBank() Application.ScreenUpdating = False Dim Myval As Integer Dim lrow As Long lrow = Sheets("Bin inventory").Range("H65536").End(xlUp).Row Selection.AutoFilter Sheets("Bin inventory").Range("A1:k1").Select Selection.AutoFilter With Selection .AutoFilter Field:=8, 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("h2:h" & lrow).SpecialCells(xlCellTypeVisible).Count If Myval >= "2" Then Worksheets("Bin inventory").Range(Cells(VisRng.Offset(, 0).Row, 8), Cells(Range("g65536").End(xlUp).Row, 8)).Select Send_Selection_Or_ActiveSheet_with_MailEnvelope End If End With End Sub Code:
Sub Send_Selection_Or_ActiveSheet_with_MailEnvelope() 'Working in Excel 2002-2013 Dim Sendrng As Range On Error GoTo StopMacro With Application .ScreenUpdating = False .EnableEvents = False End With 'Note: if the selection is one cell it will send the whole worksheet Set Sendrng = Selection 'Create the mail and send it With Sendrng ActiveWorkbook.EnvelopeVisible = True With .Parent.MailEnvelope ' Set the optional introduction field thats adds ' some header text to the email body. .Introduction = "This is a test mail." With .Item .To = "maricharm@cableone.net" .CC = "" .BCC = "" .Subject = "My subject" .Send End With End With End With StopMacro: With Application .ScreenUpdating = True .EnableEvents = True End With ActiveWorkbook.EnvelopeVisible = False End Sub Last edited by charlesdh; 03-10-2015 at 02:08 PM. Reason: Added info |
#20
|
|||
|
|||
emailing range of spreadsheet
When I adapted some of the code to my spreadsheet it did not work correctly, after a few minor tweaking of primarily the email sub folder it worked correctly.
In the future I will try to write something that email the report weekly but for now it is sufficient. I have to gather a scanner and try to see if I can input data, if I can perhaps I can make some kind of table to automatically change my quantities as parts are taken out of inventory. attached is the most recent version with its changes. |
|
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 |