E-mail attachment with VBA from Excel

[teniz52]

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]

I have an approach here you can adapt:

http://www.codeforexcelandoutlook.co...-clean-emails/

[teniz52]

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]

Hi,

Please post a sample workbook with the requirements in detail.

I will try if I can help
Arvind

[JP2112]

Try this:

[VBA]' 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[/VBA]