Place code from current workbook to new workbook

I am using the following code to create a new workbook and send via outlook
to whatever adress the user enters.

Private Sub Send1_Click()
Dim strdate As String
Sheets(Array("Emp24", "Main")).Copy
strdate = Format(Date, "dd-mm-yy") & " " & Format(Time, "h-mm-ss")
Worksheets("Emp24").Name = "NewData"
ActiveWorkbook.SaveAs "NewEmployeeData.xls"
ActiveWorkbook.SendMail "", _
"Employee Attendance Data"
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False
End Sub

In the original workbook inside the This Workbook section I have the
following:

Private Sub Workbook_Open

Dim lngColor As Long
lngColor = RGB(221, 221, 221)
ActiveWorkbook.Colors(16) = lngColor

End Sub

How can I place this 2nd code into the This Workbook section of the file
being sent i the first sub?

Thank You for any help.

Hi Qaspec,

See Chip Pearson's 'Programming To The Visual Basic Editor' page at:

http://www.cpearson.com/excel/vbe.htm

---
Regards,
Norman



"Qaspec" <[email protected]> wrote in message
news:[email protected]...
>I am using the following code to create a new workbook and send via outlook
> to whatever adress the user enters.
>
> Private Sub Send1_Click()
> Dim strdate As String
> Sheets(Array("Emp24", "Main")).Copy
> strdate = Format(Date, "dd-mm-yy") & " " & Format(Time, "h-mm-ss")
> Worksheets("Emp24").Name = "NewData"
> ActiveWorkbook.SaveAs "NewEmployeeData.xls"
> ActiveWorkbook.SendMail "", _
> "Employee Attendance Data"
> ActiveWorkbook.ChangeFileAccess xlReadOnly
> Kill ActiveWorkbook.FullName
> ActiveWorkbook.Close False
> End Sub
>
> In the original workbook inside the This Workbook section I have the
> following:
>
> Private Sub Workbook_Open
>
> Dim lngColor As Long
> lngColor = RGB(221, 221, 221)
> ActiveWorkbook.Colors(16) = lngColor
>
> End Sub
>
> How can I place this 2nd code into the This Workbook section of the file
> being sent i the first sub?
>
> Thank You for any help.
>

I've tried to use the suggestions made in the url. I can't even get to tell
if I can get it to work bacause a) it is specific to modules and b) they set
off the virus scan at my company. They are not going to change the scan so is
there another way to export or copy a sub from the This Workbook section in
one file to the This Workbook section in another file?

"Norman Jones" wrote:

> Hi Qaspec,
>
> See Chip Pearson's 'Programming To The Visual Basic Editor' page at:
>
> http://www.cpearson.com/excel/vbe.htm
>
> ---
> Regards,
> Norman
>
>
>
> "Qaspec" <[email protected]> wrote in message
> news:[email protected]...
> >I am using the following code to create a new workbook and send via outlook
> > to whatever adress the user enters.
> >
> > Private Sub Send1_Click()
> > Dim strdate As String
> > Sheets(Array("Emp24", "Main")).Copy
> > strdate = Format(Date, "dd-mm-yy") & " " & Format(Time, "h-mm-ss")
> > Worksheets("Emp24").Name = "NewData"
> > ActiveWorkbook.SaveAs "NewEmployeeData.xls"
> > ActiveWorkbook.SendMail "", _
> > "Employee Attendance Data"
> > ActiveWorkbook.ChangeFileAccess xlReadOnly
> > Kill ActiveWorkbook.FullName
> > ActiveWorkbook.Close False
> > End Sub
> >
> > In the original workbook inside the This Workbook section I have the
> > following:
> >
> > Private Sub Workbook_Open
> >
> > Dim lngColor As Long
> > lngColor = RGB(221, 221, 221)
> > ActiveWorkbook.Colors(16) = lngColor
> >
> > End Sub
> >
> > How can I place this 2nd code into the This Workbook section of the file
> > being sent i the first sub?
> >
> > Thank You for any help.
> >
>
>
>

Is it possible to use the code in my send macro to copy the entire workbook
and then delete the sheets that I don't need before sending via outlook?

"Qaspec" wrote:

> I've tried to use the suggestions made in the url. I can't even get to tell
> if I can get it to work bacause a) it is specific to modules and b) they set
> off the virus scan at my company. They are not going to change the scan so is
> there another way to export or copy a sub from the This Workbook section in
> one file to the This Workbook section in another file?
>
> "Norman Jones" wrote:
>
> > Hi Qaspec,
> >
> > See Chip Pearson's 'Programming To The Visual Basic Editor' page at:
> >
> > http://www.cpearson.com/excel/vbe.htm
> >
> > ---
> > Regards,
> > Norman
> >
> >
> >
> > "Qaspec" <[email protected]> wrote in message
> > news:[email protected]...
> > >I am using the following code to create a new workbook and send via outlook
> > > to whatever adress the user enters.
> > >
> > > Private Sub Send1_Click()
> > > Dim strdate As String
> > > Sheets(Array("Emp24", "Main")).Copy
> > > strdate = Format(Date, "dd-mm-yy") & " " & Format(Time, "h-mm-ss")
> > > Worksheets("Emp24").Name = "NewData"
> > > ActiveWorkbook.SaveAs "NewEmployeeData.xls"
> > > ActiveWorkbook.SendMail "", _
> > > "Employee Attendance Data"
> > > ActiveWorkbook.ChangeFileAccess xlReadOnly
> > > Kill ActiveWorkbook.FullName
> > > ActiveWorkbook.Close False
> > > End Sub
> > >
> > > In the original workbook inside the This Workbook section I have the
> > > following:
> > >
> > > Private Sub Workbook_Open
> > >
> > > Dim lngColor As Long
> > > lngColor = RGB(221, 221, 221)
> > > ActiveWorkbook.Colors(16) = lngColor
> > >
> > > End Sub
> > >
> > > How can I place this 2nd code into the This Workbook section of the file
> > > being sent i the first sub?
> > >
> > > Thank You for any help.
> > >
> >
> >
> >

Hi Qaspec,

Your company blocks VB(A) macros because they might contain viruses. If it
were possible readily to circumvent this, virus writers would be able to
gain unfettered access to the company's computer systems.

> so is there another way to export or copy a sub from the
> This Workbook section in one file to the This Workbook
> section in another file?

AFAIK, only with the assistance of the recipient. Perhaps, for example, you
could send the macro as text together with instructions for copying this
into the requisite module.

---
Regards,
Norman



"Qaspec" <[email protected]> wrote in message
news:[email protected]...
> I've tried to use the suggestions made in the url. I can't even get to
> tell
> if I can get it to work bacause a) it is specific to modules and b) they
> set
> off the virus scan at my company. They are not going to change the scan so
> is
> there another way to export or copy a sub from the This Workbook section
> in
> one file to the This Workbook section in another file?
>
> "Norman Jones" wrote:
>
>> Hi Qaspec,
>>
>> See Chip Pearson's 'Programming To The Visual Basic Editor' page at:
>>
>> http://www.cpearson.com/excel/vbe.htm
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "Qaspec" <[email protected]> wrote in message
>> news:[email protected]...
>> >I am using the following code to create a new workbook and send via
>> >outlook
>> > to whatever adress the user enters.
>> >
>> > Private Sub Send1_Click()
>> > Dim strdate As String
>> > Sheets(Array("Emp24", "Main")).Copy
>> > strdate = Format(Date, "dd-mm-yy") & " " & Format(Time, "h-mm-ss")
>> > Worksheets("Emp24").Name = "NewData"
>> > ActiveWorkbook.SaveAs "NewEmployeeData.xls"
>> > ActiveWorkbook.SendMail "", _
>> > "Employee Attendance Data"
>> > ActiveWorkbook.ChangeFileAccess xlReadOnly
>> > Kill ActiveWorkbook.FullName
>> > ActiveWorkbook.Close False
>> > End Sub
>> >
>> > In the original workbook inside the This Workbook section I have the
>> > following:
>> >
>> > Private Sub Workbook_Open
>> >
>> > Dim lngColor As Long
>> > lngColor = RGB(221, 221, 221)
>> > ActiveWorkbook.Colors(16) = lngColor
>> >
>> > End Sub
>> >
>> > How can I place this 2nd code into the This Workbook section of the
>> > file
>> > being sent i the first sub?
>> >
>> > Thank You for any help.
>> >
>>
>>
>>

I apologize for looking in the wrong section of the page you referred me to.
Instead of exporting modules I should have looked at creating an event. Using
that and tweaking it I was able to modify my macro. Thanks for pointing me in
the correct direction. So just in case anyone else has this similiar
situation. Here is my finished working Sub.

Private Sub Send1_Click()
Dim strdate As String
ActiveSheet.Copy
strdate = Format(Date, "mm-dd-yy")
ActiveSheet.Name = "Snapshot"
Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
StartLine = .CreateEventProc("Open", "Workbook") + 1
.InsertLines StartLine, _
"ActiveWorkbook.Colors(16) = lngColor"
.InsertLines StartLine, _
"lngColor = RGB(221, 221, 221)"
.InsertLines StartLine, _
"Dim lngColor As Long"
End With
ActiveWorkbook.SaveAs "QA Snapshot" & strdate & ".xls"
Application.VBE.MainWindow.Visible = False
ActiveWorkbook.SendMail "", _
"QA Snapshot" & " " & Range("A8")
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False
End Sub

Basically this allows me to copy the worksheet, save it as a new workbook
that I can send to anyone I want and allows me to keep a custom color I
created for my charts that the recipient will be able to see correctly when
they open the new workbook.


"Norman Jones" wrote:

> Hi Qaspec,
>
> Your company blocks VB(A) macros because they might contain viruses. If it
> were possible readily to circumvent this, virus writers would be able to
> gain unfettered access to the company's computer systems.
>
> > so is there another way to export or copy a sub from the
> > This Workbook section in one file to the This Workbook
> > section in another file?
>
> AFAIK, only with the assistance of the recipient. Perhaps, for example, you
> could send the macro as text together with instructions for copying this
> into the requisite module.
>
> ---
> Regards,
> Norman
>
>
>
> "Qaspec" <[email protected]> wrote in message
> news:[email protected]...
> > I've tried to use the suggestions made in the url. I can't even get to
> > tell
> > if I can get it to work bacause a) it is specific to modules and b) they
> > set
> > off the virus scan at my company. They are not going to change the scan so
> > is
> > there another way to export or copy a sub from the This Workbook section
> > in
> > one file to the This Workbook section in another file?
> >
> > "Norman Jones" wrote:
> >
> >> Hi Qaspec,
> >>
> >> See Chip Pearson's 'Programming To The Visual Basic Editor' page at:
> >>
> >> http://www.cpearson.com/excel/vbe.htm
> >>
> >> ---
> >> Regards,
> >> Norman
> >>
> >>
> >>
> >> "Qaspec" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >I am using the following code to create a new workbook and send via
> >> >outlook
> >> > to whatever adress the user enters.
> >> >
> >> > Private Sub Send1_Click()
> >> > Dim strdate As String
> >> > Sheets(Array("Emp24", "Main")).Copy
> >> > strdate = Format(Date, "dd-mm-yy") & " " & Format(Time, "h-mm-ss")
> >> > Worksheets("Emp24").Name = "NewData"
> >> > ActiveWorkbook.SaveAs "NewEmployeeData.xls"
> >> > ActiveWorkbook.SendMail "", _
> >> > "Employee Attendance Data"
> >> > ActiveWorkbook.ChangeFileAccess xlReadOnly
> >> > Kill ActiveWorkbook.FullName
> >> > ActiveWorkbook.Close False
> >> > End Sub
> >> >
> >> > In the original workbook inside the This Workbook section I have the
> >> > following:
> >> >
> >> > Private Sub Workbook_Open
> >> >
> >> > Dim lngColor As Long
> >> > lngColor = RGB(221, 221, 221)
> >> > ActiveWorkbook.Colors(16) = lngColor
> >> >
> >> > End Sub
> >> >
> >> > How can I place this 2nd code into the This Workbook section of the
> >> > file
> >> > being sent i the first sub?
> >> >
> >> > Thank You for any help.
> >> >
> >>
> >>
> >>
>
>
>