I hope to have time soon to find a fix for it

--

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


"James" wrote in message ...
Ron,

It's just to make life slightly easier. I am going to accept the fact that
this little step should not be done and just explain to user that they need
to add their own messages to the data table.

Thanks for all your help. Very good code.
--
J


"Ron de Bruin" wrote:

Never try this James

No time to try/test it now

Ask it in a Outlook group

--

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


"James" wrote in message ...
Is there a way I can build this into the macro?

The reason I ask is that the company require the email signature to be used
on all emails. Turning off this email editor won't be acceptable for all
other emails sent externally.

This email will be sent internally, so I see no reason why we can uncheck
for the purposes of the email, and then reselect once sent. If this can be
done in the current macro that would be brilliant, if not is there another
macro that can do the same job?

Thanks for sticking with this. Everything else works great.

James
--
J


"Ron de Bruin" wrote:

If Word is your mail editor in Outlook it can give problems
Try to uncheck it in Outlook

--

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


"James" wrote in message ...
Ron,

Found another problem (not with the code).

I set up a dummy spreadsheet at home to test out the code. The code works
fine and generates an email from Outlook with the required data.

I have now tried to do exactly the same thing at work. The problem is that
the macro code generates the email, but there is no table, only my HTML
signature. Is there anything I can change in the code?

Thanks

James
--
J


"Ron de Bruin" wrote:

Hi James

A couple of modifications needed. Firstly can I add a pre-defined message
to the body of the email?

Use

.HTMLBody = "This is line 1.<br" & _
"This is line 2.<br" & _
"This is line 3.<br" & _
"This is line 4.<br<br<br" & _
RangetoHTML(rng)


Also, what does this line do:
TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

It will create a htm file to create the html in your temp folder
At the end of the function it delete this file

'Delete the htm file we used in this function
Kill TempFile



Finally, the code only generates the email, it doesn't send automatically.
What do I need to change?

.Display 'or use .Send


--

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


"James" wrote in message ...
Ron,

Genius!

A couple of modifications needed. Firstly can I add a pre-defined message
to the body of the email? Something like:

Joe,

These are the projects that have been over-allocated

The way you seem to do it is "Joe" & vbNewLine & vbNewLine & _ "..."

Also, what does this line do:

TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

Finally, the code only generates the email, it doesn't send automatically.
What do I need to change?

Thanks for your help.
--
J


"Ron de Bruin" wrote:

Ok Try this one

Change this in the code

'Name of the worksheet with the data
Set WS = Sheets("Sheet1") '<<< Change

'Set filter range : A1 is the top left cell of your filter range and
'the header of the first column, D is the last column in the filter range
Set rng = WS.Range("A1:D" & Rows.Count)


Then set the filter field (B in your code i believe ?)

'This example filters on the second column in the range (change the field if needed)
'In this case the range starts in A so Field:=1 is column A, 2 = column B, ......
rng.AutoFilter Field:=2, Criteria1:="<0"


It will display the mail first this example so you can view the mail first
Copy the macro and function below in a standard module in your excel workbook


Sub Test_Outlook_Body()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2007
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim WS As Worksheet

'Name of the worksheet with the data
Set WS = Sheets("Sheet1") '<<< Change

'Set filter range : A1 is the top left cell of your filter range and
'the header of the first column, D is the last column in the filter range
Set rng = WS.Range("A1:D" & Rows.Count)

'Firstly, remove the AutoFilter
WS.AutoFilterMode = False

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

'This example filters on the second column in the range (change the field if needed)
'In this case the range starts in A so Field:=1 is column A, 2 = column B, ......
rng.AutoFilter Field:=2, Criteria1:="<0"

Set rng = Nothing
On Error Resume Next
Set rng = WS.AutoFilter.Range
On Error GoTo 0

If rng Is Nothing Then GoTo EndMacro

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = "
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.HTMLBody = RangetoHTML(rng)
.Display 'or use .Send
End With
On Error GoTo 0

EndMacro:

'Close AutoFilter
WS.AutoFilterMode = False

With Application
.EnableEvents = True
.ScreenUpdating = True
End With

Set OutMail = Nothing
Set OutApp = Nothing
End Sub


Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2007
Dim fso As Object
Dim ts As Object
Dim TempFile As String
Dim TempWB As Workbook

TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

'Copy the range and create a new workbook to past the data in
rng.Copy
Set TempWB = Workbooks.Add(1)
With TempWB.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Cells(1).PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
On Error Resume Next
.DrawingObjects.Visible = True
.DrawingObjects.Delete
On Error GoTo 0
End With

'Publish the sheet to a htm file
With TempWB.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=TempWB.Sheets(1).Name, _
Source:=TempWB.Sheets(1).UsedRange.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With

'Read all data from the htm file into RangetoHTML
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
"align=left x:publishsource=")

'Close TempWB
TempWB.Close savechanges:=False

'Delete the htm file we used in this function
Kill TempFile

Set ts = Nothing
Set fso = Nothing
Set TempWB = Nothing
End Function










--

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


"James" wrote in message ...
Ron,

Filtering would work and would probably be the easiest option.

As long as the macro filters the list, sends the range and then unfilters
the list at the end, it won't be a problem.

It would be great if you could set something up.

Thanks alot for your help.

James
--
J