Solved

VBA: Save excel file and email

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
.Range Set 25 75
VBA Help 18 43
Excel VBA 4 26
VBA - If Bookmark = "XXBOOKMARKXX" then 15 24
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
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…

806 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