Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

VBA: Save excel file and email

Posted on 2014-10-16
5
Medium Priority
?
286 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 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

575 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