Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-26-2014, 01:56 PM
AmyScott AmyScott is offline Macro for submit email Windows 8 Macro for submit email Office 2013
Novice
Macro for submit email
 
Join Date: Dec 2014
Location: Flowood, MS
Posts: 2
AmyScott is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 12-26-2014, 11:04 PM
gmayor's Avatar
gmayor gmayor is offline Macro for submit email Windows 7 64bit Macro for submit email Office 2010 32bit
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

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.
Reply With Quote
  #3  
Old 12-29-2014, 09:28 AM
AmyScott AmyScott is offline Macro for submit email Windows 8 Macro for submit email Office 2013
Novice
Macro for submit email
 
Join Date: Dec 2014
Location: Flowood, MS
Posts: 2
AmyScott is on a distinguished road
Default

Yay! That worked. Thanks, Graham.
Reply With Quote
Reply

Tags
macro email, submit button

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro for submit email VBA Code for a SUBMIT button in a Word form BlueMax Word VBA 3 08-07-2013 06:26 AM
Macro for submit email 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
Macro for submit email How to search for email addresses lacomputech Word 2 12-10-2009 08:33 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:37 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft