Macro for submit email

AmyScott · #1 12-26-2014, 01:56 PM

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!

gmayor · #2 12-26-2014, 11:04 PM

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

AmyScott · #3 12-29-2014, 09:28 AM

Yay! That worked. Thanks, Graham.

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