Thread: [Solved] Macro for submit email
View Single Post
 
Old 12-26-2014, 11:04 PM
gmayor's Avatar
gmayor gmayor is offline Windows 7 64bit Office 2010 32bit
Expert
 
Join Date: Aug 2014
Posts: 4,142
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 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