Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Outlook / Programming VBA / May 2008

Tip: Looking for answers? Try searching our database.

Emailing attachments in VBA

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Glenn Suggs - 06 May 2008 10:46 GMT
Can someone give me a VBA example of emailing one or more attachments
contained in a string?  It would be nice to use the SendObject method since
the application is already set up that way but I'm not sure if that will work
(other than with one object being sent).  An alternative example would be
great too.
Thanks in advance,
Signature

Glenn

Robert Martim, Excel - 06 May 2008 13:12 GMT
Glenn,

You can do as follows:

Sub Example()
 Dim oOutlook      As Outlook.Application
 Dim oEmailItem    As MailItem
 
 On Error Resume Next
 Set oOutlook = GetObject(, "Outlook.Application")
 If oOutlook Is Nothing Then Set oOutlook =
CreateObject("Outlook.Application")
 
 Set oEmailItem = oOutlook.CreateItem(olMailItem)
 
 With oEmailItem
   .Attachments.Add "C:\MyFile.doc"
   .To = "email@email.com"
   .Display
 End With
 
 Set oEmailItem = Nothing
 Set oOutlook = Nothing
 
End Sub

Signature

Best regards
Robert, Excel MVP
 Author of RibbonX: Customizing the Office 2007 Ribbon:
 Find me at http://www.msofficegurus.com - be part of it!
 Join our forum: http://www.msofficegurus.com/forum/

Glenn Suggs - 06 May 2008 13:57 GMT
Robert,

Thanks for the quick response.  When adding multiple attachments, does a
semi-colon work between each one?  Like this...
.Attachments.Add "C:\Myfile1.doc;C:\MyFile2.doc;C:\MyFile3.doc"

Signature

Glenn

> Glenn,
>
[quoted text clipped - 21 lines]
>  
> End Sub
Robert Martim, Excel - 06 May 2008 14:28 GMT
Glenn

A better option might be to loop through the files in a specific folder. The
reason is that you may have files with different extensions and the names may
also change in future, which would require a change in your code.

In this example, you will need to install the references to Windows Script
Host Model (if you do not wish to install it, you will require to create the
scripting object instead):

Sub Example()
 Dim oOutlook      As Outlook.Application
 Dim oEmailItem    As MailItem
 Dim oFileSystem   As New FileSystemObject
 Dim oFolder       As Object
 Dim oFile         As File
 
 On Error Resume Next
 Set oOutlook = GetObject(, "Outlook.Application")
 If oOutlook Is Nothing Then Set oOutlook =
CreateObject("Outlook.Application")
 
 On Error GoTo 0
 Set oEmailItem = oOutlook.CreateItem(olMailItem)
 Set oFolder = oFileSystem.GetFolder("C:\For Deletion")
 
 With oEmailItem
   For Each oFile In oFolder.Files
     .Attachments.Add oFile.Path
   Next oFile
   .To = "email@email.com"
   .Display
 End With
 
 Set oFolder = Nothing
 Set oFileSystem = Nothing
 Set oEmailItem = Nothing
 Set oOutlook = Nothing
 
End Sub

Signature

Best regards
Robert, Excel MVP
 Author of RibbonX: Customizing the Office 2007 Ribbon:
 Find me at http://www.msofficegurus.com - be part of it!
 FORUM: http://www.msofficegurus.com/forum/

Sue Mosher [MVP-Outlook] - 06 May 2008 15:32 GMT
No, that won't work at all. You must call Attachments.Add each time you want to add a file. 3 files, three calls to Attachments.Add

Signature

Sue Mosher, Outlook MVP
  Author of Microsoft Outlook 2007 Programming:
    Jumpstart for Power Users and Administrators
   http://www.outlookcode.com/article.aspx?id=54

> Robert,
>
[quoted text clipped - 27 lines]
>>  
>> End Sub
Robert Martim, Excel - 06 May 2008 16:22 GMT
Sue

You can try the code and you will see it works... On the other hand, I am
not clear whether the post was addressed to Glenn (which I guess it was).

Signature

Best regards
Robert, Excel MVP
 Author of RibbonX: Customizing the Office 2007 Ribbon:
 Find me at http://www.msofficegurus.com - be part of it!
 FORUM: http://www.msofficegurus.com/forum/


Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.