• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Alert Pop up

R

Raja Kamarthi

New Member
Hello All,


I have a file which talks about the data of the new recruitments along with their joining dates.


Can I have an option where there would be some kind of "ALERT" poping up exactly a day before the joining date of the employee even when my report file is inactive/closed state?


This would be of much help to me ensuring that I dont miss on any of the new joiners


Please refer to the sample file to have a glance of the report


https://www.dropbox.com/s/ebj5imyf78r4gyz/Sample%20File.Recruitments.xlsm?m


Regards,

Raja
 
I don't think it can be done with just a closed file. We can either have another file which reads from the closed file and acts upon that information, or we can write an Auto Open macro for the file itself, which checks if there are any incoming employees. Both options will require some VB skills. If this is acceptable, which route would you like to pursue?
 
Hello Luke,


Much Thanks for your response.


Option 2 (Auto Open) sounds good to me.


Furthermore, would it be possible to auto generate an E Mail to the respective new joinee on the day before his joining date. I would be including the E Mail ID details in the report?


Thanks in advance,

Raja
 
I'd check out these templates of Chandoo then, either a basic hyperlink template:

http://chandoo.org/wp/2010/10/26/birthday-reminder-template/


Or this more VB intensive version:

http://chandoo.org/wp/2012/04/23/send-mails-using-excel-vba-and-outlook/


The latter gives you more control over what is in message and stuff, but there's a lot to cover.


There's also several links at the bottom to related articles which might prove useful.
 
Hello Luke,


Considering my skill level of VBA scripting, this is something beyond my brains.


The 1st link is a basic one which needs manual intervention for generating an auto email and the 2nd link is rocket science,too hard to crack


Unfortunately both the links couldn't cater my need.


Can we look at having an simple code which can create an "Alert Pop up" and generate an E Mail as well?


Regards,

Raja
 
Okay, I think I found a work around by modifying the Birthday reminder template. I'll be using the cell references from there so you can follow along. On the birth-day reminder worksheet, remove all the formulas from the Mail Link column. We'll be using this column to flag which emails have already been sent. Change the subject field and Message field to whatever you want (keep it generic). We won't be using the From field.


Now, open up the VBE. In the ThisWorkbook module, copy this code:

[pre]
Code: 
Private Sub Workbook_Open()
Dim lastCell As Long
Dim msgString As String
With Worksheets("birth-day reminder")
lastCell = .Cells(.Rows.Count, "C").End(xlUp).Row
For i = 9 To lastCell
If .Cells(i, "C") - 1 <= Date And .Cells(i, "E").Value = "" Then
MsgBox "Need to send email!", vbOKOnly 'Not sure if you need, but creates pop-up

msgString = "mailto:" & .Cells(i, "D").Value & _
"?subject=" & .Range("C4").Value & _
"&body=" & .Range("C5").Value

'translate to correct HTML
msgString = WorksheetFunction.Substitute(WorksheetFunction.Substitute(msgString, _
" ", "%20"), Chr(10), "%0A")
ThisWorkbook.FollowHyperlink msgString

'add a flag so we don't send any more to same person
.Cells(i, "E").Value = "LETTER SENT"
End If
Next i
End With

End Sub
[/pre]
Everything should be set now. Note that this will only create the emails, user will still need to press send.
 
Hello Raja Kamarthi,


I do not have outlook set in my home desktop so I am not able to test the code, but it should be working fine.


First, in VB editor from tools menu, click on Tools menu and then hit reference and the look for the below references and check them:


1. Microsoft Outlook 12.0 Object Library

2. Microsoft Scripting Runtime Library


I have made some additions / adjustments to your workbook by doing the following:


1) Make few dates(highlighted in yellow) prior to today's date

2) Add dummy e-mail IDs in Col I

3)Added another column (Col J) named as "E-mail send status"


The code that you need to work with is:

[pre]
Code: 
Sub SendEmail()

'Declare variables

Dim X As Long
Dim Y As Long
Dim Cntr As Long
Dim OutApp As Object
Dim OutMail As Object

'Run the loop through all the dates till the last row

For Cntr = 2 To Sheet1.Range("A" & Rows.Count).End(xlUp).Row

'Store the date temporarily while running the loop
X = Sheet1.Range("G" & (Cntr)).Value

'Store the today's date
Y = Date

'Check if today's date is day before to the joining day or not

If Y - 1 = X Then

'If true then initiate the e-mail action

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next

With OutMail

'Pick the respective e-mail ID to whom the e-mail needs to be sent
'Mention the subject line
'Write the content of the e-mail

.To = Sheet1.Range("I" & (Cntr)).Value
.Subject = "Joining Reminder"
.Body = "Dear" & " " & Sheet1.Range("E" & (Cntr)).Value & vbNewLine & vbNewLine & _
"Tomorrow is your joining date"
.Send

End With
On Error GoTo 0

'After sending the e-mail, mention send at the corresponding cell at Col J
Sheet1.Range("J" & (Cntr)).Value = "Send"
Set OutMail = Nothing

End If

Next

End Sub
[/pre]

I have mentioned comments throughout the code wherever necessary and also added some comments in the worksheet as instruction.


However, here is the completed workbook for your kind perusal.

http://speedy.sh/BeF2z/Sample-File.Recruitments.xlsm


Best regards

Kaushik
 
No worries Kaushik. Always better to have more answers than none at all. Also, I noticed you took the time to write out a proper script using the Outlook reference in VB, while I just adapted Chandoo's mailto hyperlink. Pros and Cons to both, hopefully Raja can pick whichever works (or understands) best. Cheers!
 
@ Luke,


Thank you so much for your input.You have exactly delivered what I was looking for.


Just a bit of tweaking in the code to suit my need and the file would be good to go.


@Kaushik,


Your template goes that extra mile to have this auto send option which is amazing.


However there is onething which has to be fixed, Kaushik. Each time when I click on the "Send E Mail" button, Outlook initiates e mails to all the employers who have already received the mail.


For eg the template has sent a mail to Mr.X yesterday as his joining date is today and today when I have to send a mail to Mr.Y whose joining date is tomorrow and when I click on the "Send Mail", outlook triggers mails to Mr.X & Mr.Y which is not the case. Ideally mail should be sent only to Mr.Y


Can you please re look at the template and share a revised one?


I would be sharing both the templates with the top management and seek their comments


once again many thanks to both of you,


Regards,

Raja
 
Looks like Kaushik included a marking line in the code, just need to check for it in the if statement. Change this block

[pre]
Code: 
'Check if today's date is day before to the joining day or not

If Y - 1 = X Then
to this

'Check if today's date is day before to the joining day or not

If X - 1 <= Y And UCase(Sheet1.Cells(Cntr,"J")) <> "SEND" Then
[/pre]
If there's any lines that you don't want the email sent to, put the word "Send" in col J.
 
Sorry for coming back a bit late to the forum..!


@Raja Kamarthi:


Good point to notice. My apologies! I actually did not introduce any mechanism in the code to check that fact.However, our great Luke has already tweaked that in the IF AND clause. Just check and advise in case of any issue.But I think it should be fine now...


@Luke: I do really admire you a lot from very early days. Whatever, I have learnt till date, just because of the tremendous knowledge that you (other many people) have shared in order to enlighten us.


Kaushik
 
@Kaushik


Thank you for the kind words my friend. Likewise, it's nice to see members grow and learn, and answering questions themselves. =)
 
You must log in or register to reply here.
Back
Top