Solved

VBA: Save excel file and email

Posted on 2014-10-16
5
260 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

863 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now