?
Solved

Email different people based on rows in spreadsheet

Posted on 2011-04-29
17
Medium Priority
?
498 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:mbibmdi
  • 9
  • 8
17 Comments
 
LVL 1

Expert Comment

by:simonwait
ID: 35493855
Could you provide a sample workbook?
0
 
LVL 1

Expert Comment

by:simonwait
ID: 35493968
Sorry dont need workbook.  Didnt see the top of your message.  Have the information I need now
0
 

Author Comment

by:mbibmdi
ID: 35493997
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Expert Comment

by:simonwait
ID: 35494110
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
 

Author Comment

by:mbibmdi
ID: 35494265
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
 
LVL 1

Expert Comment

by:simonwait
ID: 35494346
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
 

Author Comment

by:mbibmdi
ID: 35494831
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
 

Author Comment

by:mbibmdi
ID: 35494888
do I just paste your code in there with his code to get it to work?
0
 
LVL 1

Expert Comment

by:simonwait
ID: 35497003
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
 
LVL 1

Expert Comment

by:simonwait
ID: 35497054
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
 

Author Comment

by:mbibmdi
ID: 35499334
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
 

Author Comment

by:mbibmdi
ID: 35499336
I use Rackspace Email as my external email server by the way.  Not sure if you're familiar with them.
0
 
LVL 1

Expert Comment

by:simonwait
ID: 35508006
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
 
LVL 1

Accepted Solution

by:
simonwait earned 2000 total points
ID: 35508415
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
 

Author Comment

by:mbibmdi
ID: 35515751
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
 
LVL 1

Expert Comment

by:simonwait
ID: 35515855
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
 

Author Comment

by:mbibmdi
ID: 35516005
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This article will help to fix the below errors for MS Exchange Server 2016 I. Certificate error "name on the security certificate is invalid or does not match the name of the site" II. Out of Office not working III. Make Internal URLs and Externa…
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question