Import and run Macro

[Steph]

Hi. The below code inserts VBA code via VBA code. The code it inserts is
code to Import a much larger procedure. So i was hoping to write the code
via code, and then execute the newly written code that imports the macro.
But when I call the new procedure, I get a "macro does not exist error".
Anyone see what is wrong with this code? Thanks!

Sub AddProcedure()

Dim VBCodeMod As CodeModule
Dim LineNum As Long

Set VBCodeMod =
ActiveWorkbook.VBProject.VBComponents("NewModule").CodeModule
With VBCodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum, _
"Sub CDOEmail()" & Chr(13) & _
"ThisWorkbook.VBProject.VBComponents.Import(""H:\CDO_Email_New.bas"")" &
Chr(13) & _
"End Sub"
End With

Application.Run "CDOEmail"

End Sub

 

[Guest]

hi,
did you check the vb editor to see what it did import if
anything?
i played with it but couldn't get it to work either.
Dim VBCodeMod As CodeModule. codemodule does not exist at
this point
ActiveWorkbook.VBProject.VBComponents
("NewModule").CodeModule
i never got past this. Scrip out of range error.

why are you doing things this way. why not just copy the
other procedure and paste it in the project?

 

[Steph]

Yeah, the code does get entered into the new module ok. So when I look at
the new module, I see
Sub CDOEmail()
ThisWorkbook.VBProject.VBComponents.Import(""H:\CDO_Email_New.bas"")_
End Sub
So it creates the code correctly. But I just can't get the code to execute
the newly created CDOEmail code.

 

[Bob Phillips]

Steph,

Try changing

Application.Run "CDOEmail"

to

Application.Run VBCodeMod.Name & ".CDOEmail"