Excel Email Follow-Up Question

  • Thread starter Thread starter carl
  • Start date Start date
C

carl

Thank you all for helping me to learn how I can email
spreadsheets directly from Excel.

I am using the code @
http://www.rondebruin.nl/sendmail.htm#selection and it
works fantastic.

Now I'm trying to refine my worksheet.

On the sheet that I am sending, column K has the email
addresses. The addresses are "paste-linked" from another
sheet in the workbook.

I would like to filter on column K and then send the
visible cells to that email address.

Is there a way to modify the code to allow this ??
 
Hi Carl

I give you a example in your other thread
Do you have problems with it?
Or do you not know how to use it in the selection sub
 
Thanks. I went back to yesterday's post and found it.

Not sure how to use it. Could you explain ??


Sub test()
Dim Recipient As String
Dim cell As Range
For Each cell In Columns("K").Cells.SpecialCells
(xlCellTypeConstants)
If cell.EntireRow.Hidden = False And cell.Value
Like "*@*" Then
Recipient = Recipient & cell.Value & ";"
End If
Next
Recipient = Left(Recipient, Len(Recipient) - 1)
MsgBox Recipient
End Sub

-----Original Message-----
Hi Carl

I give you a example in your other thread
Do you have problems with it?
Or do you not know how to use it in the selection sub

--
Regards Ron de Bruin
http://www.rondebruin.nl


"carl" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
 
Hi Carl

Try this one
It will send the selection to all the E-mail Addresses that are visible in the K column
There is no error check if there is no formula/addres in the K column

Sub Mail_Selection()
Dim source As Range
Dim dest As Workbook
Dim strdate As String
Dim cell As Range
Dim Arr() As String
Dim N As Integer


Set source = Nothing
On Error Resume Next
Set source = Selection.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If source Is Nothing Then
MsgBox "The source is not a range or the sheet is protect, please correct and try again.", vbOKOnly
Exit Sub
End If

If ActiveWindow.SelectedSheets.Count > 1 Or _
Selection.Cells.Count = 1 Or _
Selection.Areas.Count > 1 Then
MsgBox "An Error occurred :" & vbNewLine & vbNewLine & _
"You have more than one sheet selected." & vbNewLine & _
"You only selected one cell." & vbNewLine & _
"You selected more than one area." & vbNewLine & vbNewLine & _
"Please correct and try again.", vbOKOnly
Exit Sub
End If

Application.ScreenUpdating = False

N = 0
For Each cell In Columns("K").Cells.SpecialCells(xlCellTypeFormulas)
If cell.EntireRow.Hidden = False And cell.Value Like "*@*" Then
N = N + 1
ReDim Preserve Arr(1 To N)
Arr(N) = cell.Value
End If
Next cell

Set dest = Workbooks.Add(xlWBATWorksheet)
source.Copy
With dest.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
' Paste:=8 will copy the column width in Excel 2000 and higher
' If you use Excel 97 use the other example
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Cells(1).PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
End With

strdate = Format(Now, "dd-mm-yy h-mm-ss")

With dest
.SaveAs "Selection of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
.SendMail Arr, _
"This is the Subject line"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub
 
Hi Ron. Thank you for helping me learn how to email
spreadsheets.

I took the code and pasted it into a 'module'.

My K column heading is in K3. I then filtered to a
specific email address.

When I ran it from the excel>tools>macro the macro got
hung up at:

N = 0
For Each cell In Columns("K").Cells.SpecialCells
(xlCellTypeFormulas)

HungUp Here>>>>If cell.EntireRow.Hidden = False
And cell.Value Like "*@*" Then
N = N + 1
ReDim Preserve Arr(1 To N)
Arr(N) = cell.Value
End If
Next cell


Am I using the code incorrectly ? Do I need to set my
spreadsheet up differently ?

Thank you again for your help.
-----Original Message-----
Hi Carl

Try this one
It will send the selection to all the E-mail Addresses
Click to expand...
that are visible in the K column
There is no error check if there is no formula/addres in the K column

Sub Mail_Selection()
Dim source As Range
Dim dest As Workbook
Dim strdate As String
Dim cell As Range
Dim Arr() As String
Dim N As Integer


Set source = Nothing
On Error Resume Next
Set source = Selection.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If source Is Nothing Then
MsgBox "The source is not a range or the sheet is
Click to expand...
protect, please correct and try again.", vbOKOnly
 
Is this one line in your code Carl

HungUp Here>>>>If cell.EntireRow.Hidden = False
And cell.Value Like "*@*" Then
 
Back
Top