I'm have really no experience with coding, so I'm out of my depth trying to do this myself. Hopefully someone can help, so here goes!
Essentially, my excel sheet keeps track of dates of when files need to be renewed. They need to be renewed every 60 days. So I have an "IF" formula that tells me if it's time for renewal.
What do I need to do to get an email notification whenever a file comes up for renewal? I've attached a sample sheet.
Best,
Nick
PS.
Using Excel/Outlook 2010
ClickYes Pro is installed (don't know if it will be necessary or not)
the click Yes program will be required to by pass security, but are you expecting this to happen from a closed book? I don't think that will be possible.
What you will likely need is the Excel file to load in theXLstart folder and have an auto execute macro that will check the dates.... please provide more details
Thanks for the reply. I wasn't expecting to do it from a closed book. I really have no idea how to do it though. If you let me know what needs to be done I'll do it.
Any help would be especially appreciated. Thanks again.
I guess I don't understand why you need an email notification if you are looking at the spreadsheet? That's why I figured it was a closed book, with the "Current" date being the variable that affects when the date becomes due. You only see it if you open it, so if you forget to open then you don't know, but if a macro ran when ever your computer logs on, or via the windows task scheduler, then it would do it's check and send the email.
The send code I includes sending the Excel file, so by doing that I could have it filter to the items that are due and when you open it you see them right away... or if the file is large copy the due items to a new file, ... but still have to attach a file.
So what I did was created a new task for my windows task scheduler, and it works fine. It's set to open the file once daily in the morning. I copied the code you sent and put it onto a macro-enabled excel sheet.
When I saved it, and ran the task (with outlook open), the excel file opened, but no email was sent.
Also, I think it'd be awesome if it could filter just the lines of files that need to be renewed and send that. If I'm right it saves them into a new workbook right? Could it automatically delete that workbook after it's sent?
Ziggy you're awesome so far! Thanks for your help!!
Private Sub Workbook_Open()
Application.DisplayAlerts = False
Dim SubJ As String
Dim Email_1 As String
Email_1 = "Put your email here"
SubJ = "Subject Goes Here"
ActiveWorkbook.SendMail Email_1, SubJ, ReturnReceipt:=True
Application.DisplayAlerts = True
End Sub
correct, so what we need to do now is put in a condition to check if anything is overdue. so give me a little while I'll put in a loop that check the column.
Then if it finds an overdue it will send, if not it will still run but not send... oh I forgot to add a close command, so I'll add that in
alright, this should do it..... (don't forget to edit the email address... you could actually pull it from a cell on the sheet)
Code:
Private Sub Workbook_Open()
Dim endrow As Integer
Dim RowC As Integer 'row Counter
Dim wb As Workbook
Dim ws As Worksheet
Dim SubJ As String
Dim Email_1 As String
Dim SendChk As String
Set wb = ThisWorkbook
Set ws = wb.ActiveSheet
Application.DisplayAlerts = False
' Columns D & E will update everytime
'find last row used based on 3rd column
endrow = ws.Cells(ws.Rows.Count, 3).End(xlUp).Row
For RowC = 2 To endrow
'ads 60 days to date from column 4 and puts in column 5
Cells(RowC, 4) = Cells(RowC, 3) + 60
If Now() >= Cells(RowC, 4) Then
Cells(RowC, 5) = "Needs Attention"
'when it finds an overdue it will set the variable
'which will be used to determine if it will send below
SendChk = "Sendme"
Else
' clears the cell in column 5
Cells(RowC, 5) = ""
End If
Next RowC
If SendChk = "Sendme" Then
Email_1 = "your email"
SubJ = "Subject Goes Here"
ActiveWorkbook.SendMail Email_1, SubJ, ReturnReceipt:=True
End If
wb.Save
Application.Quit
Application.DisplayAlerts = True
End Sub
Also when the file is sent it sends the whole file. Is there a way to get it to open a new workbook, save the rows in there that need to be renewed (excluding the ones that don't), send it, and delete it? Maybe this part is too much to ask. If it is don't worry about it! Thanks!
ok have a look, the code looks a lot "busier now", also I've turned off screen updating so it will look like nothing is happening.
1. a new book will be added
2. part of the previous loop that aded the "Need Attention" now includes a portion that will copy that row at the same tme to the new book, and return back to the main and continue
3. I format the new book then send it
4. both books will close and I don't save the new book... because I did not want to confuse you as it would require a folder to save to and if I pick a generic one you might not know it..... can add later.
there are several ways I could have done this, but hopefully this gets the job done
Public Sub Workbook_Open()
'hides all the screen updating, gets turned on at end of code
Application.ScreenUpdating = False
Dim endrow As Integer
Dim RowC As Integer 'row Counter
Dim CrowP As Integer
Dim NB As Workbook
Dim wb As Workbook
Dim ws As Worksheet
Dim SubJ As String
Dim Email_1 As String
Dim SendChk As String
Set wb = ThisWorkbook ' orig book
Set ws = wb.ActiveSheet
Application.Workbooks.Add
Set NB = ActiveWorkbook 'new book
CrowP = 2 'row in new book
wb.Activate
Application.DisplayAlerts = False
' Columns D & E will update everytime
'find last row used based on 3rd column
endrow = ws.Cells(ws.Rows.Count, 3).End(xlUp).Row
For RowC = 2 To endrow
'adds 60 days to date from column 4 and puts in column 5
Cells(RowC, 4) = Cells(RowC, 3) + 60
If Now() >= Cells(RowC, 4) Then
Cells(RowC, 5) = "Needs Attention"
' copy the row
ws.Rows(RowC).Copy
' switch to new book
NB.Activate
Rows(CrowP & ":" & CrowP).Select
'Paste the copied row
Selection.PasteSpecial Paste:=xlPasteAll
' increments the row counter for the new workbook to paste in next row
CrowP = CrowP + 1
'switch back to orig book
wb.Activate
'when it finds an overdue it will set the variable
'which will be used to determine if it will send below
SendChk = "Sendme"
Else
' clears the cell in column 5
Cells(RowC, 5) = ""
End If
Next RowC
' generates an email
If SendChk = "Sendme" Then
NB.Activate
' format headers
Cells(1, 1) = "Client"
Cells(1, 2) = "Location"
Cells(1, 3) = "Date of Most Recent File"
Cells(1, 4) = "Date of Next Required File"
Cells(1, 5) = "Immediately Required"
Cells(1, 6) = "Next File Requested"
Range("A1:F1").Interior.Color = RGB(255, 255, 0)
Columns("A:H").EntireColumn.AutoFit
Email_1 = "user@somewhere.com"
SubJ = "Subject Goes Here"
ActiveWorkbook.SendMail Email_1, SubJ, ReturnReceipt:=True
End If
NB.Close SaveChanges:=False
wb.Save
wb.Close
Application.ScreenUpdating = True
' turns off save confirmation
Application.DisplayAlerts = True
Application.Quit
End Sub
Status
Not open for further replies.
You have insufficient privileges to reply here.
Related Threads
?
?
?
?
?
Tech Support Guy
9.9M posts
859.7K members
Since 1998
A forum community dedicated to tech experts and enthusiasts. Come join the discussion about articles, computer security, Mac, Microsoft, Linux, hardware, networking, gaming, reviews, accessories, and more!