Place code from current workbook to new workbook

[Qaspec]

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.

 

[Norman Jones]

Hi Qaspec,

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

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

 

[Qaspec]

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?

 

[Qaspec]

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?

 

[Norman Jones]

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.

 

[Qaspec]

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.