Email macro add attachement different file.

[Zoufy]

Hey everyone !

I'm seeking your help because I can't seem to find a solution on the net.

I use a macro to send emails automatically, and I need to attach a different file to the emails.

The only way I found is to specify the file's location, however, since that file changes every week, I need to update the emplacement manually in the macro : "C:\Users\Zoufy\Desktop\Finance_File_06-30-2015.xlsx"

Is there any way to bypass that problem ? Since the name of the file always starts by the same words "Finance_File"

I tried this but it doesn't work :

Dim CurrWorkbook As Workbook
Dim Workbook As Workbook

Set CurrWorkbook = ActiveWorkbook

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

With OutMail
To = Sheets("EMAIL_SUMMARY").Cells(2, 3)
        .CC = Sheets("EMAIL_SUMMARY").Cells(3, 3)
        .BCC = ""
        .Subject = Sheets("EMAIL_SUMMARY").Cells(4, 3)
        .HTMLBody = Sheets("EMAIL_SUMMARY").Cells(7, 2) & RangetoHTML(rng)

For Each Workbook In Excel.Workbooks
If Left(Workbook.Name, 12) = "Finance_File" Then

        .Attachments.Add Workbook
End If
Next

Thanks a lot for your help.

 

[baitmaster]

Welcome to the board. Looks to me like your problem is primarily that you are trying to add a workbook object, where the .attachments.add method requires a text string. You might look at amending this to workbook.name as a fix

A better approach might be to identify the attachment first then passing the name into an emailing routine, you can download a file that contains example code for doing this at Examples