#1
|
|||
|
|||
Macro for submit email
I want to send my staff a timesheet with a submit button so they can fill it out and submit the completed excel spread sheet to myself as well as their direct supervisor and our HR department. I have created a submit button and attached a macro, but I just don't have the code right. I keep editing it and looking for extra examples online, but something is just not quite right. when I hit the submit button, I send an email with no attachment. My staff all use Outlook. This is my most recent stab ath VBC
Option Explicit Sub SubmitButton() Dim x As Outlook.Application Dim y As Outlook.MailItem Set x = CreateObject("Outlook.Application") Set y = oLapp.CreateItem(0) With y .Subject = "Timesheet Submitted" .CC = "themodernarc@gmail.com" .To = "amycraig@familyhealthcareclinic.com" .Attachments.Add "C:\Users\amy.craig\AppData\Local\Microsoft\Window s\INetCache\Content.Outlook\74BIHEUD\current submittable form.xlsx" .Display End With Set x = Nothing Set y = Nothing End Sub Thanks so much for help! Last edited by AmyScott; 12-26-2014 at 02:06 PM. Reason: decided to include code |
#2
|
||||
|
||||
Are you trying to send the active worksheet? In which case
Code:
Sub SubmitButton() Dim olApp As Object Dim olItem As Object Dim olInsp As Object Dim wdDoc As Object Dim oRng As Object Dim xlWorkBook As Workbook Set xlWorkBook = ActiveWorkbook xlWorkBook.Save Set olApp = CreateObject("Outlook.Application") Set olItem = olApp.CreateItem(0) With olItem .Subject = "Timesheet Submitted" .CC = "themodernarc@gmail.com" .To = "amycraig@familyhealthcareclinic.com" .BodyFormat = 2 .Attachments.Add xlWorkBook.FullName Set olInsp = .GetInspector Set wdDoc = olInsp.WordEditor Set oRng = wdDoc.Range(0, 0) oRng.Text = "Time sheet attached." .Display End With Set olItem = Nothing Set olApp = Nothing Set xlWorkBook = Nothing Set olInsp = Nothing Set wdDoc = Nothing Set oRng = Nothing End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com Last edited by gmayor; 12-27-2014 at 12:18 AM. |
#3
|
|||
|
|||
Yay! That worked. Thanks, Graham.
|
Tags |
macro email, submit button |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
VBA Code for a SUBMIT button in a Word form | BlueMax | Word VBA | 3 | 08-07-2013 06:26 AM |
Email submit that... | antipelagian | Word VBA | 3 | 02-12-2013 12:07 PM |
Macro to send document in email | AnneN | Word VBA | 1 | 02-05-2013 08:08 PM |
Word macro to email hyperlink | pooley343 | Word VBA | 0 | 07-20-2011 01:48 AM |
How to search for email addresses | lacomputech | Word | 2 | 12-10-2009 08:33 AM |