1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

sending excel sheet via email autmatically

Discussion in 'Business Applications' started by deb0and, Mar 20, 2008.

Thread Status:
Not open for further replies.
  1. deb0and

    deb0and Thread Starter

    Oct 17, 2007
    Hi is there a way to send an automatic email of a workbook on a certain date?

    For example workbook 1 needs to be sent at the end of every month automatically when opened.

  2. bomb #21

    bomb #21

    Jul 1, 2005
    "the end of every month"

    Is that the last day or the last working day? If the latter, define "working".

    The Analysis Toolpak add-in (you'd have to check if you have it installed) has some useful additional functions, such as EOMONTH.

    Today (20th March), the formula:


    returns 8. That probably means that there are 8 working days left this month, including today. Of course, this month is a bad example because of Easter; NETWORKDAYS' 3rd argument "Holidays" is something you'd have to plug in.

    Some very basic "workbook open" code could check the value of such as formula and act accordingly (i.e. if formula result = 1, do x-y-z). You might also need to set a flag to handle the workbook being opened > once on the "last" day.

    Beyond that (or maybe even instead of), Ron de Bruin has lots of snazzy SendMail stuff.

  3. Rollin_Again


    Sep 4, 2003
    As Bomb has already mentioned we need clarification on what defines "the end of the month"

    Some basic code to accomplish what you want is listed below but this code assumes that the workbook will be opened exactly on the last day of each month. If the workbook is not opened on exactly the last day of the month it will not be automatically emailed. Please provide more details on whether logic should be included to factor in weekend days, holidays, etc. If you truly and literally mean the last day of the month you can use the code I provided below.

    Open Excel and right click the Excel icon in the top left corner of the screen next to the FILE menu and select VIEW CODE and paste the code below into the module and save.

    Private Sub Workbook_Open()
    If Day(Date) = Day(DateSerial(Year(Date), Month(Date) + 1, 1) - 1) Then
    ActiveWorkbook.SendMail Recipients:="[email protected]"
    End If
    End Sub
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/695144