+ Reply to Thread
Results 1 to 4 of 4

Macro to save a Workbook and then e-mail that file from Outlook

  1. #1
    Registered User
    Join Date
    08-19-2017
    Location
    Brisbane Australia
    MS-Off Ver
    2010
    Posts
    2

    Macro to save a Workbook and then e-mail that file from Outlook

    Hi,

    I am not an experienced user as yet but have combined some code from various post to try and solve my problem.

    I want to save an Excel Workbook into a specific folder on the c Drive with a date attached to a fixed file name. I then want to export that specific file in Outlook to an e-mail address.

    The Code I am using fails on the line "Attachments.Add ActiveWorkbook.FullName"

    Hope someone can help me solve this.

    .

    Sub ExportOffice3()
    '
    ' ExportOffice3 Macro


    Dim dt As String, wbNam As String, path As String
    path = "C:\Daily Site Diary\Weekly Site Records\"
    wbNam = "Weekly Site Record_"
    dt = Format(CStr(Now), "dd_mm_yyyy")
    ActiveWorkbook.SaveAs Filename:=path & wbNam & dt & ".xlsm"


    Dim OutlookApp As Object
    Dim OutlookMailItem As Object
    Dim myAttachments As Object

    Set OutlookApp = CreateObject("OutLook.Application")
    Set OutlookMailItem = OutlookApp.CreateItem(0)
    Set myAttachments = OutlookMailItem.Attachments

    With OutlookMailItem
    .To = "[email protected]"
    .Subject = "Weekly Site Record"
    .Body = "Please find Attached the Weekly Site Record in Excel format."
    Attachments.Add ActiveWorkbook.FullName
    'send
    .Display
    End With


    Set OutlookMailItem = Nothing
    Set OutlookApp = Nothing

    '
    End Sub
    Last edited by Toby019#; 08-20-2017 at 09:33 PM.

  2. #2
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Macro to save a Workbook and then e-mail that file from Outlook

    Please amend your 1st post with Code Tags when posting code !!

    Try this way.

    Please Login or Register  to view this content.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  3. #3
    Registered User
    Join Date
    08-19-2017
    Location
    Brisbane Australia
    MS-Off Ver
    2010
    Posts
    2

    Re: Macro to save a Workbook and then e-mail that file from Outlook

    Thank you. That fixed the problem. Great work

  4. #4
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Macro to save a Workbook and then e-mail that file from Outlook

    You're welcome and thanks for rep+.

    If this answers your question please mark the thread as Solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Outlook Macro to save mail attachment from a specific sender with subject line
    By manoj.0790 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-17-2016, 09:34 AM
  2. [SOLVED] Macro to Save Excel File to a SharePoint location and then e-mail that location in Outlook
    By NickyHavey in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-11-2016, 09:27 AM
  3. [SOLVED] screenshot text file and embed in outlook mail through excel with mail info
    By bmbalamurali in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 01-11-2015, 08:21 PM
  4. code to save attachment from Outlook mail
    By prabhuduraraj09 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-22-2014, 07:11 AM
  5. Replies: 5
    Last Post: 03-21-2013, 07:34 AM
  6. Outlook - Save Worksheet as PDF and attach to outlook mail
    By kaurka in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-05-2012, 10:19 AM
  7. [SOLVED] Macro to save mail merged word docs to users desktop and with a desired file name
    By bonny24tycoon in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-22-2012, 12:27 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1