jentomacelli
New Member
- Joined
- Jul 20, 2010
- Messages
- 1
Hello,
I have been googling VB code to help me accomplish the following task. Please understand, my knowledge is EXTREMELY limited, so its ok to talk down to me.
Here is what I need to do.
1.) I have created an excel sheet for a company PTO form.
2.) I have created a button for the employee to press that opens an Outlook email, fills in the To, CC, Subject and attaches the active worksheet. This email will go to the manager for approval.
3.) I have created a button for the manager, on the same sheet, that they will press that generates an email to go to payroll and also attach the newest active sheet.
On my computer, I can click either button and the Outlook email box will open with the correct recipients and subject (based on if I clicked Employee or Manager)
However, when I click the Employee button and send it to the manager, the manager can open the file, review it, but CANNOT click the manager button. Basically, the error message states that the Macro is not available in the workbook.
My thought is that because the macro is saved on my computer, it doesn't follow the excel sheet from recipient to recipient.
The initial version will have the macros, but once its emailed, the version changes and loses the next macro? maybe?
Here is my code.
Sub EmailWithOutlook()
'Variable declaration
Dim oApp As Object, _
oMail As Object, _
WB As Workbook, _
FileName As String
'Turn off screen updating
Application.ScreenUpdating = False
'Make a copy of the active sheet and save it to
'a temporary file
ActiveSheet.Copy
Set WB = ActiveWorkbook
FileName = "PTO Request1.xlsx"
On Error Resume Next
Kill "C:\" & FileName
On Error GoTo 0
WB.SaveAs FileName:="C:\" & FileName
'Create and show the outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
'Uncomment the line below to hard code a recipient
.To = Range("e598").Text
.CC = Range("e595").Text
'Uncomment the line below to hard code a subject
.Subject = "PTO Submission - Approval Required"
.Attachments.Add WB.FullName
.Display
End With
'Delete the temporary file
WB.ChangeFileAccess Mode:=xlReadOnly
Kill WB.FullName
WB.Close SaveChanges:=False
'Restore screen updating and release Outlook
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing
End Sub
I don't know what a lot of this means, but it works EXCEPT when anyone else uses it
Both the Employee and Manager macro are saved in the workbook.
Any ideas on how I can accomplish my goals of sending a PTO request to the manager VIA a button on the form and then the manager approving the form and clicking a button to send to payroll??
Thanks!!
Jen
I have been googling VB code to help me accomplish the following task. Please understand, my knowledge is EXTREMELY limited, so its ok to talk down to me.
Here is what I need to do.
1.) I have created an excel sheet for a company PTO form.
2.) I have created a button for the employee to press that opens an Outlook email, fills in the To, CC, Subject and attaches the active worksheet. This email will go to the manager for approval.
3.) I have created a button for the manager, on the same sheet, that they will press that generates an email to go to payroll and also attach the newest active sheet.
On my computer, I can click either button and the Outlook email box will open with the correct recipients and subject (based on if I clicked Employee or Manager)
However, when I click the Employee button and send it to the manager, the manager can open the file, review it, but CANNOT click the manager button. Basically, the error message states that the Macro is not available in the workbook.
My thought is that because the macro is saved on my computer, it doesn't follow the excel sheet from recipient to recipient.
The initial version will have the macros, but once its emailed, the version changes and loses the next macro? maybe?
Here is my code.
Sub EmailWithOutlook()
'Variable declaration
Dim oApp As Object, _
oMail As Object, _
WB As Workbook, _
FileName As String
'Turn off screen updating
Application.ScreenUpdating = False
'Make a copy of the active sheet and save it to
'a temporary file
ActiveSheet.Copy
Set WB = ActiveWorkbook
FileName = "PTO Request1.xlsx"
On Error Resume Next
Kill "C:\" & FileName
On Error GoTo 0
WB.SaveAs FileName:="C:\" & FileName
'Create and show the outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
'Uncomment the line below to hard code a recipient
.To = Range("e598").Text
.CC = Range("e595").Text
'Uncomment the line below to hard code a subject
.Subject = "PTO Submission - Approval Required"
.Attachments.Add WB.FullName
.Display
End With
'Delete the temporary file
WB.ChangeFileAccess Mode:=xlReadOnly
Kill WB.FullName
WB.Close SaveChanges:=False
'Restore screen updating and release Outlook
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing
End Sub
I don't know what a lot of this means, but it works EXCEPT when anyone else uses it
Both the Employee and Manager macro are saved in the workbook.
Any ideas on how I can accomplish my goals of sending a PTO request to the manager VIA a button on the form and then the manager approving the form and clicking a button to send to payroll??
Thanks!!
Jen