Problems with emailing HTML attachment using VBA

Posted on 2011-04-25
Medium Priority
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 = "mail@mail.com"
.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
  • 2
LVL 76

Accepted Solution

David Lee earned 2000 total points
ID: 35463129
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

ID: 35465349

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
ID: 35465477
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

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

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

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!
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
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.

862 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