Excel and emailing using Macro

[Guest]

Hi

I am using excel 2000 and am trying to automatically send an email on a button press, button is called status update

on the press of this button it should unhide columns K-S select 4 cells O12,013,014,015 all with formulas of =m12 through 15 in. I then need this to be copied and paste as values into an email and sent. I then need the macro to rehide the columns and return to cell a1 in the sheet. I have tried adapting Ron de Bruins code as below but get a debug error. Any help would be greatly appreciate

Sub Mail_Selection_Outlook_Body(
'Is not working in Office 9
Dim source As Rang
Dim dest As Workboo
Dim myshape As Shap
Dim OutApp As Outlook.Applicatio
Dim OutMail As Outlook.MailIte

Set source = Nothin
On Error Resume Nex
Set source = Selection.Range("o1215"
Cells.Cop
Cells.PasteSpecial xlPasteValue
Cells(1).Selec
Application.CutCopyMode = Fals
On Error GoTo
If source Is Nothing The
MsgBox "The selection is not a range or the sheet is protect, please correct and try again.", vbOKOnl
Exit Su
End I
If ActiveWindow.SelectedSheets.Count > 1 Or
Selection.Cells.Count = 1 Or
Selection.Areas.Count > 1 The
MsgBox "An Error occurred :" & vbNewLine & vbNewLine &
"You have more than one sheet selected." & vbNewLine &
"You only selected one cell." & vbNewLine &
"You selected more than one area." & vbNewLine & vbNewLine &
"Please correct and try again.", vbOKOnl
Exit Su
End I

Application.ScreenUpdating = Fals
ActiveSheet.Cop
Set dest = ActiveWorkboo
For Each myshape In dest.Sheets(1).Shape
myshape.Delet
Nex
Set OutApp = CreateObject("Outlook.Application"
Set OutMail = OutApp.CreateItem(olMailItem
With OutMai
.To = "email adress witheld
.CC = "
.BCC = "
.Subject = "This is the Subject line
.HTMLBody = RangetoHTM
.Send 'or use .Displa
End Wit
dest.Close Fals
Set OutMail = Nothin
Set OutApp = Nothin
Set dest = Nothin
Application.ScreenUpdating = Tru
End Su

Function RangetoHTML(
Dim fso As Objec
Dim ts As Objec
Dim TempFile As Strin
TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm

With ActiveWorkbook.PublishObjects.Add(
SourceType:=xlSourceRange,
Filename:=TempFile,
Sheet:=ActiveSheet.Name,
source:=Selection.Address,
HtmlType:=xlHtmlStatic
.Publish (True

End Wit
Set fso = CreateObject("Scripting.FileSystemObject"
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2
RangetoHTML = ts.ReadAl
ts.Clos
Set ts = Nothin
Set fso = Nothin
Kill TempFil
End Functio

 

[Bob Phillips]

Ade,

When you say an error, do you mean that MsgBox in the code? If so, it could
be because you issue this statement in the code

Cells(1).Select


and then test for this

Selection.Cells.Count = 1 Or _

and kick it out if so. In other words your code says that more than 1 cell
must be selected, but you also select one cell, overriding any user
selection.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Hi,

I am using excel 2000 and am trying to automatically send an email on a

button press, button is called status update.

on the press of this button it should unhide columns K-S select 4 cells

O12,013,014,015 all with formulas of =m12 through 15 in. I then need this
to be copied and paste as values into an email and sent. I then need the
macro to rehide the columns and return to cell a1 in the sheet. I have
tried adapting Ron de Bruins code as below but get a debug error. Any help
would be greatly appreciated

Sub Mail_Selection_Outlook_Body()
'Is not working in Office 97
Dim source As Range
Dim dest As Workbook
Dim myshape As Shape
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem

Set source = Nothing
On Error Resume Next
Set source = Selection.Range("o1215")
Cells.Copy
Cells.PasteSpecial xlPasteValues
Cells(1).Select
Application.CutCopyMode = False
On Error GoTo 0
If source Is Nothing Then
MsgBox "The selection is not a range or the sheet is protect,

please correct and try again.", vbOKOnly

 

[Ron de Bruin]

Hi Ade

Why you want to do a paste special if you send it in the body of the mail???
I don't see code that unhide the columns

you say <should unhide columns K-S>
but you only want to send cells from O



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)

Hi,

I am using excel 2000 and am trying to automatically send an email on a button press, button is called status update.

on the press of this button it should unhide columns K-S select 4 cells O12,013,014,015 all with formulas of =m12 through 15

in. I then need this to be copied and paste as values into an email and sent. I then need the macro to rehide the columns and
return to cell a1 in the sheet. I have tried adapting Ron de Bruins code as below but get a debug error. Any help would be
greatly appreciated