PDA

View Full Version : E-mail attachment with VBA from Excel



teniz52
11-09-2009, 01:49 PM
I am trying to write a macro to send an e-mail with all of the files in a designated folder attached.

Any help would be appreciated.

Thanks!

JP2112
11-09-2009, 05:54 PM
I have an approach here you can adapt:

http://www.codeforexcelandoutlook.com/blog/2008/09/save-attachments-and-send-clean-emails/

teniz52
11-09-2009, 06:19 PM
Thanks for the reply JP. The only problem is that I am completely new to VBA and don't even know where to start with that code. Any direction would be much appreciated!

aravindhan_3
11-10-2009, 07:57 AM
Hi,

Please post a sample workbook with the requirements in detail.

I will try if I can help
Arvind

JP2112
11-10-2009, 10:00 AM
Try this:

' adjust this line to match the folder with the files you want to attach
Const FOLDER As String = "C:\My Files\"
Sub ProcessEachFileInFolder()
On Error GoTo ErrorHandler
Dim fileName As String
Dim olApp As Object
Dim Msg As Object
fileName = Dir(FOLDER, vbDirectory)
' if no files in folder, exit
If Len(fileName) = 0 Then GoTo ProgramExit
' get Outlook instance
Set olApp = GetOutlookApp
If olApp Is Nothing Then GoTo ProgramExit
' create new email message
Set Msg = CreateMessage(olApp)
' loop through folder and attach to email
Do While Len(fileName) > 0
Msg.Attachments.Add fileName
' get next file
fileName = Dir
Loop
' display email for sending
Msg.Display
ProgramExit:
Exit Sub
ErrorHandler:
MsgBox Err.Number & " - " & Err.Description
Resume ProgramExit
End Sub
Function GetOutlookApp() As Object
On Error Resume Next
Set GetOutlookApp = GetObject(, "Outlook.Application")
If Err.Number <> 0 Then
Set GetOutlookApp = CreateObject("Outlook.Application")
End If
On Error GoTo 0
End Function
Function CreateMessage(olApp As Object) As Object
Set CreateMessage = olApp.CreateItem(0)
End Function