Tech Support Guy banner
Status
Not open for further replies.

Solved: Auto Generated Email in Excel

5K views 43 replies 2 participants last post by  DiverHodge 
#1 ·
Firstly Hi,
I'm Hodge and this is my first post on this forum, please (HELP)

I have been looking around the various forum's trying to figure this out myself but with little success. There are many variants of this problem throughout these forums with many solutions, but none of which i've been able to adapt or use.

I basically run a diving team with 27+ divers and have various spread sheets to manage the team, from equipment servicing to personnel run out dates. I have been trying to find/create a Macro that will automatically send me an email, with Cc's option, when cells in column M & O turn yellow or red. Then copy all the data within that row and email the address in column P as an attachment. I have looked at Ron de Bruin's code, and tried to adapt it without success. Once I have the correct code, I believe i will be able to adapt it to most, if not all of my spreadsheets.

Hopefully someone has used this type of code before for the same reasons, Sorry must mention that I'm working on MS Office For Mac 2011, but don't think the process is much different. I don't really know much about VBA, so any help will be welcomed.

I've attached one of my spreadsheets just incase.

Thanks in advance for any help you may be able to offer.

Hodgey
 

Attachments

See less See more
#2 ·
Hoggey,

Welcome to the forum.
When using "Conditional Formating" Excel does not detect the changes.
What you can do is use code the will look at the cells referenced in the Conditional range
and determine if it fits the same criteria. If it does send the email.
 
#3 ·
Hi Charles,
Thanks for the reply and advice, you wouldn't happen to know how how I would start this code would you. As you have stated, I have used Conditional Formatting to highlight cells as criteria is met. My problem is trying to reference these cells in VBA code and trigger an Outlook email from that. As I stated above, this is something I've been looking in to from a while but I can adapt anything I have found. Any further guidance you could offer would be greatly appreciated.

Thanks Hodge
 
#4 ·
Hi Charles,
Thanks for the reply and advice, you wouldn't happen to know how how I would start this code would you. As you have stated, I have used Conditional Formatting to highlight cells as criteria is met. My problem is trying to reference these cells in VBA code and trigger an Outlook email from that. As I stated above, this is something I've been looking in to from a while but I can adapt anything I have found. Any further guidance you could offer would be greatly appreciated.

Thanks Hodge
 
#5 ·
Hi,

We can use a loop that would go down column "M" and "O" to check the value.
If you can tell me the range of the value you wish to see if it to send an email.
What is it for "Red" and "Yellow". Ie 1 to 365?
With this we need to determine if the email was sent so that we do not send it again.
As mention Excel does not detect a change caused by "Conditional Format".
You can use a "Command" button to run the code.
And to possibly a Worksheet Change event. This is predicated on the last entry that you would use when
filling out the form.
I would use the Command button myself.
Now how would you determine if the Email was sent? Add a column for Sent?
LMK
 
#6 ·
Hi,
Thanks again for your quick reply

The parameters I used for conditional format were
1>365 = Green
366>543 = Yellow
544>1000 = Red

I also used Formula (=IF(L8="","",TODAY()-L8))

I had hoped that I would be able to leave the document open and maybe have excel run a daily check at a particular time, then once a cell triggered some type of event the complete row containing all details would be sent directly to Outlook and emailed to the respective addresses. If this is not possible then a command button would be fine, and yes I will have to add a column for previously sent emails, so I have sight of what has already been send. Sorry didn't think about that one.

I sorry that i don't have a better knowledge of excel to provide you with a better more accurate description of what I am trying to achieve.

thanks again for the assistance you've given me.

Hodge
 
#7 ·
Hi,
Thanks again for your quick reply

The parameters I used for conditional format were
1>365 = Green
366>543 = Yellow
544>1000 = Red

I also used Formula (=IF(L8="","",TODAY()-L8))

I had hoped that I would be able to leave the document open and maybe have excel run a daily check at a particular time, then once a cell triggered some type of event the complete row containing all details would be sent directly to Outlook and emailed to the respective addresses. If this is not possible then a command button would be fine, and yes I will have to add a column for previously sent emails, so I have sight of what has already been send. Sorry didn't think about that one.

I sorry that i don't have a better knowledge of excel to provide you with a better more accurate description of what I am trying to achieve.

thanks again for the assistance you've given me.

Hodge
 
#9 ·
HI,

You mention Mac excel 2011 is this the system your using?
Just want to make sure. The code for sending the mail in the Mac environment is different than windows.
I have the code worked out to validate the data, am working on the email.

Please note I have no way to test the Email code.
Also will the range of data you show change.
Or will the current worksheet stay the same.
 
#10 ·
Hi,

I found some code for the Email. I can not test.
You will see a button for Test".
This is only a test. So let me know what happens.
I added a column for Email sent.
You can look at the code and add the "CC" (I hope).
 

Attachments

#12 ·
Hi,

I think while your in the code module you will see "Tools" if you do select it and then you see "Reference" select it.
Take a look at the pop up to see if says any thing "Missing" if it does deselect it and then scroll down to see if you can see a "library" with the same name or similar name and select it.
Then compile to see if this took care of it.
I'm in the process to see if I can load 2011 on my wife's mac. Not sure I can.
I had a Mac, but it gave out...so it's back to the old pc...
 
#14 ·
Hi,

I'll look at the code.
Also I tried to download 2011 to my wife's Mac, but the excel is for a "Intel" base system.
If you know of an 2011 that is non Intel based let me know.
 
#16 ·
Just had a quick look on line myself, your right it's has to be intel for Mac 2011. Just check the spec on the product website sorry. Have to say though, got into Mac about 4 years ago now and would never go back to a windows computer, I'm Mac mad, I've changed everything in the house to Mac now, but it can be frustrating sometimes when your trying to do something (like this Document) and everything is based around the windows versions.

Thanks, I've just received your recent reply with new code. I'll check it out and get back to you.

much appreciated

Hodgey
 
#18 ·
Just ran the code and it seems to be working. Once I pressed the Test button the code started to run, just a few quick questions:

Firstly, It only seems to scan the Doc until it finds the first entry and then displays that entry (row) on the screen is that correct? or should it go through finding all matching cells?

Secondly, once the row displays that seems to be it, is an email supposed to be generated in outlook? hopefully it isn't be being stupid and doing the wrong thing.

Thanks

Regards Hodge
 
#19 ·
Hi,

I have the code loop down column "M" to check the value. If the Value is suppose to send an Email the code looks at column P for the address. And yes this is row by row in column M for now.
Ok, your not stupid. It should send an email, but as mentioned I have no way to test. If as your indicating may not do what we want.
I'll check on it again.
 
#22 ·
Hi,

Do you know how to debug?
If not when your in the code you can set a "Break Point" for the code to run to and then stop.
On the left side of the line where you want the code to stop move the cursor so that it changes to an "Arrow".
Click and the line should be highlighted.
Yo can then run the code and it should stop there.
We need to find out if the code is getting to the "Function" code.
Place the breakpoint at this line. If the code stops there you should be able to do a step by step action for the code.

"If Val(Application.Version) < 14 Then Exit Sub"

Try this. But I'll be check on this tomorrow.
If the code does not go to Function code then we have a problem.
 
#23 ·
Just ran the debug and it seemed to run through, I put some details in so that the code could ID it. An email is being generated to my Outlook, but remains in the outbox with an error message. so looks like I may need to look at maybe code or AppleScript in my Outlook.

Thanks very much the code, that seems to be doing the job. One question, would it be easy enough for me to copy what you did for col M and produce it for col O as well so that it recognises one or the other?

thanks again, you an absolute Gent!

Regards

Hodgey
 
#24 ·
Hi,

You may need to have Outlook open when you run the code. I will try to verify.
But for now try that.
Here's the code. There may be a simpler way but....

Code:
Sub VaildateDataAndEmail()
Application.ScreenUpdating = False
Dim cel As Range
Dim Myval As Double
For Each cel In Range("M8:M53")
    On Error Resume Next '''if cell is blank continue
    Myval = Range("M" & cel.Row).Text
    Select Case Myval
        Case 1 To 365 '''Green
             MyRow = cel.Row
             '' Do nothing ''''
        Case 366 To 543 ''yellow
             MyRow = cel.Row
             If Range("R" & MyRow).Text <> "X" And Range("P" & MyRow).Text <> "" Then
            
             Call Mail_Range_In_Excel2011
                Range("R" & MyRow).Value = "X"
             End If
             
        Case 544 To 730 '''red
            If Range("R" & MyRow).Text <> "X" And Range("P" & MyRow).Text <> "" Then
                Call Mail_Range_In_Excel2011
                Range("R" & MyRow).Value = "X"
            End If
        End Select
Next cel
For Each cel In Range("O8:O53")
    On Error Resume Next '''if cell is blank continue
    Myval = Range("O" & cel.Row).Text
    Select Case Myval
        Case 1 To 365 '''Green
             MyRow = cel.Row
             '' Do nothing ''''
        Case 366 To 543 ''yellow
             MyRow = cel.Row
             If Range("R" & MyRow).Text <> "X" And Range("P" & MyRow).Text <> "" Then
            
             Call Mail_Range_In_Excel2011
                Range("R" & MyRow).Value = "X"
             End If
             
        Case 544 To 730 '''red
            If Range("R" & MyRow).Text <> "X" And Range("P" & MyRow).Text <> "" Then
                Call Mail_Range_In_Excel2011
                Range("R" & MyRow).Value = "X"
            End If
        End Select
Next cel
End Sub
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top