Protect worksheet before sending
 

I have this macro (www.rondebruin.nl) works fine but i want to protect this
worksheet before i sent it to the emailaddress So the reciever can not change
my sheet. When i protect it the macro hungs and its not sending.
Wich code must i add?

gr:

Sub Mail_Loadingorder()
'You must add a reference to the Microsoft outlook Library
Dim OutApp As Object
Dim OutMail As Object
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With ActiveSheet.UsedRange
.Value = .Value
End With
With wb
.SaveAs "Loadingorder " & Sheets("Loadingorder").Range("G1").Value _
& ".xls"
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)



With OutMail
.To = ThisWorkbook.Sheets("Loadingorder").Range("e4").Va lue
.CC = ""
.BCC = ""
.Subject = "Loadingorder " &
Sheets("Loadingorder").Range("G1").Value
.Attachments.Add wb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\Loadingorder.txt")
.Send 'or use .Display
End With
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Protect worksheet before sending
 

you can do this

With wb
.sheets(1).protect "password"
.SaveAs "Loadingorder " & Sheets("Loadingorder").Range("G1").Value _
& ".xls"


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


"Pieter" wrote in message ...
I have this macro (www.rondebruin.nl) works fine but i want to protect this
worksheet before i sent it to the emailaddress So the reciever can not change
my sheet. When i protect it the macro hungs and its not sending.
Wich code must i add?

gr:

Sub Mail_Loadingorder()
'You must add a reference to the Microsoft outlook Library
Dim OutApp As Object
Dim OutMail As Object
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With ActiveSheet.UsedRange
.Value = .Value
End With
With wb
.SaveAs "Loadingorder " & Sheets("Loadingorder").Range("G1").Value _
& ".xls"
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)



With OutMail
.To = ThisWorkbook.Sheets("Loadingorder").Range("e4").Va lue
.CC = ""
.BCC = ""
.Subject = "Loadingorder " &
Sheets("Loadingorder").Range("G1").Value
.Attachments.Add wb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\Loadingorder.txt")
.Send 'or use .Display
End With
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Protect worksheet before sending
 

Pieter,
Could you pls tell me how to add a reference to the Microsoft outlook
Library& what reference.
Thxs


Pieter wrote:
I have this macro (www.rondebruin.nl) works fine but i want to protect this
worksheet before i sent it to the emailaddress So the reciever can not change
my sheet. When i protect it the macro hungs and its not sending.
Wich code must i add?

gr:

Sub Mail_Loadingorder()
'You must add a reference to the Microsoft outlook Library
Dim OutApp As Object
Dim OutMail As Object
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With ActiveSheet.UsedRange
.Value = .Value
End With
With wb
.SaveAs "Loadingorder " & Sheets("Loadingorder").Range("G1").Value _
& ".xls"
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)



With OutMail
.To = ThisWorkbook.Sheets("Loadingorder").Range("e4").Va lue
.CC = ""
.BCC = ""
.Subject = "Loadingorder " &
Sheets("Loadingorder").Range("G1").Value
.Attachments.Add wb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\Loadingorder.txt")
.Send 'or use .Display
End With
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Protect worksheet before sending
 

Hi al007

This is the example that Pieter use
http://www.rondebruin.nl/mail/folder2/mail2.htm

You can read how to set a reference on that page
Click on the Index link to see more examples

Note:If you use Outlook Express use this one
http://www.rondebruin.nl/mail/folder1/mail2.htm


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


"al007" wrote in message oups.com...
Pieter,
Could you pls tell me how to add a reference to the Microsoft outlook
Library& what reference.
Thxs


Pieter wrote:
I have this macro (www.rondebruin.nl) works fine but i want to protect this
worksheet before i sent it to the emailaddress So the reciever can not change
my sheet. When i protect it the macro hungs and its not sending.
Wich code must i add?

gr:

Sub Mail_Loadingorder()
'You must add a reference to the Microsoft outlook Library
Dim OutApp As Object
Dim OutMail As Object
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With ActiveSheet.UsedRange
.Value = .Value
End With
With wb
.SaveAs "Loadingorder " & Sheets("Loadingorder").Range("G1").Value _
& ".xls"
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)



With OutMail
.To = ThisWorkbook.Sheets("Loadingorder").Range("e4").Va lue
.CC = ""
.BCC = ""
.Subject = "Loadingorder " &
Sheets("Loadingorder").Range("G1").Value
.Attachments.Add wb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\Loadingorder.txt")
.Send 'or use .Display
End With
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Protect worksheet before sending
 

Look at www.rondebruin.nl

beside with this code i do not have to add the reference in outlook

"al007" wrote:

Pieter,
Could you pls tell me how to add a reference to the Microsoft outlook
Library& what reference.
Thxs


Pieter wrote:
I have this macro (www.rondebruin.nl) works fine but i want to protect this
worksheet before i sent it to the emailaddress So the reciever can not change
my sheet. When i protect it the macro hungs and its not sending.
Wich code must i add?

gr:

Sub Mail_Loadingorder()
'You must add a reference to the Microsoft outlook Library
Dim OutApp As Object
Dim OutMail As Object
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With ActiveSheet.UsedRange
.Value = .Value
End With
With wb
.SaveAs "Loadingorder " & Sheets("Loadingorder").Range("G1").Value _
& ".xls"
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)



With OutMail
.To = ThisWorkbook.Sheets("Loadingorder").Range("e4").Va lue
.CC = ""
.BCC = ""
.Subject = "Loadingorder " &
Sheets("Loadingorder").Range("G1").Value
.Attachments.Add wb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\Loadingorder.txt")
.Send 'or use .Display
End With
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Protect worksheet before sending
 

For the OP

beside with this code i do not have to add the reference in outlook

Pieter change to late binding (also on that page)

You don't have to set a reference then

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


"Pieter" wrote in message ...
Look at www.rondebruin.nl

beside with this code i do not have to add the reference in outlook

"al007" wrote:

Pieter,
Could you pls tell me how to add a reference to the Microsoft outlook
Library& what reference.
Thxs


Pieter wrote:
I have this macro (www.rondebruin.nl) works fine but i want to protect this
worksheet before i sent it to the emailaddress So the reciever can not change
my sheet. When i protect it the macro hungs and its not sending.
Wich code must i add?

gr:

Sub Mail_Loadingorder()
'You must add a reference to the Microsoft outlook Library
Dim OutApp As Object
Dim OutMail As Object
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With ActiveSheet.UsedRange
.Value = .Value
End With
With wb
.SaveAs "Loadingorder " & Sheets("Loadingorder").Range("G1").Value _
& ".xls"
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)



With OutMail
.To = ThisWorkbook.Sheets("Loadingorder").Range("e4").Va lue
.CC = ""
.BCC = ""
.Subject = "Loadingorder " &
Sheets("Loadingorder").Range("G1").Value
.Attachments.Add wb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\Loadingorder.txt")
.Send 'or use .Display
End With
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing
End Sub