sending mails to 25 receipt and having 25 excel attachement, i want to send like one mail, one attachment, than second new mail, second attachment

[ashwinv]

i want to send 25 mail and having 25 excel as attachment,
For every mail i have to attach one excel.
All the excel file are in one folder. Every day i have to create 25mails and one attachment with evey mail.
Is there any easy way of macro to send this mails and attachment.

 

[ranman256]

IN excel ,make a list of persons and emails
NAME, EMAIL

this can cycle thru and attched the files,
this runs the list sending people their file.
I dont know the names of the files so here assumes name of person is in the name of the excel file to attach.

Private oApp As Outlook.Application
Private oMail As Outlook.MailItem

'----------
Public Sub SendXlFiles()
'----------
Dim vTo, vSubj, vBody, vFile, vName
Dim i As Integer
Const vDIR = "C:\TEMP\"


Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.createitem(olmailitem)


Range("A2").Select


While ActiveCell.Value <> ""
    vName = ActiveCell.Offset(0, 0).Value
    vTo = ActiveCell.Offset(0, 1).Value
    vSubj = "Todays file " & Date
    vBody = "Here's your file"
    
    vFile = vDIR & vName & ".xlsx"
    call    Email1 (vTo, vSubj, vBody, vFile)
   
   ActiveCell.Offset(1, 0).Select  'next row
Next


Set oMail = Nothing
Set oApp = Nothing
End Sub


'----------
Public Function Email1(ByVal pvTo, ByVal pvSubj, ByVal pvBody, Optional ByVal pvFile) As Boolean
'----------


On Error GoTo ErrMail


'****
'**** NOTE : YOU MUST HAVE THE OUTLOOK REFERENCE CHECKED IN VBE; ctl-G, menu,tools, references
'****


With oMail
    .To = pvTo
    .Subject = pvSubj
    If Not IsNull(pvBody) Then .Body = pvBody
    If Not IsMissing(pvFile) Then .Attachments.Add pvFile, olByValue, 1
    
    .Display True
    '.Send
End With


Email1 = True
endit:
Exit Function


ErrMail:
MsgBox Err.Description, vbCritical, Err
Resume endit
End Function