Solved: Emails from Excel using various dates

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

bobbynova

Thread Starter
Joined
Apr 24, 2015
Messages
33
Hi all,
I've spent a good few days reading posts on this site it seems between everyone here there a whole host of knowledge!

I'm here as I am having trouble with an excel sheet and I'm unsure how to solve it.
I am using excel 2007 on my laptop.

I've searched both this site and google for a few days now but I haven't found anything that seems to be what I need.
There is a thread on here that was helped by Keebellah and that is the closest I can find to what I am trying to do however I can't seem to edit the code in such a way that gets it working in my sheet.

Let me explain what I am trying to do.

My sheet is a training tracker which has a column of names of employees and some dates.
Specifically the dates are: Ideal WK4 date, Ideal WK8 date, Ideal WK12 date. These all have dates inserted.
There is also a column next to each of these that either says completed, or is empty.

I am trying to get Excel to email me when one of these dates is 7 days away or less, unless the column says completed. Then I don't want an email.
I want the email to basically say:
EMPLOYEE NAME is due for their WK? meeting within 7 days. Please schedule this in.

The employee name and WK4/8/12 should be pulled from the sheet.

Logically speaking, this is what I am looking for...
IF M(ideal date column wk4) = less than 7 days, send email with row data. If O=Complete don't send

IF Q(ideal date column wk8) = less than 7 days, send email with row data. If S=Complete don't send

IF X(ideal date column wk4) = less than 7 days, send email with row data. If Z=Complete don't send

O, S and Z are all columns that either say Completed or are blank. If they say completed thereis no need for the email to send as obviously its already done.

I have a code that literally sends the whole sheet as an attachment which has to be done manually, I have to go in to excel and open up the code and press run, it then sends to my email address the predefined message and the entire sheet. Close but not what I'm looking for.

I haven't seen many posts about reading multiple date columns for this during my search so I hope someone can help.
I'm also speaking to an excel guru via email to see if he can help. (I think he is on this site), I am hoping that I will be able to get this sorted with some help.

Look forward to any assistance.

Regards
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
Hi, welcome to the forum.
What you are asking has already been doen in one or other way. I've helped with some of these.
I think it will make it easier to help if you can attach a asample file with dummy data.
 

bobbynova

Thread Starter
Joined
Apr 24, 2015
Messages
33
Here is the sheet with dummy data, hopefully it will explain in more detail what I posted in the first post. I have seen a post that you helped with earlier that is similar to what I need, I couldn't get the code to work myself however when I tried to edit it to suit my project. I have had a good attempt before having to post for help, I don't like to admit defeat :)

Many thanks for your reply.
 

Attachments

bobbynova

Thread Starter
Joined
Apr 24, 2015
Messages
33
I have managed to get the code you wrote for another workbook to work however only for the week 4.
It is only part working, by that I mean it is trying to send an email regardless of if the box says complete or not. This is because I am unsure how to add that statement to the code. I am also unsure how to get it to read which update is required, be it week 4, 8 or 12 and insert that in to the subject box and also the message body.

It is opening up lots of emails when I run the macro, I have to manually press send on all of these. If we could make that an automatic progress it would be ideal. I have pasted the code below, its an adaption of what you used in a previous workbook but with my cells inserted.

Option Explicit


Public Sub CheckAndSendMail()
Dim lRow As Long
Dim lstRow As Long
Dim toDate As Date
Dim toList As String
Dim ccList As String
Dim bccList As String
Dim eSubject As String
Dim EBody As String
Dim ws As Worksheet

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

Set ws = Sheets(1)
ws.Select

lstRow = WorksheetFunction.Max(3, ws.Cells(Rows.Count, "L").End(xlUp).Row)

For lRow = 3 To lstRow
toDate = Cells(lRow, "M").Value
'toDate = Replace(Cells(lRow, "M"), ".", "/")
If Left(Cells(lRow, "M"), 4) <> "Mail" And toDate - Date <= 7 Then

toList = Cells(lRow, "AG") 'gets the recipient from col D
eSubject = "Induction Update " & Cells(lRow, "E") & " " & Cells(lRow, "D") & " is due on " & Cells(lRow, "M").Value
EBody = "Dear " & Cells(lRow, "E") & vbCrLf & vbCrLf & "An update is due. Please book this with your Manager."

MailData msgSubject:=eSubject, msgBody:=EBody, Sendto:=toList


End If
Next lRow

ActiveWorkbook.Save

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

Function MailData(msgSubject As String, msgBody As String, Sendto As String, _
Optional CCto As String, Optional BCCto As String, Optional fAttach As String)

Dim app As Object, Itm As Variant
Set app = CreateObject("Outlook.Application")
Set Itm = app.CreateItem(0)
With Itm
.Subject = msgSubject
.To = Sendto
If Not IsMissing(CCto) Then .Cc = CCto
If Len(Trim(BCCto)) > 0 Then
.Bcc = BCCto
End If
.Body = msgBody & Chr(13) & Chr(13)
.BodyFormat = 1 '1=Plain text, 2=HTML 3=RichText -- ISSUE: this does not keep HTML formatting -- converts all text
'On Error Resume Next
If Len(Trim(fAttach)) > 0 Then .Attachments.Add (fAttach) ' Must be complete path'and filename if you require an attachment to be included
'Err.Clear
'On Error GoTo 0
.Save ' This property is used when you want to saves mail to the Concept folder
.Display ' This property is used when you want to display before sending
' .Send ' This property is used if you want to send without verification
End With
Set app = Nothing
Set Itm = Nothing
End Function


Sub testing()
Dim toList As String
Dim ccList As String
Dim bccList As String
Dim eSubject As String
Dim EBody As String
toList = "[email protected]"
eSubject = "test mail sent " & Now()
EBody = "Hello there," & vbNewLine & _
"This is a test message." & vbNewLine & vbNewLine & _
"Regards," & vbNewLine & vbNewLine & _
"Dan"
MailData msgSubject:=eSubject, msgBody:=EBody, Sendto:=toList

End Sub
 

bobbynova

Thread Starter
Joined
Apr 24, 2015
Messages
33
Have spent the morning working on this today and have ended up no further forward lol. Thinking about this, Is the sheet going to need a column to indicate if the email has been sent already so it doesn't send the same email multiple times?
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
Yes, you will need an extra column to chekc sent and i suggest a date sent too.
It's a pity you don't attach a macro embedded file.
I'll have to copy and psste the code you've used. :eek:
 

bobbynova

Thread Starter
Joined
Apr 24, 2015
Messages
33
Ok,
Ideally those columns could be added at the very end so it doesn't break all of the conditional formatting, there is quite a bit!

I have attached the macro enabled file to this post...I think. Hopefully the file attached makes it easier for you to see what I have done without you having to copy and paste?
 

Attachments

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
I knew the vba code was familiar, it looks like the code I also used and modified to make it better to use for all circumstances.
I assume you're using Outlook?
I see the added columns and will work with the last file you attached.
 

bobbynova

Thread Starter
Joined
Apr 24, 2015
Messages
33
It is the same code you used for another project on this forum, very helpful!
I am using outlook, that is correct. Many thanks Hans.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
Since you added the columns, the original question and columns have changed just to make sure I'm getting it rigyt and this based in file DUMMY2:

(1) M < 7 days and O <> Complete then send mail
(2) S < 7days and W <> Complete then send mail
(3) AB < 7 days and AF <> Complete then send mail

You also say mail with row data, the entire row? what data exactly.
 

bobbynova

Thread Starter
Joined
Apr 24, 2015
Messages
33
That looks correct to me however I think O should be Q? As below...

(1) M < 7 days and Q <> Complete then send mail
(2) S < 7days and W <> Complete then send mail
(3) AB < 7 days and AF <> Complete then send mail

To be honest, if it sends the data as it was when I was testing then the row won't need to be sent.
If the email will tell if the meeting is for a week 4, week 8 or week 12 then that is enough.

The way I modified the code it included:
Name, employee ID and date the meeting is due in the subject box, The message body included the name, and just that an update is due. If that could also include the type of update (wk4, 8 or 12) then it would be perfect and no need to send the whole row from the sheet.

The original idea was as I have just wrote but also to attach the 1 row of the sheet to the email, so if Colleague 1 has their week 4 meeting it would send an email as above and also attach the entire row from the sheet for COLLEAGUE 1. I have however realised this may be un-needed as the email will say all it needs to without the attachment of the row.

I hope that makes sense.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top