Hello from Steved

I got the below by going through Google.

The objective is to email a single Worksheet.

I'm getting a error

Invalid procedure call or argument.

Please help Thankyou.

Sub eMailActiveWorksheet()

Dim OL As Object ' Outlook instantiation
Dim EmailItem As Object ' A new mail item (e-mail)
Dim FileName As String ' The name of the file we are
attaching
Dim lngLoop As Long ' counter for the FileName length
Dim TempChar As String ' used for the loop to test for
unusable characters
Dim SaveName As String ' Attachment's new name, after
cleaning

Application.ScreenUpdating = False ' speed up Excel
processing time
Set OL = CreateObject("Outlook.Application") ' New
Outlook application
Set EmailItem = OL.CreateItem(olMailItem) ' new
MailItem
FileName = ActiveSheet.Name & "1-City" &
ActiveWorkbook.Name ' create a filename on the fly
For lngLoop = 1 To Len(1 - City) ' error check for
unusable chars in the filename
TempChar = Mid(1 - City, y, 1)
Select Case TempChar
Case Is = "/", "\", "*", "?", """", "<", "", "/"
Case Else
SaveName = SaveName & TempChar
End Select
Next lngLoop
ActiveSheet.Cells.Copy ' copy the contents of the
ActiveSheet
Workbooks.Add ' create a new workbook
Selection.PasteSpecial Paste:=xlValues ' paste the
worksheet values into the new book
Selection.PasteSpecial Paste:=xlFormats ' and their
formats
ActiveWorkbook.SaveAs "C:\Temp" & SaveName ' temp file
attachment location
ActiveWorkbook.ChangeFileAccess xlReadOnly ' make
access read only
With EmailItem ' with the newly created e-mail
.Subject = ActiveWorkbook.Name
.Body = "This is an example of a single worksheet
sent by VBa mail"
.To = "1-Depot"
.Importance = olImportanceNormal 'Or
olImportanceHigh Or olImportanceLow
.Attachments.Add "C:\Temp" & SaveName
.Send ' send the worksheet
End With
Kill "C:\Temp" & SaveName ' delete the temporary
attachment
ActiveWorkbook.Close False ' close down the workbook
without saving (single sheet)

Application.ScreenUpdating = True ' always remember to
switch it back on!!

Set OL = Nothing ' clean down memory
Set EmailItem = Nothing

End Sub

..

Hi Steved

Look for example code on my site
http://www.rondebruin.nl/sendmail.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Steved" wrote in message ...
Hello from Steved

I got the below by going through Google.

The objective is to email a single Worksheet.

I'm getting a error

Invalid procedure call or argument.

Please help Thankyou.

Sub eMailActiveWorksheet()

Dim OL As Object ' Outlook instantiation
Dim EmailItem As Object ' A new mail item (e-mail)
Dim FileName As String ' The name of the file we are
attaching
Dim lngLoop As Long ' counter for the FileName length
Dim TempChar As String ' used for the loop to test for
unusable characters
Dim SaveName As String ' Attachment's new name, after
cleaning

Application.ScreenUpdating = False ' speed up Excel
processing time
Set OL = CreateObject("Outlook.Application") ' New
Outlook application
Set EmailItem = OL.CreateItem(olMailItem) ' new
MailItem
FileName = ActiveSheet.Name & "1-City" &
ActiveWorkbook.Name ' create a filename on the fly
For lngLoop = 1 To Len(1 - City) ' error check for
unusable chars in the filename
TempChar = Mid(1 - City, y, 1)
Select Case TempChar
Case Is = "/", "\", "*", "?", """", "<", "", "/"
Case Else
SaveName = SaveName & TempChar
End Select
Next lngLoop
ActiveSheet.Cells.Copy ' copy the contents of the
ActiveSheet
Workbooks.Add ' create a new workbook
Selection.PasteSpecial Paste:=xlValues ' paste the
worksheet values into the new book
Selection.PasteSpecial Paste:=xlFormats ' and their
formats
ActiveWorkbook.SaveAs "C:\Temp" & SaveName ' temp file
attachment location
ActiveWorkbook.ChangeFileAccess xlReadOnly ' make
access read only
With EmailItem ' with the newly created e-mail
.Subject = ActiveWorkbook.Name
.Body = "This is an example of a single worksheet
sent by VBa mail"
.To = "1-Depot"
.Importance = olImportanceNormal 'Or
olImportanceHigh Or olImportanceLow
.Attachments.Add "C:\Temp" & SaveName
.Send ' send the worksheet
End With
Kill "C:\Temp" & SaveName ' delete the temporary
attachment
ActiveWorkbook.Close False ' close down the workbook
without saving (single sheet)

Application.ScreenUpdating = True ' always remember to
switch it back on!!

Set OL = Nothing ' clean down memory
Set EmailItem = Nothing

End Sub

.

Thankyou Ron.

-----Original Message-----
Hi Steved

Look for example code on my site
http://www.rondebruin.nl/sendmail.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Steved" wrote in
message ...
Hello from Steved

I got the below by going through Google.

The objective is to email a single Worksheet.

I'm getting a error

Invalid procedure call or argument.

Please help Thankyou.

Sub eMailActiveWorksheet()

Dim OL As Object ' Outlook instantiation
Dim EmailItem As Object ' A new mail item (e-mail)
Dim FileName As String ' The name of the file we are
attaching
Dim lngLoop As Long ' counter for the FileName length
Dim TempChar As String ' used for the loop to test
for
unusable characters
Dim SaveName As String ' Attachment's new name, after
cleaning

Application.ScreenUpdating = False ' speed up Excel
processing time
Set OL = CreateObject("Outlook.Application") ' New
Outlook application
Set EmailItem = OL.CreateItem(olMailItem) ' new
MailItem
FileName = ActiveSheet.Name & "1-City" &
ActiveWorkbook.Name ' create a filename on the fly
For lngLoop = 1 To Len(1 - City) ' error check for
unusable chars in the filename
TempChar = Mid(1 - City, y, 1)
Select Case TempChar
Case Is = "/", "\", "*", "?", """", "<", "", "/"
Case Else
SaveName = SaveName & TempChar
End Select
Next lngLoop
ActiveSheet.Cells.Copy ' copy the contents of the
ActiveSheet
Workbooks.Add ' create a new workbook
Selection.PasteSpecial Paste:=xlValues ' paste the
worksheet values into the new book
Selection.PasteSpecial Paste:=xlFormats ' and their
formats
ActiveWorkbook.SaveAs "C:\Temp" & SaveName ' temp
file
attachment location
ActiveWorkbook.ChangeFileAccess xlReadOnly ' make
access read only
With EmailItem ' with the newly created e-mail
.Subject = ActiveWorkbook.Name
.Body = "This is an example of a single worksheet
sent by VBa mail"
.To = "1-Depot"
.Importance = olImportanceNormal 'Or
olImportanceHigh Or olImportanceLow
.Attachments.Add "C:\Temp" & SaveName
.Send ' send the worksheet
End With
Kill "C:\Temp" & SaveName ' delete the temporary
attachment
ActiveWorkbook.Close False ' close down the workbook
without saving (single sheet)

Application.ScreenUpdating = True ' always remember
to
switch it back on!!

Set OL = Nothing ' clean down memory
Set EmailItem = Nothing

End Sub

.




.