Wants to mail on different ID attachments from a folder through Outlook

[Nisha Dhawan]

Hello Everyone,

I have a folder in which I have some files. All the files I want to send one by one to the concern person via email (Outlook). Please help to write VBA code.


Thanks in advance

Nisha

 

[dianacris]

There are many code samples out there for looping through files in a folder, or specifically for Excel files in a folder. Here's one example: http://stackoverflow.com/questions/...-files-in-a-specified-folder-and-pulling-data

Once you have that loop, instead of opening each file, make a call to a SendEmail procedure like the one below. Pass the name of the file found in the loop.

Sub SendEmail(ByVal sTo As String, ByVal sCC As String, ByVal sSubject As String, ByVal sBody As String, ByVal sFile As String)
      Dim OutApp As Object
      Dim OutMail As Object
      Dim strBody As String
      Dim SigString As String
      Dim Signature As String

  On Error GoTo SendEmail_Error

1    Set OutApp = CreateObject("Outlook.Application")
2    Set OutMail = OutApp.CreateItem(0)

3    With OutMail
4        .Display
5        .To = sTo
6        .CC = sCC
7        .bCC = ""
8        .Subject = sSubject
9        .HTMLBody = sBody & "<br>" & .HTMLBody
10        .Attachments.Add sFile
11    End With

Exit_SendEmail:
    Set OutMail = Nothing
    Set OutApp = Nothing
    Exit Sub

SendEmail_Error:
    MsgBox Err.Number & ": " & Err.Description, , "Module1.SendEmail"
    Resume Exit_SendEmail
End Sub

Hope this helps!