email by rows

In my work book i have a form that calls the email this is where i get
the error .publish from

also when i changed the email address coulmn from B to E that is when i
only get headers in the mail when i use B it all works
how can i change the email address coulmn from B to E and also use tis
in my work book

DaleL


---
Message posted from http://www.ExcelForum.com/

Hi Dale

Ash.Range("A1:L100").AutoFilter Field:=2,
It filter on column B
Change 2 to 5(that's E)

Send the file to me Dale if you can't get it to work

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


"DaleL " wrote in message news
HI Ron I added your code to my workbook and it is giveing me errors at
Publish (True)when I click the debug button this is where the yellow
line is..Publish (True)

it works part of the time .. when it does work all it will send is the
headers..i dont know what im doing diffrently to get to work those
times

This is the code I am using is it possable I can send my work book to
you and have you look it over and maybe try the code out your self ???

Dim Nsh As Worksheet

Sub Send_Row()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim rng As Range
Dim Ash As Worksheet

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
Set Ash = ActiveSheet
Set Nsh = Worksheets.Add
Ash.Activate

On Error GoTo cleanup
For Each cell In
Ash.Columns("E").Cells.SpecialCells(xlCellTypeCons tants)
If cell.Value Like "*@*" And cell.Offset(0, 1).Value = "yes"
Then
Ash.Range("A1:L100").AutoFilter Field:=2,
Criteria1:=cell.Value
With Ash.AutoFilter.Range
On Error Resume Next
Set rng = .SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
rng.Copy Nsh.Cells(1)
Nsh.Columns.AutoFit
Ash.AutoFilterMode = False

Set OutMail = OutApp.CreateItem(0)
With OutMail
To = cell.Value
Subject = "Grades Aug"
HTMLBody = RangetoHTML2
Send 'Or use Display
End With
Set OutMail = Nothing
Nsh.Cells.Clear
End If
Next cell

cleanup:
Application.DisplayAlerts = False
Nsh.Delete
Application.DisplayAlerts = True
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub




Function RangetoHTML2()
Dim fso As Object
Dim ts As Object
Dim TempFile As String
TempFile = Environ$("temp") & "/" & _
Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
With ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=Nsh.Name, _
Source:=Nsh.UsedRange.Address, _
HtmlType:=xlHtmlStatic)
Publish (True)
End With
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML2 = ts.ReadAll
ts.Close
Set ts = Nothing
Set fso = Nothing
Kill TempFile
End Function


---
Message posted from http://www.ExcelForum.com/

I got the script to work I figured out the header part and also that if
you have the same address twice it will send 2 emails but each mail
will have multiple lines in it that was part of my problem because I
was testing it with my email account....

new problem now to fix

I have a form that pops up when you click on an option button it lets
you do stuff like add a user or edit a user or send a mass mail to
every one in the list or do the row by row mail when you click on the
row by row button this is what is behind it

Private Sub ob2_Click()
' Send a Volunteer Status Update
Unload Me
Application.Run "Send_Row"
End Sub

when I use this to send the row by row email that is where I get the
.Publish (True) error if I send it from the main sheet and use tool
macroSend_Row .. then it works fine

any idea how to get the button to work ???

DaleL


---
Message posted from http://www.ExcelForum.com/

i have also tried to cal it using Dim Nsh As Worksheet with no results
anyone care to try there hand at it ??

Dale


---
Message posted from http://www.ExcelForum.com/

Hi Dale

Send (private) me a small example workbook and I will look at it

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


"DaleL " wrote in message news
i have also tried to cal it using Dim Nsh As Worksheet with no results
anyone care to try there hand at it ??

Dale


---
Message posted from http://www.ExcelForum.com/