Help - Sending email through VBA with multiple attached files

[mike760534211]

I am attempting to use the following code to send an email through outlook from excel with 2 cells and a range of cells as the message body and attach multiple files to the email if they exist on the hard drive.

Sub Send_Email_ATT()
'
' Sends Email to Group with file attachments
'
'

'Group 1

' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, Outlook 2010.
' This example sends the last saved version of the Activeworkbook object .
    Dim OutApp As Object
    Dim OutMail As Object
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
        
    On Error Resume Next
   ' Change the mail address and subject in the macro before you run it.
    With OutMail
        .To = ActiveSheet.Range("B4")
        .CC = ""
        .BCC = ""
        .Subject = ActiveSheet.Range("B5")
        .Body = Sheets("Email Body Information").Range("A1").Value & Sheets("OLDEST CALCS").Range("B6:I28").Value & Sheets("Email Body Information").Range("A2").Value
        .Attachments.Add "C:\ILCC\Process\DCP\1 <use wildcard=""> .xlsx"
        .Send
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing

End Sub

Many parts of this work and don't work depending on what i choose.

The below code creates the message body as blank if i include the range Sheets("OLDEST CALCS").Range("B6:I28").Value but if left out it creates the message body correctly.

        .Body = Sheets("Email Body Information").Range("A1").Value & Sheets("OLDEST CALCS").Range("B6:I28").Value & Sheets("Email Body Information").Range("A2").Value

I would like the below code to go through a specified folder and attach all files that start with a given number. the different files have completely different names but the ones i want to attach will always start with a number 1

        .Attachments.Add "C:\ILCC\Process\DCP\1 [COLOR=#ff0000]<use wildcard=""></use>[/COLOR] .xlsx"

</use>

 

[CharlesChuckieCharles]

Check out this earlier MrExcel answer

http://www.mrexcel.com/forum/excel-...ns-send-multiple-attachments-via-outlook.html



or a simple approach


.Body = Sheets("Email Body Information").Range("A1").Value & Sheets("OLDEST CALCS").Range("B6:I28").Value & Sheets("Email Body Information").Range("A2").Value

Path="C:\ILCC\Process\DCP\"


Type="1*.xlsx"


Myfile= Dir(Path&type)

while myfile<>""
.Attachments.add Path & Myfile

doevents
<USE wildcard=""></USE>wend

.Send