Solved

VBA: Save excel file and email

Posted on 2014-10-16
5
272 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

732 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