S
Steved
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
..
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
..