+ Reply to Thread
Results 1 to 4 of 4

Send a monthly email that has the same file attached, the attached file is updated daily.

  1. #1
    Forum Contributor
    Join Date
    04-05-2010
    Location
    Lakewood, IL
    MS-Off Ver
    Excel 2016
    Posts
    384

    Send a monthly email that has the same file attached, the attached file is updated daily.

    I am not sure exactly where this question belongs. As it might be VBA code in Outlook. I have an Excel macro that generates a report and saves the results in a table that is an Excel file. I would like to know how to create a macro or possibly a rule that would at the first day of the month send an email with this Excel table attached to a specific email address automatically. The Excel Table is always in the same directory.

    Thanks in advance,

    Bobbbo

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,876

    Re: Send a monthly email that has the same file attached, the attached file is updated dai

    There is a way to do this, but we will need more information.

    The Widows Task Scheduler has the capability to open an Excel Spreadsheet, but it cannot run a macro in that spreadsheet. However, an Excel Spreadsheet (Let's call this spreadsheet the script) can open another spreadsheet and run a macro in it (Let's call this spreadsheet the target).

    The target is a normal report. In your case it will contain code (a macro) to mail the spreadsheet to the distribution list as an attachment.

    I invented a script program that takes the path name to the target, the file name of the target and the macro inside the target that is to be executed. The script program also produces the command string to be used by the Windows Task Scheduler to call itself.

    I keep all my scripts in one directory and all my targets in subdirectories of a separate target directory. It's just easier to manage that way.

    The steps are simple, you make a copy of the script template and rename it. I suggest naming it to match the name of the target program. Fill in the blanks and copy the command string into notepad or some other text editor.

    Then open the Windows Task Manager and set up the task. Copy/paste the command string into the actions section.

    When the first of the month happens the Widows Task Manager opens the script spreadsheet which has an On Open trigger to call the target spreadsheet and run the macro in it.

    I've attached half of the solution in _Control Template.zip. This file contains the Task Manager pieces. The other half of the solution is contained in ModMailerMain.zip. When unzipped this is a *.BAS file that contains a mail module. You'll have to write some simple code to feed it things like who to mail to, what the subject is, the body of the message and what the attachments should be.
    Attached Files Attached Files
    Last edited by dflak; 02-10-2024 at 08:52 PM.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Contributor
    Join Date
    04-05-2010
    Location
    Lakewood, IL
    MS-Off Ver
    Excel 2016
    Posts
    384

    Re: Send a monthly email that has the same file attached, the attached file is updated dai

    Thanks dflak, I already have VBA code to email an attachment to specific person. My problem is getting the VBA code to run on a specific date. Sounds like you have given me that answer. Let me work with what you have given me and I will get back to you with my results.

    Thanks in advance!!

  4. #4
    Forum Contributor
    Join Date
    04-05-2010
    Location
    Lakewood, IL
    MS-Off Ver
    Excel 2016
    Posts
    384

    Re: Send a monthly email that has the same file attached, the attached file is updated dai

    Hi dflak,

    I finally have some time to work on your suggested solution and it all makes sense. In your Word document you have a sample workbook that I believe I need to play with. You stated "The following program is used in this exercise. It resides in C:\Users\dflak\Documents\Home\Memos\Technical\Temp and is called Sample workbook table.xlsm.". You did not include the Excel workbook: workbook table.xlsm in your attachments. Don't I need this file? If so can you send that to me?

    Best regards,

    Bob

+ 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. [SOLVED] Need to be able to send email with attached PDF file based on a list
    By jkhuskies in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-09-2019, 03:55 AM
  2. Can i send automatic mails to certain emails with a file attached?
    By chnarakisa in forum Outlook Programming / VBA / Macros
    Replies: 6
    Last Post: 09-05-2015, 06:16 AM
  3. Keep a PDF file after it's been attached to email
    By MedicP3 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-28-2014, 01:58 AM
  4. Help on Monthly Summary (File attached)
    By MIchaelb1100 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-10-2013, 11:12 AM
  5. Send excel file attached in email
    By vijanand1279 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-06-2013, 03:55 AM
  6. RTE 91 only when file is attached to email.
    By modytrane in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-10-2013, 03:28 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