need help to update macro to office 2007 macro enabled workbook

[jatman]

good morning,

i compiled this macro for office 2003 with help from users here, and now i'm
back again because we have upgraded to office 2007. i have also changed the
file to office 2007 - macro enabled.

the following macro, sends a copy of the excel sheet, then saves a copy in
pdf format (using the MS add-in) and goes through a few other things as you
will see. i need two things in this macro, and i have those items [IN THIS
FORMAT - ALL CAPS]


Sub POInv()
' Macro recorded 8/28/2006 by Jat
'
If ActiveSheet.Name = "Purchase Order (Inventory)" Then
'Sub SaveName() - multiple steps
ActiveSheet.Copy 'creates a new one page workbook with a copy of
the activesheet in it, this becomes the activesheet/book
ActiveSheet.Name = Range("L5").Value 'renames the active sheet
(from ActiveSheet.Copy) to the purchase order value located in cell M5 [I
WOULD LIKE THE SHEET NAME TO INCLUDE THE SUPPLIER NAME IN CELL D11. I have
tried adding the cell d11 into the activesheet.name range but i cannot figure
it out.]
Range("L7") = Now
strdate = Format(Now, "mm-dd-yy h-mm-ss")
ActiveSheet.Protect
'End Sub

'Sub Email() - sends a copy of the email to the recipients in an excel
format(should be accounts payable department, or similar)
ActiveWorkbook.SendMail Recipients:="(e-mail address removed)",
Subject:=ActiveSheet.Name

[IN THE EMAIL, THE EXCEL ATTACHMENT'S NAME IS BOOK1.XLSX. I WOULD LIKE THE
ATTACHMENT'S NAME TO BE THE SAME AS THE ACTIVESHEET.NAME. I tried
Attachment:=ActiveSheet.Name but not that easy.]

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\Jat\Desktop\Operations\Purchase Orders\Purchase Orders
Issued\" & ActiveSheet.Name & ".pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True,
IgnorePrintAreas _
:=False, OpenAfterPublish:=False

ActiveWorkbook.Close SaveChanges:=False 'don't ask - kind of looks
good.
'End Sub Email()

'Sub Count() 'increases the PO number (stored in cell K8 and
displayed in M5)
mycount = Range("K8") + 1
Range("K8") = mycount
'End Sub

'Sub ClearContents() - Clears the contents in selectable cells, and
reverts the actual PO to it's original form

Range("L9,L11,L13,L15,D11:G15,A18:K38,E39,G39,J39,C41,E41,H41,B43:K46,L50,A51:G51,F5:H8").Select
Selection.ClearContents
Range("B18:I18").Select
Range("B18:I18").Select
Selection.Copy

Range("B38:I38").Select
ActiveSheet.Paste
Application.ScreenUpdating = True
Range("D11:G11").Select
'End Sub

'Sub AutoSave() - saves the updated purchase order
ActiveWorkbook.Save
'End Sub

End If

End Sub


well, that's it. just two simple things to keep the macro simple. any help
would be appreciated.

thank you,

jat jaswal

 

[Joel]

To change the worksheet name try this
from
ActiveSheet.Name = Range("L5").Value
to
ActiveSheet.Name = Range("L5").Value & Range("D11").Value


Because you created a new workbook and haven't saved the wrokbook the name
defaults to BOOK1.XLSX. You need to "save as" the file to change the name
before you e-mail the file. the "save as" need to ber before the line which
writes the subject line in the e-mail.

ActiveWorkbook.SendMail Recipients:="(e-mail address removed)",
Subject:=ActiveSheet.Name

good morning,

i compiled this macro for office 2003 with help from users here, and now i'm
back again because we have upgraded to office 2007. i have also changed the
file to office 2007 - macro enabled.

the following macro, sends a copy of the excel sheet, then saves a copy in
pdf format (using the MS add-in) and goes through a few other things as you
will see. i need two things in this macro, and i have those items [IN THIS
FORMAT - ALL CAPS]


Sub POInv()
' Macro recorded 8/28/2006 by Jat
'
If ActiveSheet.Name = "Purchase Order (Inventory)" Then
'Sub SaveName() - multiple steps
ActiveSheet.Copy 'creates a new one page workbook with a copy of
the activesheet in it, this becomes the activesheet/book
ActiveSheet.Name = Range("L5").Value 'renames the active sheet
(from ActiveSheet.Copy) to the purchase order value located in cell M5 [I
WOULD LIKE THE SHEET NAME TO INCLUDE THE SUPPLIER NAME IN CELL D11. I have
tried adding the cell d11 into the activesheet.name range but i cannot figure
it out.]
Range("L7") = Now
strdate = Format(Now, "mm-dd-yy h-mm-ss")
ActiveSheet.Protect
'End Sub

'Sub Email() - sends a copy of the email to the recipients in an excel
format(should be accounts payable department, or similar)
ActiveWorkbook.SendMail Recipients:="(e-mail address removed)",
Subject:=ActiveSheet.Name

[IN THE EMAIL, THE EXCEL ATTACHMENT'S NAME IS BOOK1.XLSX. I WOULD LIKE THE
ATTACHMENT'S NAME TO BE THE SAME AS THE ACTIVESHEET.NAME. I tried
Attachment:=ActiveSheet.Name but not that easy.]

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\Jat\Desktop\Operations\Purchase Orders\Purchase Orders
Issued\" & ActiveSheet.Name & ".pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True,
IgnorePrintAreas _
:=False, OpenAfterPublish:=False

ActiveWorkbook.Close SaveChanges:=False 'don't ask - kind of looks
good.
'End Sub Email()

'Sub Count() 'increases the PO number (stored in cell K8 and
displayed in M5)
mycount = Range("K8") + 1
Range("K8") = mycount
'End Sub

'Sub ClearContents() - Clears the contents in selectable cells, and
reverts the actual PO to it's original form

Range("L9,L11,L13,L15,D11:G15,A18:K38,E39,G39,J39,C41,E41,H41,B43:K46,L50,A51:G51,F5:H8").Select
Selection.ClearContents
Range("B18:I18").Select
Range("B18:I18").Select
Selection.Copy

Range("B38:I38").Select
ActiveSheet.Paste
Application.ScreenUpdating = True
Range("D11:G11").Select
'End Sub

'Sub AutoSave() - saves the updated purchase order
ActiveWorkbook.Save
'End Sub

End If

End Sub


well, that's it. just two simple things to keep the macro simple. any help
would be appreciated.

thank you,

jat jaswal