I have a macro that works sometimes when testing, but then gives users an error when activating it:
Run-time error ‘1004’:
Unable to set the Hidden property of the Range class
Debug error:
Rows("1:5").Hidden = True
Macro is:
Sub Emailstraightruckreq()
Dim oApp, oMail As Object, _
tWB, cWB As Workbook, _
fileName, FilePath As String
Application.ScreenUpdating = False
'Set email id here, it may be a range in case you have email id on your worksheet
Mailid = "[email protected]; [email protected]; [email protected]"
'Write your email message body here , add more lines using & vbLf _ at the end of each line
Body = "Straight truck requirements: " & vbLf _
& vbLf _
& "Truck/Driver comments: " & [B29].Value & " " & [B30].Value & vbLf _
& vbLf _
& "Thanks & Regards, " & vbLf _
& [B7].Value
'Copy Active Sheet and save it to a temporary file
Set cWB = ActiveWorkbook
ActiveSheet.Copy
'Hide Rows 1 through 5
Rows("1:5").Hidden = True
Rows("10:25").Hidden = True
Rows("31:45").Hidden = True
Set tWB = ActiveWorkbook
fileName = "Temp.xls" 'You can define the name
FilePath = Environ("TEMP")
On Error Resume Next
Kill FilePath & "\" & fileName
On Error GoTo 0
Application.DisplayAlerts = False
tWB.SaveAs fileName:=FilePath & "\" & fileName, FileFormat:=56
Application.DisplayAlerts = True
'Sending email through outlook
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
.To = Mailid
.Subject = [B6].Value & " Straight Truck request attached for " & [B8].Value
.Body = Body & [B4].Value
.Attachments.Add tWB.FullName
.Display
End With
'Delete the temporary file and restore screen updating
tWB.ChangeFileAccess Mode:=xlReadOnly
Kill tWB.FullName
tWB.Close SaveChanges:=False
cWB.Activate
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing
End Sub
Bookmarks