E-mail spreadsheet

[tenbellies]

aaarghhhh can any one help, I have to write a macro in excel that when the spreadsheet opens it automatically runs a macro rereshs itself and e-mails itself out.

The refresh i have sorted but can i get it to run the macro on open and then e-mail out to a distribution list?

 

[SkipVought]

Hi,

Take a look at the Workbook_Open event.

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[benaround2]

You may want to try the following url for the excel send mail issue. I modified this sites code and it worked great.

http://www.exceltip.com/excel_tips/Mail_-_Send_and_Receive_in_VBA/211.html

 

[longhair]

tenbellies,
there have been numerous posts here regarding this, i believe. but to get you started:

Sub Auto_Open()
    Application.Run "dsnocoitm.xls!Menus"
End Sub

will run the macro to put it on the tool bar whenever the spreadsheet is open

Sub Menus()
With MenuBars("Worksheet")
    .Reset
    .Menus.Add ("&Macros")
    With .Menus("&Macros")
      .MenuItems.Add ("Macro1&1"), "Macro1"
    End With
  End With
  newHour = Hour(Now())
  newMinute = Minute(Now())
  newSecond = Second(Now()) + 1
  waitTime = TimeSerial(newHour, newMinute, newSecond)
  Application.Run "dsnocoitm.xls!Macro1"
End Sub

puts the macro on the toolbar waits 1 sec then actually runs the macro

Sub macro1()
Dim newHour As Variant
Dim newMinute As Variant
Dim newSecond As Variant
Dim waitTime As Variant

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 3
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
If IsNull(Application.MailSession) Then
 Application.MailLogon "MS Exchange Settings", , False
End If
   ChDir "C:\aafiles"
'code for refreshing your xls here
ActiveWorkbook.SendMail Recipients:=Array("user1@company.com", "user2@company.com")
Workbooks("wbname_" & Format(Now(), "yyyymmdd") & ".xls").Close SaveChanges:=False
Application.DisplayAlerts = True

Application.Quit
End Sub

how to get the data is left as an exercise for the user.
regards,
longhair