Email different people based on rows in spreadsheet

Hi Experts,

I have payroll information in each row of a spreadsheet:
Employee Name
Employee email address
Vacation Accrued
Sick Time Accrued

I would like to be able plug stuff in the spreadsheet and then run an automated routine (preferably in VBA), to send the information in each row to each individual employee and keep individually sending each row until there is no Employee in there.  Can anyone help me out?  Also, this would have to be pretty bulletproof, because don't want to piss employees off by showing how much another employee has available.  Or maybe I shouldn't include their name in the email, so they wouldn't know who it was in case it screwed up?  Thanks for your help.
mbibmdiAsked:
Who is Participating?
 
simonwaitCommented:
Try this.  You will need to put your email address into the code and maybe change the port or server addresses I used (i got this info from http://www.rackspace.com/apps/support/portal/1088

It wont prompt you so be careful in testing that you nuse your email addresses rather than everyone elses!

Cheers

Simon
Sub sendmail()

    Dim iMsg As Object
    Dim iConf As Object
    Dim Flds As Variant
    'Dim olApp As Object
    'Dim olMsg As Object
    Dim strBody As String
    
ServerAdd = "secure.emailsrvr.com"
EmailAdd = "you@rackspace.com" 'Put your email address here
emailPassword = InputBox("Please enter your email password for " & EmailAdd)

If emailPassword = "" Then
Exit Sub
End If

lastrow = Sheets(1).UsedRange.Rows.Count

For x = 2 To lastrow

EmployeeName = Sheets(1).Range("A" & x).Value
EmployeeEmail = Sheets(1).Range("B" & x).Value
Vacation = Sheets(1).Range("C" & x).Value
Sick = Sheets(1).Range("C" & x).Value
strBody = "Dear " & EmployeeName & "," & "<BR>" & "<BR>" & _
    "Please find below your current vacation and sickness accruals:-" & "<BR>" & "<BR>" & _
    "Vacation: " & Vacation & "<BR>" & _
    "Sickness: " & Sick & "<BR>" & "<BR>" & _
    "Thank You"
    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")


    iConf.Load -1    ' CDO Source Defaults
        Set Flds = iConf.Fields
        With Flds
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = ServerAdd
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = EmailAdd
            .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = emailPassword
            .Update
        End With

strHTML = "<HTML>"
strHTML = strHTML & "<HEAD>"
strHTML = strHTML & "<BODY>"
strHTML = strHTML & "<b> " & strBody & "</b></br>"
strHTML = strHTML & "</BODY>"
strHTML = strHTML & "</HTML>"


    With iMsg
        Set .Configuration = iConf
        .To = EmployeeEmail
        .From = EmailAdd
        .Subject = "Your vacation and sickness accrual"
        .HTMLBody = strHTML
        .Send
    End With

'Set olApp = CreateObject("Outlook.Application")
'Set olMsg = olApp.CreateItem(0)
'With olMsg
'    .To = EmployeeEmail
'    .Subject = "Your vacation and sickness accrual"
'    .Body = "Dear " & EmployeeName & "," & vbNewLine & vbNewLine & "Please find below your current vacation and sickness accruals:-" & vbNewLine & vbNewLine & "Vacation: " & Vacation & vbNewLine & "Sickness: " & Sick & vbNewLine & vbNewLine & "Thank You"
'    .Display
'End With

'Set olMsg = Nothing
'Set olApp = Nothing
Next x
End Sub

Open in new window

0
 
simonwaitCommented:
Could you provide a sample workbook?
0
 
simonwaitCommented:
Sorry dont need workbook.  Didnt see the top of your message.  Have the information I need now
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
mbibmdiAuthor Commented:
This professor did it using Google Docs and Email.  
http://www.jose-vidal.com/

I'd like to do it in Outlook if possible.  Otherwise I'll do it with Google using his code.
0
 
simonwaitCommented:
How about this? EmployeeSickVac.xlsm
Sub sendmail()

Dim olApp As Object
Dim olMsg As Object

lastrow = Sheets(1).UsedRange.Rows.Count

For x = 2 To lastrow

EmployeeName = Sheets(1).Range("A" & x).Value
EmployeeEmail = Sheets(1).Range("B" & x).Value
Vacation = Sheets(1).Range("C" & x).Value
Sick = Sheets(1).Range("C" & x).Value


Set olApp = CreateObject("Outlook.Application")
Set olMsg = olApp.CreateItem(0)
With olMsg
    .to = EmployeeEmail
    .Subject = "Your vacation and sickness accrual"
    .Body = "Dear " & EmployeeName & "," & vbNewLine & vbNewLine & "Please find below your current vacation and sickness accruals:-" & vbNewLine & vbNewLine & "Vacation: " & Vacation & vbNewLine & "Sickness: " & Sick & vbNewLine & vbNewLine & "Thank You"
    .Display
End With

Set olMsg = Nothing
Set olApp = Nothing
Next x
End Sub

Open in new window

0
 
mbibmdiAuthor Commented:
Wow, impressive Simon!  Worked perfectly!  It doesn't send the emails.  Just creates them, waiting for me to check them and hit send button.  At the risk of sending them to the wrong person (I wouldn't put the name in there), how would I automatically send them too?  Thanks a lot.
0
 
simonwaitCommented:
You could change the .Display bit with:-

.Save
.Send

however outllook doesnt really like being told to send things out without being given a reason why it should!  Maybe look at http://www.everythingaccess.com/tutorials.asp?ID=Outlook-Send-E-mail-Without-Security-Warning for further info.  Another solution may be to use CDO to send the email depending if your email servers allow it.
0
 
mbibmdiAuthor Commented:
Yea, now I'm trying to shut off the warning message.  I see that code you referenced.  I'm not sure how to run both your code and that code.  Any hints?

0
 
mbibmdiAuthor Commented:
do I just paste your code in there with his code to get it to work?
0
 
simonwaitCommented:
Are you ok to reduce the security level on your Excel though.  This method does potentially open you up to other code with not quite so nice intentions!
0
 
simonwaitCommented:
The more I think about it the CDO may be a better solution if your email server supports relaying.  If it does then this is the way forward without compromising too much
0
 
mbibmdiAuthor Commented:
Regarding your first post, I was hoping to open up the Security, send the messages, and then put the Security back on.  Not leave it open.  

I checked out the CDO like you said.  Don't I have to put the username and password in the script?  This seemed insecure to me.  Maybe I'm reading it wrong.  Thanks for all your help Simonwait.  I'll be giving you the full 500 points regardless.
0
 
mbibmdiAuthor Commented:
I use Rackspace Email as my external email server by the way.  Not sure if you're familiar with them.
0
 
simonwaitCommented:
My thoughts were to ask for the username and/or password at the time of clicking the macro and then use those details so that you keep some control.  Rackspace appears to allow unlimited smtp relaying providing it is within their acceptable use policy which Im sure this does.

Will have a look into the code side of things for you to have a try

0
 
mbibmdiAuthor Commented:
Yea, that's awesome simonwait.  I can't kludged together the same thing from the sources you gave me.  Thanks for your help.  I'm kind of concerned about putting the email password in the code.  How would I ask for a popup box to ask for a password for the emailPassword variable?
0
 
simonwaitCommented:
This line in the code should have done the asking for the password bit.  Thanks for the points and good luck!

emailPassword = InputBox("Please enter your email password for " & EmailAdd)
0
 
mbibmdiAuthor Commented:
Wow, I really wasn't paying attention.  Yours is slicker than mine.  Very nice.  I expect you'll be moving up the ranks quickly.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.