Davers
Well-known Member
- Joined
- Sep 17, 2002
- Messages
- 1,165
I thought for sure I had posted this question here...but I can't seem to find it. 
I've created a "Time-Off Request Form" in Excel...so much nicer looking than HR's version they created in Word... Anyway, "MY" version creates an E-mail with the form in the body as html. And wouldn't it be cool if you could include the VotingOptions buttons on the e-mail so they could either "Accept" it or "Reject" it and have it send the response to the sender...well...I finally figure it out....I just needed to add one line to the code below:
The line I added was:
It took me almost 5 days to figure out that tiny little line!
It actually creates the e-mail with the form as the body, and includes the voting buttons...which actually work!!! yay!!!
Just thought I'd post this in case anyone else ever wondered about those pesky voting buttons!
Have a good day,
Dave M
I've created a "Time-Off Request Form" in Excel...so much nicer looking than HR's version they created in Word... Anyway, "MY" version creates an E-mail with the form in the body as html. And wouldn't it be cool if you could include the VotingOptions buttons on the e-mail so they could either "Accept" it or "Reject" it and have it send the response to the sender...well...I finally figure it out....I just needed to add one line to the code below:
Code:
Sub SendMail()
Module3.unlockSht
Worksheets("Sheet1").Select
Range("A1").Select
'Sends a specified range in an Outlook message and retains Excel formatting
'References needed :
'Microsoft Outlook Object Library
'Microsoft Scripting Runtime
Dim olApp As Outlook.Application, olMail As Outlook.MailItem
Dim FSObj As Scripting.FileSystemObject, TStream As Scripting.TextStream
Dim rngeSend As Range, strHTMLBody As String
Dim strTempFilePath As String
Dim nowTime As String
Dim empName As String
nowTime = Format(Range("J4").Value, "dddd-dd-mmm-yyyy")
empName = Range("empName").Value
On Error Resume Next
Set rngeSend = Application.Range("C5:F47")
Set oFSObj = CreateObject("Scripting.FilesystemObject")
strTempFilePath = oFSObj.GetSpecialFolder(2)
strTempFilePath = strTempFilePath & "\XLRange.htm"
ActiveWorkbook.PublishObjects.Add(xlSourceRange, strTempFilePath, rngeSend.Parent.Name, rngeSend.Address, xlHtmlStatic).Publish True
Set olApp = CreateObject("Outlook.Application")
Set olMail = olApp.CreateItem(olMailItem)
Set FSObj = New Scripting.FileSystemObject
Set TStream = FSObj.OpenTextFile(strTempFilePath, ForReading)
strHTMLBody = TStream.ReadAll
TStream.Close
Kill strTempFilePath
olMail.HTMLBody = strHTMLBody
olMail.VotingOptions = "Accept;Reject"
olMail.Subject = ("Time off request for " & empName & nowTime)
'olMail.To = ("")
'olMail.CC = ("")
'Will display the e-mail before sending, otherwise it will just send
olMail.Display
'Close Objects
Set FSObj = Nothing
Set olApp = Nothing
Set olMail = Nothing
Set TStream = Nothing
Module3.lockSht
End SubThe line I added was:
Code:
olMail.VotingOptions = "Accept;Reject"It took me almost 5 days to figure out that tiny little line!
It actually creates the e-mail with the form as the body, and includes the voting buttons...which actually work!!!
yay!!!Just thought I'd post this in case anyone else ever wondered about those pesky voting buttons!
Have a good day,
Dave M
