Apologies for the further update, I then found the tips page and found
the answer to this.

Once again, fantastic web-site.

Cheers, Al.

On Apr 15, 12:16*pm, wrote:
Thanks all for your help on this. *The website is superb, really
useful source of info'.

I have just one question/requirement that I couldn't find answer for
now. *Is it possible to automatically populate the 'From' box @ all?

Thanks in advance, Al.

On Apr 14, 5:32*pm, "Ron de Bruin" wrote:



Seehttp://www.rondebruin.nl/mail/folder2/mail4.htm

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

wrote in ...

Thanks Jim.

This site has been really useful. I can now e-mail the range of data
(as a HTML formatted text), but can't seem tosendit as an Excel
attachment instead?

Can you help with pointing me in the direction tosendthe range as an
excel attachment?

Thanks, Al.

So far:
Option Explicit

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rng As Range
Dim cell As Range
Dim Lrow As Long

Public Sub Test_With_AdvancedFilter()

* * Application.ScreenUpdating = False

* * Set ws1 = Sheets("Data")
* * Set ws2 = Worksheets.Add
* * Set rng = ws1.Range("A1:I395")

* * With ws1
* * * * rng.Columns(7).AdvancedFilter _
* * * * * * * * Action:=xlFilterCopy, _
* * * * * * * * CopyToRange:=.Range("IV1"), Unique:=True

* * * * Lrow = .Cells(Rows.Count, "IV").End(xlUp).Row
* * * * .Range("IU1").Value = .Range("IV1").Value

* * * * For Each cell In .Range("IV2:IV" & Lrow)
* * * * * * .Range("IU2").Value = cell.Value

* * * * * * ws2.Cells.ClearContents
* * * * * * rng.AdvancedFilter Action:=xlFilterCopy, _
* * * * * * * * * * * * * * * *CriteriaRange:=.Range("IU1:IU2"), _
* * * * * * * * * * * * * * * *CopyToRange:=ws2.Range("A1"), _
* * * * * * * * * * * * * * * *Unique:=False

* * * * * * .Columns.AutoFit
* * * * * * FormatUserWB

* * * * * * Mail_ActiveSheet_Body

* * * * Next
* * * * .Columns("IU:IV").Clear
* * End With
* * Application.DisplayAlerts = False
* * ws2.Delete
* * Application.DisplayAlerts = True

* * Application.ScreenUpdating = True
End Sub

Private Sub Mail_ActiveSheet_Body()
* * Dim OutApp As Object
* * Dim OutMail As Object
* * Application.ScreenUpdating = False
* * Set OutApp = CreateObject("Outlook.Application")
* * Set OutMail = OutApp.CreateItem(0)
* * With OutMail
* * * * .To = ws2.Range("G2").Value
* * * * .CC = ""
* * * * .BCC = ""
* * * * .Subject = "This is the Subject line"
* * * * .HTMLBody = SheetToHTML(ws2)
* * * * .display * 'or use .Send
* * End With
* * Application.ScreenUpdating = True
* * Set OutMail = Nothing
* * Set OutApp = Nothing
End Sub

Public Function SheetToHTML(sh As Worksheet)

* * Dim TempFile As String
* * Dim fso As Object
* * Dim ts As Object

* * Randomize

* * sh.Copy
* * TempFile = sh.Parent.Path & "\TmpHTML" & Int(Rnd() * 10) & ".htm"

* * ActiveWorkbook.SaveAs TempFile, xlHtml
* * ActiveWorkbook.Close False

* * Set fso = CreateObject("Scripting.FileSystemObject")
* * Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)

* * SheetToHTML = ts.ReadAll

* * ts.Close
* * Set ts = Nothing
* * Set fso = Nothing
* * Kill TempFile

End Function

On Apr 14, 4:42 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-

This-.com wrote:
Check out this link to get started...

http://www.rondebruin.nl/sendmail.htm
--
HTH...

Jim Thomlinson

" wrote:
Hi All,
Have found similar postings, but not enough to go on in regards to the
following.

I currently have some VBA that does the following:
- Creates individual file (with all the relevant records) for each
unique name contained within Column G
- Saves the file (with the name of the unique entry) .xls

What I'd like to do is extend this to be able to automaticallysendan
e-mail with pre-set text to advise the users (user is the unique entry
in column G) what they next need to do (so I'd need to include some e-
mail text as well). The unique entry isn't the SMTP address, it's the
internal user's name as it appears in our address book (e.g. Smith,
Bob)

I'd like it instead of automatically sending, at least at first, to
prompt the person who's running it as to whether they want tosendat
first.

So, in summary:
1) Run through all xls files on C Drive and attach the file to an e-
mail with some pre-defined subject/content text
2)Populatethe to box on the e-mail with the file name (less
the .xls part)

Appreciate your help on this.

Many Thanks, Al.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -