Temp file not saving macros

[j.millar]

Hello!
I've created a form for my work colleagues to complete when they want to start a new project.
The form is completed by the project leader, then they hit a Command Button and it's sent to the Senior Manager. After the Senior Manager looks at it and approves it, he then sends it on to Admin (me!) so the project can get set up on all of our systems.
We recently upgraded to Office 2007 (yes, we are a bit late!) and now the Macro that sends the form to Admin no longer works. An example of the error is as follows:
Cannot run the Macro "FY06-10 PROJECTS 19-Jan-10 08-32'EmailtoAdmin.EmailtoAdmin'. The macro may not be available in this workbook or all Macros may be disabled.

Here is the code that I use to send it to the Senior Manager:
Sub MailtoSM()
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set Sourcewb = ActiveWorkbook
ActiveSheet.Copy
Set Destwb = ActiveWorkbook

With Destwb
If Val(Application.Version) < 12 Then

FileExtStr = ".xls": FileFormatNum = -4143
Else

If Sourcewb.Name = .Name Then
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
MsgBox "Your answer is No in the security dialog."
Exit Sub
Else
Select Case Sourcewb.FileFormat

Case 51: FileExtStr = ".xlsx": FileFormatNum = 51

Case 52:
If .HasVBProject Then
FileExtStr = ".xlsm": FileFormatNum = 52
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If

Case 56: FileExtStr = ".xls": FileFormatNum = 56

Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
End Select
End If
End If
End With
TempFilePath = Environ$("temp") & "\"
TempFileName = Sourcewb.Sheets("Proj. Advice").Range("AC6").Value & " " & Format(Now, "dd-mmm-yy HH-MM")
With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=52
On Error Resume Next
.SendMail Sheets("Proj. Advice").Range("R54").Value, "Project Commencement Advice"
On Error GoTo 0
.Close SaveChanges:=False
End With

' Delete the file you just sent.
Kill TempFilePath & TempFileName & FileExtStr
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
MsgBox "Your form has now been submitted to your Senior Manager."
End Sub


And this is the code to send it to Admin:
Sub EmailtoAdmin()
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set Sourcewb = ActiveWorkbook
ActiveSheet.Copy
Set Destwb = ActiveWorkbook

With Destwb
If Val(Application.Version) < 12 Then

FileExtStr = ".xls": FileFormatNum = -4143
Else

If Sourcewb.Name = .Name Then
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
MsgBox "Your answer is No in the security dialog."
Exit Sub
Else
Select Case Sourcewb.FileFormat

Case 51: FileExtStr = ".xlsx": FileFormatNum = 51

Case 52:
If .HasVBProject Then
FileExtStr = ".xlsm": FileFormatNum = 52
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If

Case 56: FileExtStr = ".xls": FileFormatNum = 56

Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
End Select
End If
End If
End With
TempFilePath = Environ$("temp") & "\"
With Destwb
ActiveWorkbook.SaveAs TempFilePath, FileFormat:=52
On Error Resume Next
.SendMail Sheets("Proj. Advice").Range("W54", "W55").Value, "Project Commencement Advice"
On Error GoTo 0
.Close SaveChanges:=False
End With

' Delete the file you just sent.
Kill TempFilePath & TempFileName & FileExtStr
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
MsgBox "Your form has now been submitted to Administration."
End Sub

Have I gone crazy and forgotton something completely obvious, or am I not as dumb as I thought

Thanks!

 

[woodycanuck]

Have you gone through the Trust Centre settings? Macro security all changed in 2007.

 

[j.millar]

Yes, the Macro Settings are set to Enable all Macros.

 

[woodycanuck]

Ok, since macros are being allowed, I'm guessing the link from the button to the code has been broken. Right-click the button and click "Assign Macro". Hopefully you see the sub you need to call in there and can reconnect it.

 

[dscg]

This line might be the problem as well:

With Destwb
        ActiveWorkbook.SaveAs TempFilePath, FileFormat:=52

Since Destwb is a workbook variable set to Activeworkbook, that's the equivalent of Activeworkbook.Activeworkbook.

Try without the activeworkbook perhaps. Or this looks like Ron DeBruin's code if you need to get more info or alternate methods.

http://www.rondebruin.nl/mail/folder1/mail2.htm

 

[j.millar]

Ok, since macros are being allowed, I'm guessing the link from the button to the code has been broken. Right-click the button and click "Assign Macro". Hopefully you see the sub you need to call in there and can reconnect it.

When I test the spreadsheet that is sent initially to the Senior Manager (It opens an outlook new message and I open it from there) there are no Macro's assigned to the spreadsheet to assign to the button.

I tried reassigning the Macro to the button and the same thing happens...

I also removed ActiveWorkbook and left the code to look like this:
With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=52

As far as I can see, it looks like the Modules aren't transferring across when sent to Senior Manager, could this be the problem?

 

[woodycanuck]

I don't think it's a code problem, otherwise you'd get a compile or a run-time error. It's not even looking at your code.

I'm not sure where you're doing all your testing, so I'll mention a few things:

Each user on each computer needs to allow macros in their own trust center, these settings don't travel with the spreadsheet.

Is this code in the modules section of the spreadsheet that's being sent around? Or is it in a .bas file somewhere?

It seems to me that if you can go to vba and the code is there, then any sub should be in the list when you do "Assign Macro".

I'll send you a PM...

 

[dscg]

Yes - that sounds like you got it. The file is being saved as an xlsx the first time. From Ron's site:

If you always want to save in a certain format you can replace this part of the macro

                Select Case Sourcewb.FileFormat
                Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
                Case 52:
                    If .HasVBProject Then
                        FileExtStr = ".xlsm": FileFormatNum = 52
                    Else
                        FileExtStr = ".xlsx": FileFormatNum = 51
                    End If
                Case 56: FileExtStr = ".xls": FileFormatNum = 56
                Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
                End Select

With one of the one liners from this list

FileExtStr = ".xlsb": FileFormatNum = 50
FileExtStr = ".xlsx": FileFormatNum = 51
FileExtStr = ".xlsm": FileFormatNum = 52
FileExtStr = ".xls": FileFormatNum = 56

In this case, use FileExtStr = ".xlsm": FileFormatNum = 52

 

[j.millar]

Just using my computer (as I said the Trust centre has enabled Macros) it's advising the Macros are disabled (The same error as from the first post) - This is after I press the Send to Senior Manager Button.

I've just changed the code to:
Sub MailtoSM()
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set Sourcewb = ActiveWorkbook
ActiveSheet.Copy
Set Destwb = ActiveWorkbook

With Destwb
If Val(Application.Version) < 12 Then

FileExtStr = ".xls": FileFormatNum = -4143
Else

If Sourcewb.Name = .Name Then
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
MsgBox "Your answer is No in the security dialog."
Exit Sub
Else
FileExtStr = ".xlsm": FileFormatNum = 52
End If
End If
End With
TempFilePath = Environ$("temp") & "\"
TempFileName = Sourcewb.Sheets("Proj. Advice").Range("AC6").Value & " " & Format(Now, "dd-mmm-yy HH-MM")
With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=52
On Error Resume Next
.SendMail Sheets("Proj. Advice").Range("R54").Value, "Project Commencement Advice"
On Error GoTo 0
.Close SaveChanges:=False
End With

' Delete the file you just sent.
Kill TempFilePath & TempFileName & FileExtStr
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
MsgBox "Your form has now been submitted to your Senior Manager."
End Sub

I'm still getting the error. It's definitely saving the temp file as a .xlsm but the Macro's dissapear into the magical world of Excel Land.

The code is in one of the Modules for the Workbook. (There are 5 Modules that I'm using for the form - NOTE: it's NOT a UserForm)

 

[dscg]

I think the only other issue is the macro attached to the button is in a closed file.

http://support.microsoft.com/kb/930076

Try to reassign the macro in the original workbook, not the emailed version. When you assign it, make sure the This Workbook option is chosen at the bottom.

You could aslo try to use Alt+F8 to try and run the macro manually...