Problems with emailing HTML attachment using VBA

Posted on 2011-04-25
Last Modified: 2012-05-11
Every week in my work, I email a snapshot of open jobs from an Excel spreadsheet (set up as a database) to another agency.

In the past I have used Access as our job database and then email the reports generated in HTML format. We are no longer permitted to use Access and as such now use Excel.

The other agency have asked that the jobs still be in HTML format. This has proved tricky for me to do but I have finally done it. Unfortunately, the size of the attachment is 7Mb!!!! Our mailbox limit for incoming and outgoing mail is 5Mb!

I use the code below to filter and send the required data.

How on earth can I reduce the size? Is it down to the coding or something else? Could I insert into the code a way of zipping the attachment or maybe copy and pasting the actice cells into a blank sheet to send? Or can I just get it to copy only the active area into the HTML?

The original size of the file is only 1.5Mb. Unfortunately due to the nature of the work, I am unable to attach it to this thread, nor would it be practical to knock up a dummy one without speding ages tailoring the details in it although I have tweaked the code in terms of email address and such like.

Sub Email()

Selection.AutoFilter Field:=1, Criteria1:="Open"
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim wb As Workbook
Dim strdate As String

strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
Set xsheet = ActiveSheet

xsheet.UsedRange.Copy ActiveSheet.Range("A1")
Set wb = ActiveWorkbook
wb.Sheets(1).DrawingObjects.Visible = True

With wb
.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".htm", FileFormat:=xlHtml

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "Latest Open Jobs Dated - "
.Body = "Latest Open Jobs from My Workplace"
.Attachments.Add wb.FullName

End With

.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With

Selection.AutoFilter Field:=1

End Sub

Open in new window

Question by:laffs_efc
    LVL 76

    Accepted Solution

    Hi, laffs_efc.

    It's pretty difficult to offer advice on how to shrink something that you can't see.  Yes, it's possible to zip the file from code, assuming of course that you have a zipping tool that can be called from a script.  It's also possible to copy a range to another sheet and save just that sheet to an HTML file.  Something like this.
    Sub SaveRangeToFile()
        Dim excWks As Excel.Worksheet, excRng As Excel.Range, excTmp As Excel.Worksheet
        Set excWks = Application.ActiveSheet
        'Select and copy a range of cells to the clipboard'
        Set excRng = excWks.Range("A1", "D5")
        'Add a temporary worksheet'
        Set excTmp = Excel.ActiveWorkbook.Worksheets.Add()
        excTmp.Name = "Temp"
        'Paste the copied range into the temporary worksheet'
        excWks.Paste Destination:=excTmp.Range("A1", "D5")
        'Save the sheet to an HTML file'
        excTmp.SaveAs "c:\Users\David\Documents\Testing.htm", FileFormat:=xlHtml
        'Delete the temporary sheet'
    End Sub

    Open in new window


    Author Comment


    Thanks for the reply. I do appreciate your frustration in offering your advice on something that cant be seen. Apologies. The problem I have is, it's taken me weeks to get to this stage and would probably take another few hours to knock up a 'clean' version to upload on here. Anyway. with regards to your code, unfortunately, the active area is dynamic although historically it has never been more than the range you have suggested there is nothing to say it wont be in the future. Can I modify your code to take this into account?
    LVL 76

    Expert Comment

    by:David Lee
    You're welcome.  I understand.  Yes, you can modify the code to handle a range of any size/shape.  One way would be to simply edit the code when the range changes.  Another way would be to modify it to prompt you for range starting and ending coordinates.  Or you could modify the code to allow you to manually select the range and have the code work off of whatever is selected.
    LVL 24

    Expert Comment

    This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Article by: Leon
    Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    745 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

    16 Experts available now in Live!

    Get 1:1 Help Now