Send email thru Button command.....

  • Thread starter Thread starter Craig
  • Start date Start date
C

Craig

Hi does someone know the programming I can use within a
Sub Button1_Click()

End Sub

that can be used to email the contents of the spreadsheet
to a specific email address?
 
Any chance you could copy that info over to the newsgruop
as i am behind a firewall and cannot access many sites
outside our intranet??
 
From Dick Kusleika his site

You must add a reference to the Microsoft outlook Library to use
the example macro below.

How do you that:
1: Go to the VBA editor, Alt -F11
2: Tools>References in the Menu bar
3: Place a Checkmark before Microsoft Outlook ? Object Library
? is the Excel version number


Sub SheetInBody()
Dim olApp As Outlook.Application
Dim olMail As MailItem
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
With olMail
.To = "(e-mail address removed)"
.Subject = "Table in body"
.HTMLBody = SheetToHTML(ThisWorkbook.Sheets(1))
.Display
End With
Set olMail = Nothing
Set olApp = Nothing
End Sub


Public Function SheetToHTML(sh As Worksheet)
Dim TempFile As String
Dim fso As Object
Dim ts As Object
Randomize
sh.Copy
TempFile = sh.Parent.Path & "\TmpHTML" & Int(Rnd() * 10) & ".htm"
ActiveWorkbook.SaveAs TempFile, xlHtml
ActiveWorkbook.Close False
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
SheetToHTML = ts.ReadAll
ts.Close
Set ts = Nothing
Set fso = Nothing
Kill TempFile
End Function
 
Appreciate it!!!!


-----Original Message-----
From Dick Kusleika his site

You must add a reference to the Microsoft outlook Library to use
the example macro below.

How do you that:
1: Go to the VBA editor, Alt -F11
2: Tools>References in the Menu bar
3: Place a Checkmark before Microsoft Outlook ? Object Library
? is the Excel version number


Sub SheetInBody()
Dim olApp As Outlook.Application
Dim olMail As MailItem
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
With olMail
.To = "(e-mail address removed)"
.Subject = "Table in body"
.HTMLBody = SheetToHTML(ThisWorkbook.Sheets(1))
.Display
End With
Set olMail = Nothing
Set olApp = Nothing
End Sub


Public Function SheetToHTML(sh As Worksheet)
Dim TempFile As String
Dim fso As Object
Dim ts As Object
Randomize
sh.Copy
TempFile = sh.Parent.Path & "\TmpHTML" & Int(Rnd() * 10) & ".htm"
ActiveWorkbook.SaveAs TempFile, xlHtml
ActiveWorkbook.Close False
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, - 2)
SheetToHTML = ts.ReadAll
ts.Close
Set ts = Nothing
Set fso = Nothing
Kill TempFile
End Function



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)







.
Click to expand...
 
Back
Top