We want to be able to easily send out personalized emails to staff with their PTO balance.
I started with the M$ attendance-tracking template to determine how many days staff members have taken off, and used other worksheet to determine how many PTO days staff are eligible for (we have a funky accural calc).
So right now, I have a worksheet that has Name, email address, # of PTO days used, # PTO days remaining.
I want to send each staff member a personalized email:
Dear: {{name}}
You have used {{# of PTO days used}} leaving you a balance of {{# PTO days}}.
I've been messing with the Word email/merge but it kinda sucks. I can't seem to save the email address and subject. I'd like to have something that is sort of push a button to send out the update. We have our admins doing this for a large number of people and I don't want to make this complex for them.
Ideally, it would be a button in Excel they could double click that says "click here to send out update".
I like this mailmerge script in Excel http://j-walk.com/ss/excel/tips/tip92.htm
But can't seem to translate this to an email.
Another option is to be able to set up a word template so they can open it and it runs. Right now, they need to do a mail merge each time (annoying).
Help
- Follow via:
- RSS
- Email Alert
Question
0
Votes
Answers (1)
0
Votes
VBA is an answer
You can start with mailmerge script (Excel VBA) and modify it to send emails. For example, if you have Outlook as you MAPI client:
Public Sub SendEmail(sTo As String, sSubj As String, sBody As String)
Dim olApp 'As Outlook.Application
Dim oNewMail 'As MailItem
If Application.MailSystem = 1 Then ' xlMAPI
Set olApp = CreateObject("Outlook.Application")
Set oNewMail = olApp.CreateItem(0) ' olMailItem
oNewMail.To = sTo
oNewMail.Subject = sSubj
oNewMail.HTMLBody = sBody
oNewMail.Send
End If
End Sub
Sub Test()
SendEmail "someoneemail@somewhere", "Check this out!"
End Sub
Public Sub SendEmail(sTo As String, sSubj As String, sBody As String)
Dim olApp 'As Outlook.Application
Dim oNewMail 'As MailItem
If Application.MailSystem = 1 Then ' xlMAPI
Set olApp = CreateObject("Outlook.Application")
Set oNewMail = olApp.CreateItem(0) ' olMailItem
oNewMail.To = sTo
oNewMail.Subject = sSubj
oNewMail.HTMLBody = sBody
oNewMail.Send
End If
End Sub
Sub Test()
SendEmail "someoneemail@somewhere", "Check this out!"
End Sub
3rd Jan 2008
