Results 1 to 1 of 1

Macro to zip, encrypt and email an excel file

Threaded View

  1. #1
    Registered User
    Join Date
    03-10-2014
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    2

    Macro to zip, encrypt and email an excel file

    Hi

    I am new to macros and have been searching the internet all day but I am struggling.

    I have got the below code to zip a file and then email it to myself but I also need the macro to encrypt my file. Is anybody able to help me?

    Option Explicit
    
    Sub Zip_Mail_ActiveWorkbook()
        Dim strDate As String, DefPath As String, strbody As String
        Dim oApp As Object, OutApp As Object, OutMail As Object
        Dim FileNameZip, FileNameXls
        Dim FileExtStr As String
    
        DefPath = Application.DefaultFilePath
        If Right(DefPath, 1) <> "\" Then
            DefPath = DefPath & "\"
        End If
    
        'Create date/time string and the temporary xl* and zip file name
        If Val(Application.Version) < 12 Then
            FileExtStr = ".xls"
        Else
            Select Case ActiveWorkbook.FileFormat
            Case 51: FileExtStr = ".xlsx"
            Case 52: FileExtStr = ".xlsm"
            Case 56: FileExtStr = ".xls"
            Case 50: FileExtStr = ".xlsb"
            Case Else: FileExtStr = "notknown"
            End Select
            If FileExtStr = "notknown" Then
                MsgBox "Sorry unknown file format"
                Exit Sub
            End If
        End If
    
        strDate = Format(Now, " yyyy-mm-dd h-mm-ss")
    
        FileNameZip = DefPath & Left(ActiveWorkbook.Name, _
        Len(ActiveWorkbook.Name) - Len(FileExtStr)) & strDate & ".zip"
    
        FileNameXls = DefPath & Left(ActiveWorkbook.Name, _
        Len(ActiveWorkbook.Name) - Len(FileExtStr)) & strDate & FileExtStr
    
    
        If Dir(FileNameZip) = "" And Dir(FileNameXls) = "" Then
    
            'Make copy of the activeworkbook
            ActiveWorkbook.SaveCopyAs FileNameXls
    
            'Create empty Zip File
            NewZip (FileNameZip)
    
            'Copy the file in the compressed folder
            Set oApp = CreateObject("Shell.Application")
            oApp.Namespace(FileNameZip).CopyHere FileNameXls
    
            'Keep script waiting until Compressing is done
            On Error Resume Next
            Do Until oApp.Namespace(FileNameZip).items.Count = 1
                Application.Wait (Now + TimeValue("0:00:01"))
            Loop
            On Error GoTo 0
    
            'Create the mail
            Set OutApp = CreateObject("Outlook.Application")
            Set OutMail = OutApp.CreateItem(0)
            strbody = "Hi there" & vbNewLine & vbNewLine & _
                      "This is line 1" & vbNewLine & _
                      "This is line 2" & vbNewLine & _
                      "This is line 3" & vbNewLine & _
                      "This is line 4"
    
            On Error Resume Next
            With OutMail
                .To = "[email protected]"
                .CC = ""
                .BCC = ""
                .Subject = "This is the Subject line"
                .Body = strbody
                .Attachments.Add FileNameZip
                .Send   'or use .Display
            End With
            On Error GoTo 0
    
            'Delete the temporary Excel file and Zip file you send
            Kill FileNameZip
            Kill FileNameXls
        Else
            MsgBox "FileNameZip or/and FileNameXls exist"
        End If
    End Sub
    Help would be so much appreciated!!!

    Thanks
    Last edited by beefybef; 03-10-2014 at 01:00 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro Code to Encrypt Multiple Excel Files in Folders and Subfolders
    By ali_1989 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-04-2014, 12:53 PM
  2. MACRO for sending email with an excel file and pdf
    By Cangia87 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-27-2013, 08:04 AM
  3. Replies: 0
    Last Post: 02-21-2013, 04:46 AM
  4. Replies: 1
    Last Post: 06-08-2007, 06:47 PM
  5. [SOLVED] file encrypt
    By RC in forum Excel General
    Replies: 3
    Last Post: 07-06-2005, 06:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1