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 to send it as an Excel
attachment instead?

Can you help with pointing me in the direction to send the 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 automatically send an
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 to send at
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) *Populate the 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 -