Send email based on cell value

[James]

All,

I am looking for a macro that sends an email to two recipients based on the
cell values in a range. I have a table of 150 rows where a cell value can
fall below 0 (or conditional formatting turns it red), and for each row where
the value is less than 0 (or is red), send an email with a subject heading
which includes the row identification.

EG.

Cell A1: 80163
Cell B1: -15
Recipients: (to) Joe Bloggs ; (cc) Fred Bloggs

Email with the subject heading "80163 - Over allocation"
Message: "80163 has been over allocated by 15 man-days"

Repeat this for all rows, therefore if B2 is (positive) 15, an email is not
sent.

If changes were made to other calculations which resulted in B1 becoming
positive, I don't want the macro to loop through the entire list again and
re-send emails. I would prefer the this re-sending loop to occur every
fortnight on a Wednesday.

Hope this makes sense!

If anyone can help with this, it would be greatly appreciated.

James

 

[Ron de Bruin]

More info please about the info in the columns

Start here(see also example 2)
http://www.rondebruin.nl/mail/folder3/message.htm

 

[James]

Ron,

Column A, cells 1-150 are project numbers that are entered by the user, i.e.
80163, 80176. They are not necessarily in a logical order, but will always
be 5 characters in length.

Column B, cells 1-150 will be a formula result that compares two other cell
values. Therefore man-days allowed MINUS man-days required to give the value
in the appropriate cell of column B (either positive or negative). If the
cell value is less than 0, the cell colour turns red. For arguments sake
man-days allowed would be column C and man-days required column D. The
man-days allowed figures (column C) will change very rarely. The man-days
required figures (column D) will change, therefore an email sent one week
might not need to be sent in three weeks time.

Recipients: Will always be the same two people, so they could be defined as
"jbloggs...." etc.

Sample data:

Column A Column B Column C
Column D
80163 -15 20
35
80176 15 35
20
80187 0 20
20
80190 -10 20
30

and so on...

Therefore the macro should send an email to two recipients (to be
specified), for project numbers 80163 and 80190 that say that they have too
many man-days allocated to them.

Subject header should include the project number to make it easy to know
where to amend the data.
The email message should include the figure in column B.

If in a fortnight, cell B1 (project number 80163) is positive, the macro
should re-send the email for project number 80190 (assuming B4 is still
negative).

Let me know if you still need info.

Thanks for the link. I have been looking at your example 2 on
http://www.rondebruin.nl/mail/change.htm
I am thinking a combination of the two might be the way to go??

 

[Ron de Bruin]

Hi James

I read it and reply tomorrow after work

Bed time for me now


--

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

Ron,

Column A, cells 1-150 are project numbers that are entered by the user, i.e.
80163, 80176. They are not necessarily in a logical order, but will always
be 5 characters in length.

Column B, cells 1-150 will be a formula result that compares two other cell
values. Therefore man-days allowed MINUS man-days required to give the value
in the appropriate cell of column B (either positive or negative). If the
cell value is less than 0, the cell colour turns red. For arguments sake
man-days allowed would be column C and man-days required column D. The
man-days allowed figures (column C) will change very rarely. The man-days
required figures (column D) will change, therefore an email sent one week
might not need to be sent in three weeks time.

Recipients: Will always be the same two people, so they could be defined as
"jbloggs...." etc.

Sample data:

Column A Column B Column C
Column D
80163 -15 20
35
80176 15 35
20
80187 0 20
20
80190 -10 20
30

and so on...

Therefore the macro should send an email to two recipients (to be
specified), for project numbers 80163 and 80190 that say that they have too
many man-days allocated to them.

Subject header should include the project number to make it easy to know
where to amend the data.
The email message should include the figure in column B.

If in a fortnight, cell B1 (project number 80163) is positive, the macro
should re-send the email for project number 80190 (assuming B4 is still
negative).

Let me know if you still need info.

Thanks for the link. I have been looking at your example 2 on
http://www.rondebruin.nl/mail/change.htm
I am thinking a combination of the two might be the way to go??

 

[Ron de Bruin]

If I understand you correct?
Why not simple filter for negative values in the B column and send the visible data to the two Recipients

If this is what you want I wiil create a example for you

 

[James]

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

 

[Ron de Bruin]

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" & 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" & 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 = "(e-mail address removed);[email protected]"
.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 xublishsource=", _
"align=left xublishsource=")

'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

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

 

[James]

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.

 

[Ron de Bruin]

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

 

[James]

Thanks Ron,

The .Send / .Display section was obvious when I re-read the code!

Thanks a lot for your help.

James

 

[James]

Ron,

Sorry to be a pain, I can't get the pre-defined message to appear.

This is the code I am using (cut and paste from VBA):

.HTMLBody = "Joe,<br>" & _
"There are a number of projects that have been
over-allocated<br>" & _
"The following table shows the project number and the
over-allocation in terms of man-days<br>" & _
"<br><br><br>" & _
RangetoHTML(rng)

 

[James]

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

 

[Ron de Bruin]

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

 

[James]

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

 

[Ron de Bruin]

Never try this James

No time to try/test it now

Ask it in a Outlook group

 

[James]

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.

 

[Ron de Bruin]

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