Solved

VBA: Save excel file and email

Posted on 2014-10-16
5
270 Views
Last Modified: 2014-10-16
Experts,

I have a dilemma, where I have vba script in my workbook that hides multiple sheets and emails the workbook.  My issue is that it is modifying and saving the active file rather than creating a new instance of the workbook and modifying/saving the file.

 I would like to be able to run the script and keep the original file untouched during the automation.

Thanks

  Sub Weekly_Mail_ActiveSheet()
  
  Application.ScreenUpdating = False
 
    Dim OutApp As Object
    Dim OutMail As Object
    Dim wb1 As Workbook
    Dim Uid
    Dim x
    Dim yourdate As String
    Dim strLink As String
    Dim UserName As String
    Dim UserTitle As String
    Dim UserEmail As String

 
    yourdate = Format(Date, "yyyy mm dd") 'Format Today() as specified
    strLink = "jon.doe@mymail.com"


    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    Set x = CreateObject("WSCRIPT.Network")
    Set wb1 = ActiveWorkbook
    Uid = x.UserName
    
 'Identifies sender for email signature change
 Select Case LCase(Uid)

   Case LCase("lspaz")
        UserName = "Jon Doe"
        UserTitle = "Head Peanut"
        UserEmail = strLink
    
 Case Else

            MsgBox "ERROR: You are not authorized to send email", vbOKOnly

         End Select

 'Execute

    On Error Resume Next

   
   ' Hide Templates from sheets
   
    Sheets("TestSheet").Select
    Columns("AB:AE").Select
    Range("AB4").Activate
    Selection.EntireColumn.Hidden = True
    Range("B2:C3").Select
    Sheets("TestSheet2").Select
    Columns("AB:AD").Select
    Range("AB48").Activate
    Selection.EntireColumn.Hidden = True
    Range("B2:C3").Select
    Sheets("TestSheet3").Select
    Columns("AB:AD").Select
    Range("AB72").Activate
    Selection.EntireColumn.Hidden = True
    Range("B2:C3").Select
    Sheets("TestSheet4").Select
    Columns("AB:AD").Select
    Range("AB28").Activate
    Selection.EntireColumn.Hidden = True
    Range("B2:C3").Select
    Sheets("TestSheet5").Select
    Columns("AB:AG").Select
    Range("AE49").Activate
    Selection.EntireColumn.Hidden = True
    Range("B2:C3").Select
    Sheets("Dashboard").Select
    
   'Format Workbook for Email
   
   Call HideSheets
   
   'Save formatted workbook
    ActiveWorkbook.SaveAs Filename:= _
        "S:\TB-NOC\WorbookTest - " & yourdate & ".xlsm", FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
   
   
    With OutMail
   
            .To = "jon.doe@mymail.com"
            .Subject = "Test Workbook - " & yourdate & " Weekly"
            .Body = "Team," & vbCrLf & _
            "" & vbCrLf & _
            "Please see attached" & vbCrLf & _
            "" & vbCrLf & _
            "Thank you," & vbCrLf & _
            UserName & vbCrLf & _
            UserTitle & vbCrLf & _
            "Crazy folks" & vbCrLf & _
            UserEmail & vbCrLf
            .Attachments.Add "S:\TB-NOC\WorbookTest - " & yourdate & ".xlsm"
            .Send
        End With
            
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing

    
             
End Sub

Open in new window

0
Comment
Question by:Maliki Hassani
  • 3
  • 2
5 Comments
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40385185
Maybe save the original as a template, that way when someone opens it, it will always be a new copy of the original?
0
 

Author Comment

by:Maliki Hassani
ID: 40385192
Not sure if that is what I want to do, but thanks.  What is a vba script to save a new instance of the current file to a new location?
0
 
LVL 21

Accepted Solution

by:
Randy Poole earned 500 total points
ID: 40385196
Just like you are doing. But after you save it , you need to open it, and close the current one.
0
 

Author Comment

by:Maliki Hassani
ID: 40385202
hmm okay!
0
 

Author Closing Comment

by:Maliki Hassani
ID: 40385252
Thanks
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question