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.

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.

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 -

See
http://www.rondebruin.nl/mail/folder2/mail4.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


wrote in message ...
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 -

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 -

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 -