There's no such thing as a stupid question, but they're the easiest to answer.


Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

sending excel sheet via email autmatically


deb0and's Avatar
deb0and deb0and is offline
Member with 53 posts.
Join Date: Oct 2007
20-Mar-2008, 06:10 AM #1
sending excel sheet via email autmatically
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.

bomb #21's Avatar
Member with 8,268 posts.
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
20-Mar-2008, 07:34 AM #2
"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.

Rollin_Again's Avatar
Member with 4,726 posts.
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Advanced
20-Mar-2008, 07:47 AM #3
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:=""

End If

End Sub
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine

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.

Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Thread Tools

You Are Using: Server ID
Trusted Website Back to the Top ↑