Create an email and attach a spreadsheet
March 12, 2008
As long as you’re automating your reports, you might as well automate the email notification!
Example:
strHTMLBody = “<html>Test Message,<br/><br/>This is a test. If this were not a test:” & _
“<ul><li>There would be useful information here</li><li>There would perhaps be more information here</li></ul><br/>Thank you</html>”
Call CreateNewMail(”recipients@somedomain.com;morerecipients@anotherdomain.com”, “ccedfolks@adomain.com”, “Test Subject”, strHTMLBody,”Some fake file.xls”)
Make sure you enable Microsoft Outlook 11 Object Library! (see code)
Sub CreateNewMail(strRecipients As String, strCCs As String, strSubject As String, strBody As String, Optional strAttachPath As String)
‘
‘ Sub CreateNewMail
‘ Author: Brent Harvey
‘ Email: Excel.Examples@brentharvey.net
‘ Date: 08/24/2006
‘ Source:
‘ http://www.sitepoint.com/forums/showthread.php?t=406443
‘ Creates email message to send report
‘ ToDo:
‘ Check for and load Outlook object library
‘In Visual Basic click on Tools, References and tick Microsoft Outlook 11 Object LibraryDim myOlApp As Outlook.Application
Dim myItem As Outlook.MailItem
Dim myRecipient As Outlook.Recipient
Dim myCCRecipient As Outlook.Recipient
Dim myBCCRecipient As Outlook.RecipientSet myOlApp = CreateObject(”Outlook.Application”)
Set myItem = myOlApp.CreateItem(olMailItem)
Set myRecipient = myItem.Recipients.Add(strRecipients)
Set myCCRecipient = myItem.Recipients.Add(strCCs)
myCCRecipient.Type = olCC
myItem.Subject = strSubject
myItem.HTMLBody = strBody ‘If you don’t want to use HTML Body, comment this line and uncomment the next
‘myItem.Body = strBody ‘If you would rather use HTML Body, comment this line and uncomment the previous
If (strAttachPath <> “”) Then
Set myAttachments = myItem.Attachments
myAttachments.Add strAttachPath
End IfmyItem.Display
End Sub
Posted in Excel
content rss
