Hi,
I have the code below that I use with a 'Ctrl+t' shortcut to automatically place a copy of my current worksheet (values only) into a new e-mail as an attachment. I went to use it this morning and it doesn't seem to like the following line when I try and use it having a filtered column.
ws.[A1].PasteSpecial Paste:=xlValues
I tried adding a few lines telling it to select only visible cells, but that ended up not being the issue, because when I use Debug, it keeps pointing me back to the line of code above.
Any help fixing this to work with filtered columns would be greatly appreciated.
Thanks,
Steve
Sub EmailWithOutlook()
'Variable declaration
Dim strMyName As String
Dim ws As Worksheet
Dim oApp As Object, _
oMail As Object, _
WB As Workbook, _
FileName As String
strMyName = ActiveSheet.Name
'Turn off screen updating
Application.ScreenUpdating = False
'Make a copy of the active sheet and save it to
'a temporary file
ActiveSheet.Copy
For Each ws In ActiveWorkbook.Worksheets
ws.Cells.Copy
ws.[A1].PasteSpecial Paste:=xlValues
ws.Cells.Hyperlinks.Delete
Application.CutCopyMode = False
Cells(1, 1).Select
ws.Activate
ws.Name = strMyName & " (" & Format(Date, "mmddyy") & ")"
Next ws
Set WB = ActiveWorkbook
FileName = strMyName & ".xlsx"
On Error Resume Next
Kill "C:\" & FileName
On Error GoTo 0
WB.SaveAs FileName:="C:\" & FileName
'Create and show the outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
'Uncomment the line below to hard code a recipient
'.To = "someone@somedomain.com"
'Uncomment the line below to hard code a subject
'.Subject = "Look at my workbook!"
.Attachments.Add WB.FullName
.Display
End With
'Delete the temporary file
WB.ChangeFileAccess Mode:=xlReadOnly
Kill WB.FullName
WB.Close SaveChanges:=False
'Restore screen updating and release Outlook
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing
End Sub
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar