Home ] Up ] MS Access Page ] SQL Server ] Tech Links ]

Email and/or Zipping data from Excel.
This page contains help on how to email data directly from Excel and how to zip files directly from Excel.



Automatically zipping Excel Files
Create Outlook Distribution List from Excel List
Email from Excel
Include Email Signature from Outlook when Emailing via VBA
 
   

Email data from Excel.
For dozens of useful techniques on how to email data from Excel please Check out Ron De Bruin's site - He has all manner of different ways of linking Excel and Outlook including

Mail the whole workbook.
Mail one sheet.
Mail more then one sheet.
Mail Range or Selection.
Mail every worksheet with address in A1.

The code is tested in Outlook Express, Windows Mail and Outlook and It may or may not work with other email clients.
All code from this link will also work in Excel 2007.

See: http://www.rondebruin.nl/sendmail.htm


 
Zipping Files From Excel
If you need to save and automatically zip a spreadsheet or workbook (or part of) from Excel, please see the following links.
http://www.cpearson.com/excel/SaveCopyAndZip.htm
http://www.vbaccelerator.com/home/VB/Code/Libraries/Compression/Zipping_Files/VB6_Zip_Sample_Project.asp
http://msdn2.microsoft.com/en-us/library/ms950396.aspx

or Again from Ron De Bruin
Zip file or files with the default Windows XP zip program (VBA)
WinZip : Zip the Activeworkbook, File or Files with (VBA)
WinZip : Unzip to a specific folder with (VBA)


 

Create Outlook Distribution List from Excel List

For whoever is interested this can also be automated (not that you need to
because doing it manually is so simple)
'-----------------------------------------------------------------------------
Sub MakeDistrListFromExcelList()
'need reference to MS Outlook library
'Adds e-mail and display name to a distribution list.  Format of e-mail
address (Col F)
'must be *** Name <e-mailaddress> *** for both e-mail address and display
name to
'get populated in the distribution list
Dim ol As New Outlook.Application
Dim olns                                 As Outlook.Namespace
Dim fldContacts                          As Outlook.MAPIFolder
Dim itms                                 As Outlook.Items
Dim cf                                   As Object
Dim i                                    As Variant
Dim myDistList                           As DistListItem
Dim myRecipients                         As Recipients
Dim myMailItem                           As MailItem

Set ol = CreateObject("Outlook.Application")
Set olns = ol.GetNamespace("MAPI")
' Which folder is the contacts list in?
Set fldContacts = olns.GetDefaultFolder(olFolderContacts)
Set itms = fldContacts.Items
Set myDistList = ol.CreateItem(olDistributionListItem)
myDistList.DLName = "Test Distribution List"

For Each i In [F8:F39]
  Set cf = itms.Add("IPM.Contact")
   cf.Email1Address = i.Value
   Set myMailItem = ol.CreateItem(olMailItem)
   Set myRecipients = myMailItem.Recipients
   myRecipients.Add cf.Email1Address
   myRecipients.ResolveAll
   myDistList.AddMembers myRecipients
   myDistList.Save
Next
   myDistList.Display

End Sub
'-----------------------------------------------------------------------------
Thanks to George from Excel-L.

 
Include Email Signature from Outlook when Emailing via VBA
 
When I send an email via VBA code the resulting mail does not include any signature. I Need
to include my standard signature. Any suggestions / solution?
See: http://www.rondebruin.nl/mail/folder3/signature.htm
 
 

Home ] Up ] MS Access Page ] SQL Server ] Tech Links ]