Emailing attachments in VBA

[Glenn Suggs]

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,

 

[Robert Martim, Excel]

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 = "(e-mail address removed)"
.Display
End With

Set oEmailItem = Nothing
Set oOutlook = Nothing

End Sub

--
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]

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"

 

[Robert Martim, Excel]

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 = "(e-mail address removed)"
.Display
End With

Set oFolder = Nothing
Set oFileSystem = Nothing
Set oEmailItem = Nothing
Set oOutlook = Nothing

End Sub



--
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]]

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

 

[Robert Martim, Excel]

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).

--
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/