Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can protect worksheet then workbook but not Protect and Share in code | Excel Programming | |||
Sending a worksheet with hyperlinks | Excel Discussion (Misc queries) | |||
Sending a worksheet | Excel Discussion (Misc queries) | |||
Sending information from one worksheet to another | Excel Worksheet Functions | |||
Sending emails from worksheet | Excel Discussion (Misc queries) |