usnapoleon
Board Regular
- Joined
- May 22, 2014
- Messages
- 79
- Office Version
- 365
- Platform
- Windows
Hello
My excel has a button that does a few things:
It creates a file on my c drive that it uses in preparation of sending the file via email through Outlook
It hides specific tabs of the excel
It creates a Subject line in the email heading.
My work recently upgraded me to a laptop with office 2016 and I think the upgrade broke something in the VB that runs the button. I cannot remember what version of Windows the file was originally created under but I was previously using either 2007 or 2013. Now, the file is prepped like normal, it does everything except populate the email's Subject line with the appropriate title. This is necessary because this is a daily email we send out and the Subject line changes each day.
I was hoping someone can look at the rules and see if they spot something. Thank you in advance.
My excel has a button that does a few things:
It creates a file on my c drive that it uses in preparation of sending the file via email through Outlook
It hides specific tabs of the excel
It creates a Subject line in the email heading.
My work recently upgraded me to a laptop with office 2016 and I think the upgrade broke something in the VB that runs the button. I cannot remember what version of Windows the file was originally created under but I was previously using either 2007 or 2013. Now, the file is prepped like normal, it does everything except populate the email's Subject line with the appropriate title. This is necessary because this is a daily email we send out and the Subject line changes each day.
I was hoping someone can look at the rules and see if they spot something. Thank you in advance.
Code:
Private Sub CommandButton1_Click()
'/must have read/write access to a local directory on "c:\exwork"
'/variable declaration
Dim filespec As String '/working directory
Dim filex As String '/current workbook name
Dim dtemp As Date '/date pulled from spreadsheet
Dim mdisplay As String '/email subject line
Dim fnamemod As String '/date for file name
Dim fname As String '/temporary file name
'/create filename and save as new file on c:\exwork"
filex = ThisWorkbook.Name
dtemp = ActiveWorkbook.Worksheets("input").Range("A4")
mdisplay = Format(dtemp, "mm/dd/yy")
mdisplay = "WRSD Daily statistics for: " & mdisplay
fnamemod = Format(dtemp, "mm-dd-yy")
filespec = "C:\exwork\"
fname = filespec & "WRSD daily " & fnamemod
Application.DisplayAlerts = False
ActiveWorkbook.SaveCopyAs Filename:=fname
Application.DisplayAlerts = False
'/open the newly created copy
'/savecopyas method leave the calling "this.workbook" open
'/and macros active and leaves the new copy closed
'/open and manipulate the new file to hide the input and budget sheets
Workbooks.Open Filename:=fname
ActiveWorkbook.Worksheets("comparative").Select
ActiveSheet.Range("a1").Select
ActiveWorkbook.Worksheets("input").Visible = False
ActiveWorkbook.Worksheets("budget").Visible = False
ActiveWorkbook.Worksheets("last year").Visible = False
ActiveWorkbook.Worksheets("ytd").Visible = False
ActiveWorkbook.Worksheets("p.ytd").Visible = False
ActiveWorkbook.Worksheets("taxes").Visible = False
ActiveWorkbook.SendMail "AuditDistribution@welkgroup.com", mdisplay
ActiveWorkbook.Close
End Sub