I have a macro that saves my original file as another file.
Sub Automatic()
'
' Aut
pen
'
'
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\bgrubb\My Documents\Commercial\Work With Assessment Form -CSM CAM2.xlsm" _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
ActiveSheet.Shapes("Rectangle 2").Select
ActiveSheet.Shapes("Rectangle 2").Delete
End Sub
After changes have been made to the saved file, I save a copy of 1 worksheet and email it. Then I want to delete the saved file. The following macro finishes running but when I go back and look, the file is still there.
Sub Send()
'
' Send Macro
'
'
Range("H2:J2").Select
Selection.Copy
Range("H2:J2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
emailvalue = Worksheets("email").Range("G2").Value
emailvalue2 = Worksheets("email").Range("G3").Value
Dim myOutlook As Object
Dim myMailItem As Object
Dim objSheet As Excel.Worksheet
Dim sheetname As String
Set otlApp = CreateObject("Outlook.Application")
Set otlNewMail = otlApp.CreateItem(olMailItem)
ActiveSheet.Name = ActiveSheet.Range("C2")
sheetname = ActiveWorkbook.ActiveSheet.Name
Set objSheet = ActiveWorkbook.Sheets(sheetname)
FName = "C:\Documents and Settings\BGrubb\My Documents\Commercial" & "\" & "CAM Assessment" & " " & ActiveWorkbook.ActiveSheet.Name
objSheet.Copy
ActiveWorkbook.SaveAs FName
With otlNewMail
.to = emailvalue
.cc = emailvalue2
.Subject = "CAM Assessment"
.Attachments.Add ActiveWorkbook.FullName
.HTMLBody = "<HTML><body><p><font face=""Comic Sans MS"" size=""2"">Attached is a completed CAM Assessment form.</font></b></u></font><br></body></HTML>"
.Send
End With
ActiveWindow.Close
Set otlNewMail = Nothing
Set otlApp = Nothing
Set otlAttach = Nothing
Set otlMess = Nothing
Set otlNSpace = Nothing
ActiveWindow.Activate
ActiveWindow.Close
Kill "C:\Documents and Settings\bgrubb\My Documents\Commercial\Work With Assessment Form -CSM CAM2.xlsm"
End Sub
I've run out of ideas. Any help would be appreciated.
Sub Automatic()
'
' Aut
pen'
'
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\bgrubb\My Documents\Commercial\Work With Assessment Form -CSM CAM2.xlsm" _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
ActiveSheet.Shapes("Rectangle 2").Select
ActiveSheet.Shapes("Rectangle 2").Delete
End Sub
After changes have been made to the saved file, I save a copy of 1 worksheet and email it. Then I want to delete the saved file. The following macro finishes running but when I go back and look, the file is still there.
Sub Send()
'
' Send Macro
'
'
Range("H2:J2").Select
Selection.Copy
Range("H2:J2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
emailvalue = Worksheets("email").Range("G2").Value
emailvalue2 = Worksheets("email").Range("G3").Value
Dim myOutlook As Object
Dim myMailItem As Object
Dim objSheet As Excel.Worksheet
Dim sheetname As String
Set otlApp = CreateObject("Outlook.Application")
Set otlNewMail = otlApp.CreateItem(olMailItem)
ActiveSheet.Name = ActiveSheet.Range("C2")
sheetname = ActiveWorkbook.ActiveSheet.Name
Set objSheet = ActiveWorkbook.Sheets(sheetname)
FName = "C:\Documents and Settings\BGrubb\My Documents\Commercial" & "\" & "CAM Assessment" & " " & ActiveWorkbook.ActiveSheet.Name
objSheet.Copy
ActiveWorkbook.SaveAs FName
With otlNewMail
.to = emailvalue
.cc = emailvalue2
.Subject = "CAM Assessment"
.Attachments.Add ActiveWorkbook.FullName
.HTMLBody = "<HTML><body><p><font face=""Comic Sans MS"" size=""2"">Attached is a completed CAM Assessment form.</font></b></u></font><br></body></HTML>"
.Send
End With
ActiveWindow.Close
Set otlNewMail = Nothing
Set otlApp = Nothing
Set otlAttach = Nothing
Set otlMess = Nothing
Set otlNSpace = Nothing
ActiveWindow.Activate
ActiveWindow.Close
Kill "C:\Documents and Settings\bgrubb\My Documents\Commercial\Work With Assessment Form -CSM CAM2.xlsm"
End Sub
I've run out of ideas. Any help would be appreciated.
