Mail Merge Excel without Word

By Jessica Lynn ·
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

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).


This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

VBA is an answer

by vba66a In reply to Mail Merge Excel without ...

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
End If
End Sub
Sub Test()
SendEmail "someoneemail@somewhere", "Check <a href=''>this</a> out!"
End Sub

Related Discussions

Related Forums