Macro needed to send auto email with contents of cell on corresponding row as trigger cell

Thanks Joe & No problem...

Yes, the question is a bit unclear... basically if I update the spreadsheet to send an auto email if ANY cell in column D =95.
Then I want the value of column B, corresponding row to show up SOMEWHERE (anywhere) in the email body or subject line...

I think the part I am missing has to do with tying the corresponding cells in that row.
For example... Information on the Science Chapter of the Book I am proofing is all on row 3, when i update the status in column D to 95, it sends me an email... But the email is useless if it doesnt tell me which chapter i got the notification for... I need Science somewhere in that email to tell me to go proof the Science Chapter....

Any help you all can provide will be greatly appreciated!!

Apologies... Done.

try this:

Please Login or Register  to view this content.

this will create a new email for each row that has D>95. Careful though, if you have plenty of those this will create tons of emails and might crash Excel.

Note I placed Cells(i, 4) >= 95. If you the D column to be EXACTLY =95 just adjust the instructions

THANKS JOE! This worked for the most part... I believe that the email is not generating because 95 is formatted as a percent in my spreadsheet... would you be able to help me with that??

I thought maybe attaching the spreadsheet to this thread would help?

So if Column D reaches 95, generates an email, I just need the contents of the corresponding row's cell in column B to be the subject of the email.

THANKS AGAIN!!

if formatted as a percent try > 0.95 instead of > 95

To have this instance running only once when it sees the 1st row with D>95 then just add the instruction "Exit For" after "Set OutMail = Nothing"

Joe, Looks like we are almost there!

The 0.95 worked to identify the percentage....

But the only problem now is when the email is generated... It only captures the subject title of the first row.

I need the Title in Column B to populate the subject depending on the row cell that I change.


So for instance...

Row 1 - Science - 85% - CP
Row 2 - Life Studies - 95% - SH
Row 3 - Social Studies - 27% - DP

Notice, Row 2 is at 95%, it sends me an email automatically, but I need the TITLE "Life Studies" as the subject line in the email.
The way we have it coded now, no matter what row I change to 95% the title is always reading that first line "Science".


Hope this helps...

We're almost there!!!

THANKS AGAIN JOE!!

Would ANYONE be able to help me finish this??

Thanks Joe, I just tried that too and its still only sending that first title no matter which trigger cell i update.

I only want 1 email per trigger, so I used the "Set OutMail = Nothing" with "Exit For" behind it like you told me to do a few messages back. That worked perfectly.

All I need now is SOMEWHERE in the email (doesnt HAVE to be the subject line, needs to tell me the value of cell B, depending on which cell in column D I update. Again, it doesnt have to be the subject line of the email, it can say it in the body of the email too...

Here's where I am with my macro code now:

Sub Make_Outlook_Mail_With_File_Link()

Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim i As Integer

If ActiveWorkbook.Path <> "" Then
Set OutApp = CreateObject("Outlook.Application")


strbody = "<font size=""3"" face=""Calibri"">" & _
"Quality Assurance Department:<br><br>" & _
"Please be advised that there is a document ready to be proofed: .Cells(i, 2) <br><br>"

For i = 1 To 140

If Cells(i, 4) = 0.95 And IsNumeric(Cells(i, 4)) = True Then


Set OutMail = OutApp.CreateItem(0)

With OutMail
.To = "[email protected]"
.cc = ""
.BCC = ""
.Subject = ActiveWorkbook.Sheets("TM Tracking Chart").Cells(i, 2) & " Proofing & Editing"
.HTMLBody = strbody
.Display 'or use .Send
End With
On Error GoTo 0

Set OutMail = Nothing
Exit For


End If


On Error GoTo 0
Next i

Set OutApp = Nothing
Else
MsgBox "The ActiveWorkbook does not have a path, Save the file first."
End If
End Sub

Joe this spreadsheet is basically to send one department an email when another department is complete with 1 line item.

so i just want 1 email sent every time i run the macro.

If Production department is done with Row 3, they mark it 95 % and it emails the quality department so they can take action.

I dont want 9 emails going, there should be an email 1 time, 1 email.
Science Chapter is written, the team updates it to 95%, macro runs and sends me an email letting me know WHICH Chapter is ready for the Quality Department.

Does that make sense?

I am doing this basically to let one department know its ok to begin an assignment without the other department having to tell them, there should only be one email sent at the time the column D is changed to 95%, the email shouldnt go again.

Hope this helps

Joe this spreadsheet is basically to send one department an email when another department is complete with 1 line item.

so i just want 1 email sent every time i run the macro.

If Production department is done with Row 3, they mark it 95 % and it emails the quality department so they can take action.

I dont want 9 emails going, there should be an email 1 time, 1 email.
Science Chapter is written, the team updates it to 95%, macro runs and sends me an email letting me know WHICH Chapter is ready for the Quality Department.

Does that make sense?

I am doing this basically to let one department know its ok to begin an assignment without the other department having to tell them, there should only be one email sent at the time the column D is changed to 95%, the email shouldnt go again.

Hope this helps

THIS WORKED PERFECTLY!! THANK YOU SOOOO MUCH JOE.

It has been reviewed and all is a go... My last question, I would like to use the same column (D), when it equals 96% to send a return email to the email address located in cell T3 and instead of the macro recognizing the word "yes" to NOT send duplicate emails, can it recognize the word "Complete"???

any information you can provide will be greatly appreciated. Below is the code I am now using:

Option Explicit

Sub Make_Outlook_Mail_With_File_Link()

Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim i As Integer

If ActiveWorkbook.Path <> "" Then
Set OutApp = CreateObject("Outlook.Application")


strbody = "<font size=""3"" face=""Calibri"">" & _
"Dept:<br><br>" & _
"Please be advised that there is a document ready: <br><B>" & _
ActiveWorkbook.Name & "</B><br>" & _
"Click on this link : " & _
"<A HREF=""file://" & ActiveWorkbook.FullName & _
""">Tracking Chart</A>" & _
"<br><br>Regards," & _
"<br><br>TDS Project Management Team</font>"

For i = 4 To 150

On Error Resume Next

If Cells(i, 4) = 0.95 And Cells(i, 17) <> "Yes" Then


Set OutMail = OutApp.CreateItem(0)

With OutMail
.To = "[email protected]"
.cc = ""
.BCC = ""
.Subject = ActiveWorkbook.Sheets("TM Tracking Chart").Cells(i, 2) & " is Ready to be Proofed"
.HTMLBody = strbody
.Display
End With
On Error GoTo 0

Cells(i, 17) = "Yes"

Set OutMail = Nothing

Exit For

End If

On Error GoTo 0
Next i

Set OutApp = Nothing
Else

End If
End Sub

Please see attached spreadsheet… I need assistance creating a macro that does the following 4 things:

1. Send auto email to: [email protected] MONTHLY starting 6 months before EXPIRATION DATE (Column E). The body of the email, can just say REMINDER FOR (Same Row, Column A) that Certification Training (Same Row, Column B) is Due to Expire on (Same Row, Column C). Please renew or contact your supervisor as soon as possible.

2. Once an e-mail is sent, I also need Column F (which is blank right now) to READ: “1 REMINDER SENT” at 6 months, “2 REMINDER SENT” at 5 months, “3 REMINDER SENT” at 4 months and so on… *Basically this column is to know how many auto e-mails were sent.


3. Color Code the Cell in Column F. Fill the color of the cell in column F, depending on the following:
- Reminders sent at 6 month & 5 month = Green Filled Cell
- Reminders sent at 4 month & 3 month = Yellow Filled Cell
- Reminders sent at 2 month & 1 month = Red Filled Cell

4. For those who DO NOT COMPLETE their certification, I need ONE auto e-mail sent saying:

“No information for (Same Row, Column A) – Training Titled (Same Row, Column B) has been received. Due Date Has Expired.”

Excel will know to send this e-mail because Column C “Due Date”, will be todays Date and Column E in that same row will be blank.


I need this macro as soon as possible. If anyone can help, it would mean tons for me!!! THANKS SO MUCH, I Look forward to hearing your responses.